De PHPhulp kroeg
MySQL SELECT met CASE / SUM voor korting
Ik heb de volgende tabellen:
- varianten (opdrachten)
- varianten_datums (datums onder een opdracht)
- varianten_inhoud (producten per datum)
Het was eerst vrij eenvoudig om per maand de totale omzet op te halen.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
SUM(varianten_inhoud.inhoud_aantal * varianten_inhoud.inhoud_prijs) AS omzet
,MONTH(varianten_datums.datum) AS maand
,YEAR(varianten_datums.datum) AS jaar
FROM
varianten_inhoud
LEFT JOIN varianten_datums ON
varianten_inhoud.variant_datum_ID = varianten_datums.variant_datum_ID
LEFT JOIN varianten ON
varianten_datums.variant_ID = varianten.variant_ID
GROUP BY
MONTH(varianten_datums.datum)
, YEAR(varianten_datums.datum)
SUM(varianten_inhoud.inhoud_aantal * varianten_inhoud.inhoud_prijs) AS omzet
,MONTH(varianten_datums.datum) AS maand
,YEAR(varianten_datums.datum) AS jaar
FROM
varianten_inhoud
LEFT JOIN varianten_datums ON
varianten_inhoud.variant_datum_ID = varianten_datums.variant_datum_ID
LEFT JOIN varianten ON
varianten_datums.variant_ID = varianten.variant_ID
GROUP BY
MONTH(varianten_datums.datum)
, YEAR(varianten_datums.datum)
Maar nu is er in de tabel varianten een variabele korting bijgekomen. Die heeft betrekking op de gehele opdracht, niet specifiek op één datum of één product. Als ik deze variabele niet meer reken is de totale omzet natuurlijk te hoog.
Hoe kan ik dit qua query het beste doen?
Het is geen probleem om de korting te berekenen voor alleen maar "varianten_datums.soortdatum = 0" maar er moet dan dus iets met een CASE achtigs iets in de query.
Heeft iemand tips?
Gewijzigd op 07/02/2019 15:21:28 door Ramon van Dongen
In de SELECT kun je gewoon rekenen met de waarden uit de gejoinde tabellen.
Volgens mij doen pakketten als Magento dit ook: die trekken gewoon een kopie van de op dat moment geldende prijzen en kortingen als er een bestelling uit gaat.
Bedankt voor je snelle reactie.
De korting is een vast bedrag (decimal).
Code (php)
1
2
3
2
3
SELECT
(CASE WHEN varianten_datums.soortdatum = 0 THEN (SUM(varianten_inhoud.inhoud_aantal * varianten_inhoud.inhoud_prijs) - varianten.korting) ELSE SUM(varianten_inhoud.inhoud_aantal * varianten_inhoud.inhoud_prijs) END) AS omzet
...
(CASE WHEN varianten_datums.soortdatum = 0 THEN (SUM(varianten_inhoud.inhoud_aantal * varianten_inhoud.inhoud_prijs) - varianten.korting) ELSE SUM(varianten_inhoud.inhoud_aantal * varianten_inhoud.inhoud_prijs) END) AS omzet
...
Werkt in elk geval niet. Bedragen kloppen dan niet.
Toevoeging op 07/02/2019 15:56:40:
Thomas van den Heuvel op 07/02/2019 15:47:33:
Zo zou de prijs ook over tijd kunnen veranderen. Dit zou ook inhouden dat omzet uit het verleden ineens meer of minder zou kunnen worden.
De normale huidige prijs van producten wordt ook in een assortiment tabel opgeslagen. Dit betreft echt de verkochte producten per opdracht. Dus als later de prijs verandert, blijft het per opdracht uit het verleden gewoon kloppen.
Voorbeeld:
Opdracht 1 [tabel varianten]
- Datum: 2018-12-31 (soort = 0) [tabel varianten_datums]
-- 1x Product A a € 20,00 = € 20 [tabel varianten_inhoud]
-- 3x Product B a € 10,00 = € 30 [tabel varianten_inhoud]
- Datum: 2018-12-31 (soort = 1) [tabel varianten_datums]
-- 1x Product C a € 15,50 = € 15,50 [tabel varianten_inhoud]
- Datum: 2019-01-01 (soort = 1) [tabel varianten_datums]
-- 1x Product C a € 15,50 = € 15,50 [tabel varianten_inhoud]
Subtotaal: € 81,00
Korting: € 6,00 (staat dus in tabel varianten)
Totaal: € 75,00
Ik wil dus voor de maand december (€20 + €30 + € 15,50) - €6 = €59,50 en voor januari €15,50
Gewijzigd op 07/02/2019 16:00:25 door Ramon van Dongen
Uit de eerdere query werd een array samengesteld waarmee grafieken en tabellen per maand en kwartaal werden gemaakt.
Ik heb nu een tweede query gemaakt die nogmaals door de eerder gemaakte array loopt om de korting eraf te halen. Dat werkt goed en snel.
Dus dan maar even met 2 querys.