Resultaten cumulative krijgen
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
We hebben ook code-tags.
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
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.
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.
Gewijzigd op 07/10/2020 16:08:01 door Ad Fundum
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.
Gewijzigd op 09/10/2020 16:06:23 door Ad Fundum