[SQL] Verkopen samenvoegen van samengestelde artikelen

[Chandler479]

Ik zit met een uitdaging; ik wil de verkopen van artikelen in kaart brengen. Deze artikelen kunnen los verkocht worden, maar ook icm. andere artikelen in een package. Ik wil uiteindelijk een overzicht hebben van de totale verkopen, dus ook incl. andere artikelen.

Als test heb ik de volgende tabel gemaakt:

code:

Product Code    Naam    Parent1 Sales
1       4   Desktop     10
2       12  Losse Monitor   4   3
3       15  Muis        4   5
4       29  Toetsenbord 4   5
5       EXT Kabelslot   12  0

Zoals je ziet wordt een kabelslot icm een losse monitor verkocht. De totale sales van het slot komen dan op 0+3=3. Nu worden losse monitoren ook verkocht icm. desktops en zijn de totale sales de 0+3 van voorheen, maar dan ook nog +10 van de desktops. Totaal dus 13. Hoe los ik dat hier op?

Ik heb op het moment het volgende staan:

code:

SELECT Producten.*, Producten_1.Sales AS Related_Sales, [producten].[sales]+[related_sales] AS Total_sales
FROM Producten LEFT JOIN Producten AS Producten_1 ON Producten.Parent1 = Producten_1;

Dat werkt met één parent die zelf geen parent heeft.

[Dido]

Da's een leuke uitdaging, maar ik ben bang dat je echte recursiviteit binnen SQL mag vergeten (en het lijkt er toch op dat je dat nodig hebt).
Je moet anders van te voren weten hoeveel niveau's "diep" (of eigenlijk "omhoog") je gaat zoeken.

Een mogelijke oplossing zou zijn om de totale sales in je tabel bij te houden door middel van triggers: bij iedere update van de sales van product X pas je de sales van alle producten aan die dat product als parent hebben. (Dan moet je wel een dbms gebruiken dat die recursiviteit toestaat!)

[Verwijderd]

Recursieve queries kan wel in SQL Server 2005 en Oracle. Ik weet niet wat je gebruikt, maar het type DBMS kan belangrijk zijn om tot een juiste oplossing te komen.

[joth]

Specifiek voor deze parent-child relatie, zou je er ook voor kunnen kiezen om een "productgroep" tabel ernaast te zetten en af te stappen van de parent-child relatie. De totale verkoopprijs haal je dan uit de join tussen product en productgroep. Voorbeeldje:

ProductGroepNaam	Product
Monitor met kabelslot	5
Monitor met kabelslot	2
Desktop, Monitor, Kabelslot	5
Desktop, Monitor, Kabelslot	2
Desktop, Monitor, Kabelslot	1

Het leuke hieraan is dat je dit verder kan uitwerken in de richting van pakket-korting. Voeg een veld KortingsPercentage toe, en je kan 10% korting geven op een monitor icm een desktop, terwijl de monitor alleen toch aan volleprijs de deur uit gaat.

ProductGroepNaam	Product	KortingPercentage
Monitor met kabelslot	5	0
Monitor met kabelslot	2	0
Desktop, Monitor, Kabelslot	5	0
Desktop, Monitor, Kabelslot	2	10
Desktop, Monitor, Kabelslot	1	0

De query wordt dan iets in de genre van:

SQL:

select ProductGroepNaam, sum(p.Sales * ((100-pg.KortingPercentage)/100))
from producten as p inner join productgroep as pg on p.product=pg.product
group by pg.ProductGroepNaam

Je kan dit natuurlijk nog verder normaliseren.

[ Voor 3% gewijzigd door RobIII op 23-05-2007 13:57 ]

[RobIII]

offtopic:
@joth: code tags
En welkom op GoT

[Janoz]

Ikzelf zou het ook andersom oplossen, net als joth. Naast dat de oplossing veel efficienter is, is hij ook een stuk flexibeler. In je originele oplossing zul je bij elke printer een apparte usb kabel op moeten nemen, terwijl je met samengestelde producten gewoon telkens dezelfde usb kabel aan alle printers kunt koppelen.

Bijkomend voordeel is dat je exacte voorraden bij houd. Als je 5 producten hebt verkocht zijn er ook 5 producten weg en is dat compleet onafhankelijk van in welke combinaties dat product nog meer verkocht werd. Daarnaast kun je ook nog leuk varieren (denk bijvoorbeeld aan een tijdelijke aanbieding van een product A bij product . Dit hoeft alleen in de groepen tabel (handig door bijvoorbeeld een geldigheidsduur op te geven bij de combinatie)

[Chandler479]

joth schreef op woensdag 23 mei 2007 @ 13:32:
Specifiek voor deze parent-child relatie, zou je er ook voor kunnen kiezen om een "productgroep" tabel ernaast te zetten en af te stappen van de parent-child relatie. De totale verkoopprijs haal je dan uit de join tussen product en productgroep. Voorbeeldje:

ProductGroepNaam	Product
Monitor met kabelslot	5
Monitor met kabelslot	2
Desktop, Monitor, Kabelslot	5
Desktop, Monitor, Kabelslot	2
Desktop, Monitor, Kabelslot	1

Het leuke hieraan is dat je dit verder kan uitwerken in de richting van pakket-korting. Voeg een veld KortingsPercentage toe, en je kan 10% korting geven op een monitor icm een desktop, terwijl de monitor alleen toch aan volleprijs de deur uit gaat.

ProductGroepNaam	Product	KortingPercentage
Monitor met kabelslot	5	0
Monitor met kabelslot	2	0
Desktop, Monitor, Kabelslot	5	0
Desktop, Monitor, Kabelslot	2	10
Desktop, Monitor, Kabelslot	1	0

De query wordt dan iets in de genre van:

SQL:

select ProductGroepNaam, sum(p.Sales * ((100-pg.KortingPercentage)/100))
from producten as p inner join productgroep as pg on p.product=pg.product
group by pg.ProductGroepNaam

Je kan dit natuurlijk nog verder normaliseren.

Zo had ik het nog niet bekeken! Dit is een veel efficiëntere en ook mooiere oplossing voor dit vraagstuk. Hij doet het goed. Dank voor deze hulp!