I am really struggling with a query. I want the sum of each month between Aug 2014 and July 2015. If I specify the between dates in the where clause it sums all the months.
Here is my query:
DECLARE @CurrentYear int = DATEpart(year,getdate())
DECLARE @PreviousYear int = DATEpart(year,getdate()) -1
SELECT
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'08') THEN a.balance ELSE 0 END) AS BalanceAug ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'09') THEN a.balance ELSE 0 END) AS BalanceSep ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'10') THEN a.balance ELSE 0 END) AS BalanceOct ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'11') THEN a.balance ELSE 0 END) AS BalanceNov ,
SUM(CASE WHEN a.fin_period = concat(@PreviousYear,'12') THEN a.balance ELSE 0 END) AS BalanceDec ,
...etc.
FROM subaccount_history a with (nolock)
WHERE fin_period between concat(@PreviousYear,'08') and concat(@CurrentYear,'12')
The issue is with the between clause, i tried group by but that also doesn't work. It sums everything between the 2 dates specified.
Aucun commentaire:
Enregistrer un commentaire