lundi 29 juin 2015

In mssql: when I SUM values of each month between 2 dates, I get the wrong value

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