SQLTeam.com | Weblogs | Forums

Help with query

Good morning, I am working on a query in which the temporary table I make the sum of credits in which students enrolled, I make a second query where I bring the cancel credits , I want to create a column where show the total of credits, another column where I show the cancel credits and a third column where do the subtraction of the two columns.
First result

That is what i got for the moment.

WITH T2 AS

(

SELECT DISTINCT
STC_PERSON_ID
FROM STUDENT_ACAD_CRED SAC

INNER JOIN STC_STATUSES STC ON STC.STUDENT_ACAD_CRED_ID = SAC.STUDENT_ACAD_CRED_ID

WHERE STC_TERM = '22/2S'
AND STC_STATUS = 'X'
AND STC.POS = '1'
AND STC_STATUS_REASON = 'CNA'

)

-- First result All credits matriculated
SELECT

T2.STC_PERSON_ID,
P.FIRST_NAME + ' ' + P.LAST_NAME AS NAME,
SUM(STC_CRED)AS ENROLL_CRED

FROM T2

INNER JOIN STUDENT_ACAD_CRED ST1 ON ST1.STC_PERSON_ID = T2.STC_PERSON_ID
INNER JOIN STC_STATUSES STC ON STC.STUDENT_ACAD_CRED_ID = ST1.STUDENT_ACAD_CRED_ID
INNER JOIN PERSON P ON P.ID = T2.STC_PERSON_ID

WHERE STC_TERM = '22/2S'
AND STC.POS = '1'

GROUP BY T2.STC_PERSON_ID, P.FIRST_NAME + ' ' + P.LAST_NAME

ORDER BY STC_PERSON_ID

-- Second result credits drops
SELECT

T2.STC_PERSON_ID,
P.FIRST_NAME + ' ' + P.LAST_NAME AS NAME,
SUM(STC_CRED) AS DROP_CRED

FROM T2

INNER JOIN STUDENT_ACAD_CRED ST1 ON ST1.STC_PERSON_ID = T2.STC_PERSON_ID
INNER JOIN STC_STATUSES STC ON STC.STUDENT_ACAD_CRED_ID = ST1.STUDENT_ACAD_CRED_ID
INNER JOIN PERSON P ON P.ID = T2.STC_PERSON_ID

WHERE STC_TERM = '22/2S'
AND STC.POS = '1'
AND STC_STATUS = 'X'

GROUP BY T2.STC_PERSON_ID, P.FIRST_NAME + ' ' + P.LAST_NAME

ORDER BY STC_PERSON_ID
Second result

Hi, I managed to solve it.

SUM(STC_CRED)AS ENROLLED_CRED,
(SELECT SUM(CASE WHEN STC.STC_STATUS = 'X' THEN STC_CRED END)) AS CANCEL_CRED,
SUM(STC_CRED) - (SELECT SUM(CASE WHEN STC.STC_STATUS = 'X' THEN STC_CRED END)) AS END_CRED