Click here to Skip to main content
15,868,069 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted
Updated 1-Apr-18 22:20pm
v2

1 solution

What I would suggest is that instead of storing different months as columns you'd store them as rows in a new table. Each row would have a date column defining the month and another column defining the actual value plus a reference to the parent table.

Consider following table example

PayablePerMonth
- f_co int, foreign key to Fee_Payable_to_Students
- month date, date defining the month
- value int, the actual value.


The rows could now be (date in mm/dd/yyyy -format)
f_co month    value
---- -----    -----
1    1/1/2018 5
1    2/1/2018 5
1    3/1/2018 5
... and so on


The same idea goes with Fee_Assign_Waiver_to_Students.

Having the data like this would make it simple to use DATEPART (Transact-SQL) | Microsoft Docs[^] to extract the quarter and then group by the quarter.

So the basic query would be something like
SQL
SELECT DATEPART(quarter, p.Month) as quarter,
       SUM(p.value - a.value)
FROM ...
GROUP BY DATEPART(quarter, p.Month)


The join would be made between the new tables month and other appropriate columns.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900