Onderwerp: SQL Uitdaging

Ik zit met een SQL uitdaging waar ik zelf en enkele vrienden niet meer uitkomen.
Ik heb een 2-tal tabellen, namelijk producten, prijzen.
Ik ben opzoek naar de geldende prijs op een bepaalde datum. Dit kan ik bepalen door de prijs ingegaan op de datum van de laatste prijswijziging voor de gewenste datum op te zoeken.
Voorbeeld: Product 1001 heeft Sinds 1-1-2007 een prijs van 12,18 euro, per 1-3-2007 is de prijs verlaagd naar 9,75 euro.
Wanneer ik de tabellen koppel dan krijg ik alle in de geschiedenis geldende prijzen.

Een limit 1 is geen optie, dit omdat het aantal producten en verschillende prijzen onbekend is bij het aanroepen van de query.


Bron tabellen:

Prijzen:

Code:

+---------+-----------+-------+------------+------------+    
| PrijsID | ProductID | Prijs | Datum      | BTWGroepID |    
+---------+-----------+-------+------------+------------+    
|       1 |      1001 | 12.18 | 2007-01-01 |          1 |    
|       2 |      1001 |  9.75 | 2007-03-01 |          1 |    
|       3 |      1002 | 12.18 | 2007-01-01 |          1 |    
|       4 |      1002 | 10.00 | 2007-03-22 |          1 |    
|       5 |      1001 | 12.18 | 2007-03-26 |          1 |    
+---------+-----------+-------+------------+------------+

Producten:

Code:

+-----------+----------------------------------+
| ProductID | Naam                             |
+-----------+----------------------------------+
|      1001 | Product 1001                     |
|      1002 | Product 1002                     |
+-----------+----------------------------------+

SQL:

Code:

select PRODUCTEN.ProductID, Datum, Prijs from PRODUCTEN left outer join PRIJZEN as PRIJZEN on PRODUCTEN.ProductID=PRIJZEN.ProductID where PRIJZEN.Datum < '2007-03-25' GROUP BY Datum, ProductID, Naam, Prijs ORDER BY Datum Desc;

Resultaat:

Code:

+-----------+------------+-------+
| ProductID | Datum      | Prijs |
+-----------+------------+-------+
|      1002 | 2007-03-22 | 10.00 |
|      1001 | 2007-03-01 |  9.75 |
|      1001 | 2007-01-01 | 12.18 |
|      1002 | 2007-01-01 | 12.18 |
+-----------+------------+-------+

Het gene dat ik wil bereiken is eigenlijk het volgende:

Code:

+-----------+------------+-------+
| ProductID | Datum      | Prijs |
+-----------+------------+-------+
|      1001 | 2007-03-01 |  9.75 |
|      1002 | 2007-03-22 | 10.00 |
+-----------+------------+-------+

Kortom elk product maximaal 1 keer tonen, met de meest recente datum, en de daar bij behorende prijs.

ff denken...

wat je WEL kan doen..
is per product opslaan OF querien hoeveel wijzigingen er zijn geweest, standaard is dat 1
Doe dit met een select COUNT en zorg dat je key/indexen goed staan

Die gooi je dan in je LIMIT zoals je zelf al aangeeft.
En dan kun je ook nog fijn ORDER BY datum DESC doen of een ander commando om te sorteren.

Easy....

select a.ProductID, a.Datum, a.Prijs, max(b.Datum) from Prijzen a, Prijzen b
where a.ProductID = b.ProductID
and a.Datum < '2007-03-25'
and b.Datum < '2007-03-25'
group by a.ProductID, a.Datum
having a.Datum = max(b.Datum)

Zoiets ongeveer...

Even wat snel neergezet.

Code:

SELECT prijzen.ProductID, prijzen.Datum, prijzen.Prijs
FROM prijzen
LEFT JOIN ( 

SELECT prijzen.ProductID AS pid, MAX(prijzen.Datum) AS pdate 
FROM prijzen 
WHERE prijzen.Datum < '2007-03-25' 
GROUP BY prijzen.ProductID

) p2 ON prijzen.ProductID=p2.pid AND prijzen.Datum=p2.pdate 

WHERE p2.pdate IS NOT NULL

Waarom heb je die 2e tabel, als je daar toch geen gegevens uithaalt?

Oorspronkelijk geplaatst door bakkerl

Even wat snel neergezet.

Code:

SELECT prijzen.ProductID, prijzen.Datum, prijzen.Prijs
FROM prijzen
LEFT JOIN ( 
 
SELECT prijzen.ProductID AS pid, MAX(prijzen.Datum) AS pdate 
FROM prijzen 
WHERE prijzen.Datum < '2007-03-25' 
GROUP BY prijzen.ProductID
 
) p2 ON prijzen.ProductID=p2.pid AND prijzen.Datum=p2.pdate 
 
WHERE p2.pdate IS NOT NULL

Waarom heb je die 2e tabel, als je daar toch geen gegevens uithaalt?

Textbook voorbeeld. Dit zijn standaardqueries die je op elke it opleiding wel een keer voor je neus krijgt. Bovenstaande voorbeeld werkt ook wel maar mijn versie is een beetje efficiënter. Zeker als de tabellen wat groter gaan worden zal je dat merken.
Ik haal trouwens max(b.Datum) uit die 2de tabel aanzien dit niet in de 1ste tabel aanwezig is...

Oorspronkelijk geplaatst door systemdeveloper

Textbook voorbeeld. Dit zijn standaardqueries die je op elke it opleiding wel een keer voor je neus krijgt. Bovenstaande voorbeeld werkt ook wel maar mijn versie is een beetje efficiënter. Zeker als de tabellen wat groter gaan worden zal je dat merken.
Ik haal trouwens max(b.Datum) uit die 2de tabel aanzien dit niet in de 1ste tabel aanwezig is...

Ik zal hem ook vast wel ooit voor bij zien komen, alleen doe ik nu niet zoveel meer met databases, dus de having was ik geheel vergeten

De 2e tabel bedoelde ik eigenlijk tegen de TS . Die gegevens worden nu geheel niet gebruikt. Tenzij je er ooit nog een vriendelijke naam ipv alleen een productid wil vermelden is die pas nodig.

Het geheel is een extract uit een query die ongeveer 8 tabellen combineert.
Ik heb hem dus zo eenvoudig mogelijk gemaakt om geen onnodige poespas te hebben en daardoor verblind te worden.

Helaas hielp mij dat niet.

Ik wist niet dat je een subquery in een join kon gebruiken.
Het having-verhaal is mij wel bekend maar gaf problemen door de afwezigheid van een "group by" gedeelte.

Nu ik dus weet dat er een subquery in een join mag, heb ik mijn overige 4 problemen (afgeleiden van dit probleem) ook op kunnen lossen.

Overigens dit zijn geen standaard textboox examples van een willekeurige IT opleiding naar mijn idee.
Ik heb in mijn opleiding wel wat SQL gedaan, ook join's maar subquery's in joins dus nooit.

Tenslotte mijn dank voor iedereen die mij hierbij heeft geholpen