I have two tables first one
Fee_Payable_to_Students
and another one
Fee_Assign_Waiver_to_Students
it contains value as
Fee_Payable_to_Students
f_co |S_Adm_No | apr | may | june | jul | aug | sep | oct | nov | dec | jan | feb | mar
1 |s_1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5
2 |s_1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5
Fee_Assign_Waiver_to_Students
f_co|S_Adm_No | apr | may | june | jul | aug | sep | oct | nov | dec | jan | feb | mar
1 |s_1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5
I want to view my result as
S_Adm_No | Installment | Amount |Payable_Date
s_1 |Quarter-1 (April, May & June) | 5 |Apr 15, 2018
s_1 |Quarter-2 (July, August & September) | 5 |Jul 15, 2018
s_1 |Quarter-3 (October, November & December) | 5 |Oct 15, 2018
s_1 |Quarter-4 (January, February & March) | 5 |Jan 15, 2019
my sql query is here................
What I have tried:
BEGIN
SELECT unPvt.S_Adm_No, Installment,
sum(Amount) AS Amount, CASE
WHEN Installment = 'Quarter-1 (April, May & June)' THEN 'Apr 15, 2018'
WHEN Installment = 'Quarter-2 (July, August & September)' THEN 'Jul 15, 2018'
WHEN Installment = 'Quarter-3 (October, November & December)' THEN 'Oct 15, 2018'
WHEN Installment = 'Quarter-4 (January, February & March)' THEN 'Jan 15, 2019' END AS Payable_Date
FROM ( SELECT pc.S_Adm_No,
(Apr + May + Jun)-COALESCE(CON.Qa1,0) AS [Quarter-1 (April, May & June)],
(Jul + Aug + Sep)-COALESCE(CON.Qa2,0) AS [Quarter-2 (July, August & September)],
(Oct + Nov + Dec)-COALESCE(CON.Qa3,0) AS [Quarter-3 (October, November & December)],
(Jan + Feb + Mar)-COALESCE(CON.Qa4,0) AS [Quarter-4 (January, February & March)]
FROM Fee_Payable_to_Students pc
LEFT JOIN
(
SELECT S_Adm_no,
sum(E_Apr+E_May+E_Jun) Qa1,
sum(E_Jul+E_Aug+E_Sep) Qa2,
sum(E_Oct+E_Nov+E_Dec) Qa3,
sum(E_Jan+E_Feb+E_Mar) Qa4
FROM Fee_Assign_Waiver_to_Students w
group by S_Adm_No
) AS CON ON pc.S_Adm_no = CON.S_Adm_no
where pc.S_Adm_No=s_1) AS Pvt UNPIVOT
(Amount FOR Installment IN
([Quarter-1 (April, May & June)],
[Quarter-2 (July, August & September)],
[Quarter-3 (October, November & December)],
[Quarter-4 (January, February & March)])) AS unPvt
GROUP BY unPvt.S_Adm_No,unPvt.Installment