SQLTeam.com | Weblogs | Forums

How to calculate quarterly value


#1

Here's my output and now I need to calculate quarterly for each class in 3 months. Please help me and thanks in advance.
for example:
1st QUARTER FOR CIVILIAN = 0.89(1st month) + 0.44(2nd month) + 0.57(3rd month)
1st Quarter for Police = 0.44(1st month) + 0.15(2nd month) + 0.15(3rd month)
1st Quarter Fire = 0.0(1st month) + 0.28(2nd month) + 0.0 (3rd month)
continue as 2nd quarter, 3rd quarter and 4th.

Year Month Employee Class Percentage Quarterly (3 months)
2018 1 14 CIVILLIAN 0.89
2018 1 3 POLICE 0.44
2018 2 7 CIVILLIAN 0.44
2018 2 1 FIRE 0.28
2018 2 1 POLICE 0.15
2018 3 9 CIVILLIAN 0.57
2018 3 1 FIRE 0.28
2018 3 1 POLICE 0.15
2018 4 10 CIVILLIAN 0.63
2018 4 1 FIRE 0.28
2018 5 11 CIVILLIAN 0.7
2018 6 12 CIVILLIAN 0.76
2018 6 3 POLICE 0.44
2018 7 5 CIVILLIAN 0.32


#2
SELECT 
	[Year],
	[Quarter] = ([Month]-1)/3 +1,
	Class,
	[for Class] = SUM([Percentage Quarterly (3 months)])
FROM
	YourTable
GROUP BY
	[Year],
	([Month]-1)/3 +1,
	Class

#3

Hey James,

Thank you so much for your responded! However I am getting error message like this

Msg 207, Level 16, State 1, Line 5

Invalid column name 'Percentage Quarterly (3 months)'.

Can you please help.

Thanks


#4

Your original email implied that the column with the percentages was called [Percentage Quarterly (3 months)]. Try replacing that with whatever the percentage column is actually called.

Thanks

Mark


#5

Thank you! it worked.