Resultaten cumulative krijgen
Hoi allemaal, ik heb deze query geschreven, alleen het lukt niet om de kolommen te cumuleren. dus periode 1 = 1 periode 2 is 2+1 periode 3 is weer de optelsom van 1+2+3 enz..
Hoe kan ik dit doen
Hoe kan ik dit doen
Quote:
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
= '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
Kan je de query ook ont-spaghetti'en?
We hebben ook code-tags.
We hebben ook code-tags.
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
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
Voer je deze query rauw uit op je database, of communiceert een scriptingtaal (PHP?) met de database? In dit laatste geval zou je dit probleem (deels) kunnen overhevelen naar deze taal, waarbij je de query eenvoudig houdt en alles simpelweg groepeert op maand. In de scriptingtaal kun je dan de cumulatieve optelling doen. Verdeel en heers.
En anders kun je misschien denken aan een mysql-procedure. Of wellicht kun je hier wat inspiratie vinden.
NB aLternatief: als dit historische gegevens betreft die toch niet meer veranderen zou je dit van tevoren kunnen uitrekenen en in een aparte kolom kunnen opslaan.
En anders kun je misschien denken aan een mysql-procedure. Of wellicht kun je hier wat inspiratie vinden.
NB aLternatief: als dit historische gegevens betreft die toch niet meer veranderen zou je dit van tevoren kunnen uitrekenen en in een aparte kolom kunnen opslaan.
Wat je zoekt heet in databasetermen een 'running total'.
Het werkt via een window-function als SUM() waarbij je ook de voorgaande rijen meeneemt.
Afhankelijk van het beoogde resultaat kan je iets doen als:
Voor de volledige syntax zie: https://www.postgresql.org/docs/current/functions-window.html
Mocht je gedoemd zijn om met MySQL te werken dan is er goed nieuws: sinds versie 8 kan MySQL het kunstje eindelijk ook.
Zie voor details: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
Een running total opnieuw bedenken en uitprogrammeren in PHP is niet nodig, dat kan je beter aan de database over laten. Want die kan dat sneller en efficiënter dan dat je dat in PHP kan doen.
Het werkt via een window-function als SUM() waarbij je ook de voorgaande rijen meeneemt.
Afhankelijk van het beoogde resultaat kan je iets doen als:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT
SUM(AmountDCDebit - AmountDCCredit)
OVER (
ORDER BY ReportingDate
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS "subtotaal"
FROM ...
SUM(AmountDCDebit - AmountDCCredit)
OVER (
ORDER BY ReportingDate
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS "subtotaal"
FROM ...
Voor de volledige syntax zie: https://www.postgresql.org/docs/current/functions-window.html
Mocht je gedoemd zijn om met MySQL te werken dan is er goed nieuws: sinds versie 8 kan MySQL het kunstje eindelijk ook.
Zie voor details: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
Een running total opnieuw bedenken en uitprogrammeren in PHP is niet nodig, dat kan je beter aan de database over laten. Want die kan dat sneller en efficiënter dan dat je dat in PHP kan doen.
Ad Fundum op 07/10/2020 16:03:23:
Een running total opnieuw bedenken en uitprogrammeren in PHP is niet nodig, dat kan je beter aan de database over laten. Want die kan dat sneller en efficiënter dan dat je dat in PHP kan doen.
Mwa. Welke gegevens je gebruikt en hoe je deze gebruikt zijn twee verschillende dingen. Wat als je straks ook het verschil per maand wilt hebben? Dit is een eenvoudigere aanpassing in PHP als je maandtotalen hebt dan wanneer je een query om moet schrijven (en moet testen) voor dat doel. Er is voor beide wat te zeggen.
Thomas van den Heuvel op 07/10/2020 16:13:36:
Wat als je straks ook het verschil per maand wilt hebben?
Je kunt het makkelijk opdelen met het PARTITION BY statement.
Postgres: https://www.postgresql.org/docs/current/tutorial-window.html
MySQL: https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
Als je dat door de database laat doen hoef je niet extra tests te schrijven voor je PHP code.
En met sneller bedoel ik: als je de data eerst via TCP moet overbrengen naar PHP FPM kost dat extra resources, en qua dataverwerking is PHP altijd trager dan de database. Dat ga je merken als je grotere hoeveelheden data langs moet lopen, of grotere aantallen HTTP requests moet verwerken.




