I have a Microsoft SQL view that uses 2 other views in it. Can you try to combine all…
- User
I have a Microsoft SQL view that uses 2 other views in it.
Can you try to combine all three into one.
Here is the main View:
SELECT
S.StudentUID,
S.StudentID,
S.LastName,
S.FirstName,
S.MiddleName,
S.Email1,
S.Email2,
F.TextTerm AS pTerm,
S.TextTerm AS cTerm,
S.EnrollmentStatus,
S.Campus,
Round( F.AttTermHrs, 0 ) AS prevTermAttHr,
Round( F.ErnTermHrs, 0 ) AS prevTermErnHr,
Round( F.CumAttemptedHours, 0 ) AS prevAttHr,
Round( F.CumEarnedHours, 0 ) AS prevErnHr,
F.AcademicStatus AS prevAcadStatus,
F.InstitutionalSAP AS prevAcaSAP,
F.GovernmentalSAP AS prevGovSAP,
S.AcademicStatus AS CurAcadStatus,
S.AcademicStatusID AS CurStatID,
S.InstitutionalSAP AS CurAcaSAP,
S.InstitutionalSAPID AS CurAcaID,
S.GovernmentalSAP AS CurGovSAP,
S.GovernmentalSAPID AS CurGovID,
Term = 'Term>',
Round( S.AttTermHrs, 0 ) AS curTermAttHr,
Round( S.ErnTermHrs, 0 ) AS curTermErnHr,
Round( F.TermGPA, 2 ) AS prevTermGPA,
Round( ( F.ErnTermHrs / F.AttTermHrs ), 4 ) AS prevTermProgress,
Round( S.TermGPA, 2 ) AS curTermGPA,
Round( ( S.ErnTermHrs / S.AttTermHrs ), 4 ) AS curTermProgress,
Cumulative = 'Cumulative>',
Round( S.CumAttemptedHours, 0 ) AS cAttHr,
Round( S.CumEarnedHours, 0 ) AS cErnHr,
Round( F.CumGPA, 2 ) AS prevGPA,
Round( ( F.CumEarnedHours / F.CumAttemptedHours ), 4 ) AS prevProgress,
Round( S.CumGPA, 2 ) AS cGPA,
Round( ( S.CumEarnedHours / S.CumAttemptedHours ), 4 ) AS cProgress,
SS.TextTerm as NextTerm,
SS.AcademicStatus AS nextSetAcaStatus,
SS.InstitutionalSAP AS nextSetAcaSAP,
SS.GovernmentalSAP AS nextSetGovSAP,
SS.AcademicStatusID AS nextSetAcaStatusID,
SS.InstitutionalSAPID AS nextSetAcaID,
SS.GovernmentalSAPID AS nextGovID
FROM
--update 1stterm SAP and 2nd Term Sap Views First - 2ndterm is the current term, 1st term is the prior term
CAMS_Merge_View_ForSAP_2ndTerm AS S
LEFT JOIN CAMS_Merge_View_ForSAP_1stTerm AS F ON F.StudentUID = S.StudentUID
LEFT Outer JOIN dbo.CAMS_StudentStatus_View AS SS ON SS.StudentUID = S.StudentUID and SS.TextTerm = 'FA-25'
--Bump this text term forward to the upcoming
Here is the view for the 1stTerm:
SELECT
dbo.Student.StudentUID,
dbo.Student.StudentID,
dbo.Student.LastName,
dbo.Student.FirstName,
dbo.Student.MiddleInitial AS MiddleName,
TRM.TextTerm,
TRM.TermCalendarID,
SS.EnrollmentStatus,
SS.Campus,
SS.AcademicStatus,
SS.InstitutionalSAP,
SS.InstitutionalSAPID,
SS.GovernmentalSAP,
SS.GovernmentalSAPID,
tGPA.Credits AS AttTermHrs,
tGPA.Earned AS ErnTermHrs,
tGPA.TermGPA,
ISNULL
((SELECT TOP (1) CumAttemptedHours
FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A
WHERE (dbo.Student.StudentUID = StudentUID) AND (SS.TermCalendarID = TermCalendarID)), null) AS CumAttemptedHours, ISNULL
((SELECT TOP (1) CumEarnedHours
FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A
WHERE (dbo.Student.StudentUID = StudentUID) AND (SS.TermCalendarID = TermCalendarID)), null) AS CumEarnedHours, ISNULL
((SELECT TOP (1) CumGPA
FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A
WHERE (dbo.Student.StudentUID = StudentUID) AND (SS.TermCalendarID = TermCalendarID)), null) AS CumGPA
FROM
dbo.Student
LEFT OUTER JOIN dbo.CAMS_StudentStatus_View AS SS ON dbo.Student.StudentUID = SS.StudentUID
LEFT OUTER JOIN dbo.TermCalendar AS TRM ON SS.TermCalendarID = TRM.TermCalendarID
LEFT OUTER JOIN dbo.CAMS_StudentTermGPA_View_Fix AS tGPA ON dbo.Student.StudentUID = tGPA.StudentUID
AND SS.TermCalendarID = tGPA.TermCalendarID
WHERE NOT (ss.campus = 'SATheologySeminary') and (StudentLoad is Not Null) and (StudentLoad <> '')
and (TRM.TextTerm = 'FA-24') and ((SELECT TOP (1) CumAttemptedHours
FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A
WHERE (dbo.Student.StudentUID = StudentUID) AND (SS.TermCalendarID = TermCalendarID))) is not null
Here is the View for the 2ndTerm
SELECT
dbo.Student.StudentUID,
dbo.Student.StudentID,
dbo.Student.LastName,
dbo.Student.FirstName,
dbo.Student.MiddleInitial AS MiddleName,
TRM.TextTerm,
TRM.TermCalendarID,
SS.EnrollmentStatus,
SS.Campus,
SS.AcademicStatus,
SS.AcademicStatusID,
SS.InstitutionalSAP,
SS.InstitutionalSAPID,
SS.GovernmentalSAP,
SS.GovernmentalSAPID,
dbo.Address.Email1,
dbo.Address.Email2,
tGPA.Credits AS AttTermHrs,
tGPA.Earned AS ErnTermHrs,
tGPA.TermGPA,
ISNULL
((SELECT TOP (1) CumAttemptedHours
FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A
WHERE (dbo.Student.StudentUID = StudentUID) AND (SS.TermCalendarID = TermCalendarID)), null) AS CumAttemptedHours,
ISNULL
((SELECT TOP (1) CumEarnedHours
FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A
WHERE (dbo.Student.StudentUID = StudentUID) AND (SS.TermCalendarID = TermCalendarID)), null) AS CumEarnedHours,
ISNULL
((SELECT TOP (1) CumGPA
FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A
WHERE (dbo.Student.StudentUID = StudentUID) AND (SS.TermCalendarID = TermCalendarID)), null) AS CumGPA
FROM
dbo.Student
LEFT OUTER JOIN dbo.Student_Address AS SA ON dbo.Student.StudentUID = SA.StudentID
LEFT OUTER JOIN dbo.Address ON SA.AddressID = dbo.Address.AddressID
LEFT OUTER JOIN dbo.Glossary AS g1 ON dbo.Student.TypeID = g1.UniqueId
LEFT OUTER JOIN dbo.Glossary AS g11 ON dbo.Address.AddressTypeID = g11.UniqueId
LEFT OUTER JOIN dbo.CAMS_StudentStatus_View AS SS ON dbo.Student.StudentUID = SS.StudentUID
LEFT OUTER JOIN dbo.TermCalendar AS TRM ON SS.TermCalendarID = TRM.TermCalendarID
LEFT OUTER JOIN dbo.CAMS_StudentTermGPA_View_Fix AS tGPA ON dbo.Student.StudentUID = tGPA.StudentUID
AND SS.TermCalendarID = tGPA.TermCalendarID
WHERE (dbo.Address.ActiveFlag = 'Yes') AND (g11.DisplayText = 'Home') and NOT (ss.campus = 'SATheologySeminary') and (StudentLoad is Not Null) and (StudentLoad not in ('','Audit'))
and (TRM.TextTerm = 'SP-25') and ((SELECT TOP (1) CumAttemptedHours
FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A
WHERE (dbo.Student.StudentUID = StudentUID) AND (SS.TermCalendarID = TermCalendarID))) is not null
- Assistant
Below is an all‐in‐one view that inlines both of your “1stTerm” and “2ndTerm” views and then joins them (plus your “NextTerm” lookup). I’ve used CTEs for clarity, CROSS APPLY to pull in the cumulative GPA rows, and pushed all your filters into the appropriate CTE. You can simply drop this into SSMS (adjust the schema/name as you like) and you’ll have a single view doing the work of all three.
CREATE OR ALTER VIEW dbo.vw_Combined_SAP AS WITH -- 1st‐Term (“FA‐24”) for Prior‐Term SAP FirstTerm AS ( SELECT st.StudentUID, st.StudentID, st.LastName, st.FirstName, st.MiddleInitial AS MiddleName, tr.TextTerm AS TextTerm, tr.TermCalendarID, ss.EnrollmentStatus, ss.Campus, ss.AcademicStatus, ss.InstitutionalSAP, ss.InstitutionalSAPID, ss.GovernmentalSAP, ss.GovernmentalSAPID, tg.Credits AS AttTermHrs, tg.Earned AS ErnTermHrs, tg.TermGPA, cg.CumAttemptedHours, cg.CumEarnedHours, cg.CumGPA FROM dbo.Student AS st INNER JOIN dbo.CAMS_StudentStatus_View AS ss ON st.StudentUID = ss.StudentUID INNER JOIN dbo.TermCalendar AS tr ON ss.TermCalendarID = tr.TermCalendarID LEFT JOIN dbo.CAMS_StudentTermGPA_View_Fix AS tg ON st.StudentUID = tg.StudentUID AND ss.TermCalendarID = tg.TermCalendarID CROSS APPLY ( SELECT TOP(1) A.CumAttemptedHours, A.CumEarnedHours, A.CumGPA FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A WHERE A.StudentUID = st.StudentUID AND A.TermCalendarID = ss.TermCalendarID ) AS cg WHERE tr.TextTerm = 'FA-24' AND ss.Campus <> 'SATheologySeminary' AND st.StudentLoad IS NOT NULL AND st.StudentLoad <> '' ), -- 2nd‐Term (“SP‐25”) for Current‐Term SAP (includes email join + home‐address filter) SecondTerm AS ( SELECT st.StudentUID, st.StudentID, st.LastName, st.FirstName, st.MiddleInitial AS MiddleName, tr.TextTerm AS TextTerm, tr.TermCalendarID, ss.EnrollmentStatus, ss.Campus, ss.AcademicStatus, ss.AcademicStatusID, ss.InstitutionalSAP, ss.InstitutionalSAPID, ss.GovernmentalSAP, ss.GovernmentalSAPID, adr.Email1, adr.Email2, tg.Credits AS AttTermHrs, tg.Earned AS ErnTermHrs, tg.TermGPA, cg.CumAttemptedHours, cg.CumEarnedHours, cg.CumGPA FROM dbo.Student AS st INNER JOIN dbo.Student_Address AS sa ON st.StudentUID = sa.StudentID INNER JOIN dbo.Address AS adr ON sa.AddressID = adr.AddressID INNER JOIN dbo.Glossary AS g11 ON adr.AddressTypeID = g11.UniqueId AND g11.DisplayText = 'Home' INNER JOIN dbo.CAMS_StudentStatus_View AS ss ON st.StudentUID = ss.StudentUID INNER JOIN dbo.TermCalendar AS tr ON ss.TermCalendarID = tr.TermCalendarID LEFT JOIN dbo.CAMS_StudentTermGPA_View_Fix AS tg ON st.StudentUID = tg.StudentUID AND ss.TermCalendarID = tg.TermCalendarID CROSS APPLY ( SELECT TOP(1) A.CumAttemptedHours, A.CumEarnedHours, A.CumGPA FROM dbo.CAMS_StudentCumulativeGPA_View_GPAGroupFix AS A WHERE A.StudentUID = st.StudentUID AND A.TermCalendarID = ss.TermCalendarID ) AS cg WHERE tr.TextTerm = 'SP-25' AND ss.Campus <> 'SATheologySeminary' AND adr.ActiveFlag = 'Yes' AND st.StudentLoad IS NOT NULL AND st.StudentLoad NOT IN ('','Audit') ) SELECT S.StudentUID, S.StudentID, S.LastName, S.FirstName, S.MiddleName, S.Email1, S.Email2, -- Prior term data from F F.TextTerm AS PrevTerm, ROUND(F.AttTermHrs,0) AS prevTermAttHr, ROUND(F.ErnTermHrs,0) AS prevTermErnHr, ROUND(F.CumAttemptedHours,0) AS prevAttHr, ROUND(F.CumEarnedHours,0) AS prevErnHr, F.AcademicStatus AS prevAcadStatus, F.InstitutionalSAP AS prevAcaSAP, F.GovernmentalSAP AS prevGovSAP, ROUND(F.TermGPA,2) AS prevTermGPA, ROUND(CASE WHEN F.AttTermHrs>0 THEN F.ErnTermHrs*1.0/F.AttTermHrs END,4) AS prevTermProgress, ROUND(F.CumGPA,2) AS prevGPA, ROUND(CASE WHEN F.CumAttemptedHours>0 THEN F.CumEarnedHours*1.0/F.CumAttemptedHours END,4) AS prevProgress, -- Current term data from S S.TextTerm AS CurTerm, S.EnrollmentStatus, S.Campus, ROUND(S.AttTermHrs,0) AS curTermAttHr, ROUND(S.ErnTermHrs,0) AS curTermErnHr, S.AcademicStatus AS CurAcadStatus, S.AcademicStatusID AS CurStatID, S.InstitutionalSAP AS CurAcaSAP, S.InstitutionalSAPID AS CurAcaID, S.GovernmentalSAP AS CurGovSAP, S.GovernmentalSAPID AS CurGovID, ROUND(S.TermGPA,2) AS curTermGPA, ROUND(CASE WHEN S.AttTermHrs>0 THEN S.ErnTermHrs*1.0/S.AttTermHrs END,4) AS curTermProgress, ROUND(S.CumGPA,2) AS cGPA, ROUND(CASE WHEN S.CumAttemptedHours>0 THEN S.CumEarnedHours*1.0/S.CumAttemptedHours END,4) AS cProgress, ROUND(S.CumAttemptedHours,0) AS cAttHr, ROUND(S.CumEarnedHours,0) AS cErnHr, -- Next‐term SAP set = FA‐25 NextSS.TextTerm AS NextTerm, NextSS.AcademicStatus AS nextSetAcaStatus, NextSS.AcademicStatusID AS nextSetAcaStatusID, NextSS.InstitutionalSAP AS nextSetAcaSAP, NextSS.InstitutionalSAPID AS nextSetAcaID, NextSS.GovernmentalSAP AS nextSetGovSAP, NextSS.GovernmentalSAPID AS nextGovID FROM SecondTerm AS S LEFT JOIN FirstTerm AS F ON F.StudentUID = S.StudentUID LEFT JOIN dbo.CAMS_StudentStatus_View AS NextSS ON NextSS.StudentUID = S.StudentUID AND NextSS.TextTerm = 'FA-25' ;Explanation of key points:
• FirstTerm & SecondTerm CTEs stand in for your two SAP views (“FA-24” & “SP-25”).
• We use CROSS APPLY once per row to grab CumAttempted/Earned/GPA instead of repeating three scalar sub-queries.
• All of your original filters—campus, address type, student load, term—are pushed into the appropriate CTE.
• The final SELECT LEFT JOINs F (prior) onto S (current), then LEFT JOINs one more time against CAMS_StudentStatus_View for the “NextTerm” (‘FA-25’) record.
• Rounding, progress‐rate calculations, and labeling match your original view.Drop in and test – this one object replaces all three of your previous views. Hope it helps! 🚀
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.