October 6, 2020 at 9:03 am
Hi I wrote this query and I want the result to be cumulated by period so period 1 result is 1 and periode 2 result is 2+1 and period 3 is 3+2(2+1) etc...
SELECT dbo.grtbk.reknr, dbo.grtbk.oms25_0, SUM(CASE WHEN MONTH(ReportingDate) = '1' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [1], SUM(CASE WHEN MONTH(ReportingDate)
= '2' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [2], SUM(CASE WHEN MONTH(ReportingDate) = '3' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [3],
SUM(CASE WHEN MONTH(ReportingDate) = '4' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [4], SUM(CASE WHEN MONTH(ReportingDate) = '5' THEN (AmountDCDebit - AmountDCCredit)
ELSE 0 END) AS [5], SUM(CASE WHEN MONTH(ReportingDate) = '6' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [6], SUM(CASE WHEN MONTH(ReportingDate)
= '7' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [7], SUM(CASE WHEN MONTH(ReportingDate) = '8' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [8],
SUM(CASE WHEN MONTH(ReportingDate) = '9' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [9], SUM(CASE WHEN MONTH(ReportingDate) = '10' THEN (AmountDCDebit - AmountDCCredit)
ELSE 0 END) AS [10], SUM(CASE WHEN MONTH(ReportingDate) = '11' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [11], SUM(CASE WHEN MONTH(ReportingDate)
= '12' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [12]
FROM dbo.grtbk LEFT OUTER JOIN
dbo.GeneralLedgerBalances ON dbo.GeneralLedgerBalances.GeneralLedger = dbo.grtbk.reknr AND YEAR(dbo.GeneralLedgerBalances.ReportingDate) = 2020
GROUP BY YEAR(dbo.GeneralLedgerBalances.ReportingDate), dbo.grtbk.oms25_0, dbo.grtbk.reknr
October 6, 2020 at 9:53 am
We can't run your question, so this is very difficult for us to answer. The best I can suggest is that I can suggest is that it sounds like you're talking about a cumulative sum, which means using a windowed SUM. In simple terms:
SELECT ID,
I,
SUM(I) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS S
FROM (VALUES(1,7),(2,12),(3,2))V(ID,I);
For sanity, as well, let's get some (good) formatting in that query:
SELECT dbo.grtbk.reknr,
dbo.grtbk.oms25_0,
SUM(CASE WHEN MONTH(ReportingDate) = '1' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [1],
SUM(CASE WHEN MONTH(ReportingDate) = '2' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [2],
SUM(CASE WHEN MONTH(ReportingDate) = '3' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [3],
SUM(CASE WHEN MONTH(ReportingDate) = '4' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [4],
SUM(CASE WHEN MONTH(ReportingDate) = '5' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [5],
SUM(CASE WHEN MONTH(ReportingDate) = '6' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [6],
SUM(CASE WHEN MONTH(ReportingDate) = '7' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [7],
SUM(CASE WHEN MONTH(ReportingDate) = '8' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [8],
SUM(CASE WHEN MONTH(ReportingDate) = '9' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [9],
SUM(CASE WHEN MONTH(ReportingDate) = '10' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [10],
SUM(CASE WHEN MONTH(ReportingDate) = '11' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [11],
SUM(CASE WHEN MONTH(ReportingDate) = '12' THEN (AmountDCDebit - AmountDCCredit) ELSE 0 END) AS [12]
FROM dbo.grtbk
LEFT OUTER JOIN dbo.GeneralLedgerBalances ON dbo.GeneralLedgerBalances.GeneralLedger = dbo.grtbk.reknr
AND YEAR(dbo.GeneralLedgerBalances.ReportingDate) = 2020
GROUP BY YEAR(dbo.GeneralLedgerBalances.ReportingDate),
dbo.grtbk.oms25_0,
dbo.grtbk.reknr;
Finally, I notice some you reference some columns using 3 part naming 9for example dbo.GeneralLedgerBalances.GeneralLedger. 3+ part naming for columns is due to be deprecated in SQL Server, and should be avoided. Instead alias your tables, and then qualify the columns with those aliases.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply