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.
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
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