[MsSQL] query op peildatum op waarden met alleen begindatum

[Milmoor]

Ik heb een set waarden met alleen een begindatum. Daarvan wil ik de waarde hebben die op een specifieke peildatum geldig is.

Als de waarden ook een einddatum hebben is het makkelijk:
SELECT value
FROM table
WHERE startdate <= mydate AND
(enddate IS NULL OR enddate >= mydate)

Maar hoe doe ik dat netjes als ik alleen een startdatum heb en het moment van niet meer geldig zijn afgeleid wordt van of er een waarde is met een hoger startdatum is? Dit doe ik nu:

SELECT value
FROM table LEFT OUTER JOIN table_later ON
table.startdate < table_later.startdate AND
table.startdate <= mydate AND
table_later.startdate <= mydate AND
table_later.startdate IS NULL

Ik heb dus twee keer de tabel gejoined met de startdatum <= peildatum, waarbij de tweede met een left outer join gekoppeld is (dus NULL mag zijn) en NULL moet zijn en een startdatum > dan de eerste tabel moet hebben. Een ingewikkelde manier om te zeggen dat er geen waarden mogen zijn die tussen de opgeleverde waarden en de peildatum inliggen. Dit is natuurlijk niet mooi. Maar hoe doe ik dit wel goed?

[ Voor 0% gewijzigd door Milmoor op 05-11-2014 22:10 . Reden: enters in query toegevoegd ]

[NMe]

Heb je nog enige invloed op het model? Dit gaat namelijk een stukje makkelijker als je niet de startdatum opslaat in je nieuwe record en de einddatum weglaat maar de einddatum opslaat in je oude record. De startdatum heb je dan in principe zelfs niet meer nodig.

[Milmoor]

Helaas niet. Dat is afkomstig van de leverancier van onze basisadministratie. Ik kan zaken toevoegen, maar van de basistabellen moet ik afblijven. Ik zie trouwens niet hoe je het zonder startdatum zou kunnen queryen als je wel een einddatum hebt.

[Gomez12]

Het is een beetje db-afhankelijk of het sneller is of niet.

Maar wat ik over het algemeen doe bij dit soort dingen is een subquery in de trant van :

SQL:

select waarde,max(startdatum)
from foo
where startdatum<peildatum
group by waarde

en dan heb je je waarde en datum die je moet hebben en kan je in je outer-query de rest van de gegevens erbij pakken.

Of als je alleen een willekeurige waarde nodig hebt dan een subquery als volgt

SQL:

select max(startdatum)
from foo
where startdatum<peildatum

En dan in je outer-query je waarde bij die datum pakken.

Het is net een andere benadering, ik ben niet geinteresseerd of iets een nog hogere startdatum heeft, ik ben enkel geinteresseerd in de hoogste startdatum die voor de peildatum ligt en van daaruit zoek ik de rest van de gegevens erbij

[Milmoor]

Ik snap hem nog niet. Stel je hebt.

waarde, startdatum
6, 1 januari 2011
2, 4 juni 2012
2, 1 december 2013
2, 5 december 2013
4, 5 januari 2014

Dan geeft jouw eerste query met peildatum 2 december 2013
6, 1 januari 2011
2, 1 december 2013

De tweede geeft met peildatum 2 december 2013:
1 december 2013

Je uitgangspunt ben ik het inderdaad mee eens, dat is niet van belang. Wat ik zoek is trouwens (en het zijn WOZ waarden van huurwoningen):
2, 1 december 2013

[ Voor 12% gewijzigd door Milmoor op 05-11-2014 22:23 ]

[Merethil]

Milmoor schreef op woensdag 05 november 2014 @ 22:19:
De tweede geeft met peildatum 2 december 2013:
1 december 2013

Je uitgangspunt ben ik het inderdaad mee eens, dat is niet van belang. Wat ik zoek is trouwens (en het zijn WOZ waarden van huurwoningen):
2, 1 december 2013

Zoals hij verder al zei: Je kan die max(startdatum) dan weer verder gebruiken in een join of subquery zodat je de bijbehorende waarde kan vinden.

[ Voor 21% gewijzigd door Merethil op 06-11-2014 07:20 ]

[Milmoor]

Duh, ik was niet meer helder:

SELECT value
FROM table
WHERE startdate =
(SELECT MAX(startdate)
FROM table
WHERE startdate<mydate)

[Rotterdammertje]

In MsSQL kan je toch ook gebruik maken van analytic functions?

SQL:

SELECT id FROM
(
  SELECT id, value, LEAD(value) OVER (ORDER BY startdate) AS next_value
  FROM table
) AS fixed_table
WHERE value <= x AND next_value > x;

[ Voor 3% gewijzigd door Rotterdammertje op 06-11-2014 10:53 ]

[Wes46]

Kun je niet simpel dit doen:

SQL:

SELECT TOP 1 Value FROM Table WHERE StartDate <= mydate ORDER BY StartDate desc

Ik zal wel iets over het hoofd zien aangezien dit wel heel simpel zou zijn.

[ Voor 22% gewijzigd door Wes46 op 06-11-2014 12:54 ]

[Gomez12]

Wes46 schreef op donderdag 06 november 2014 @ 12:52:
Kun je niet simpel dit doen:

SQL:

SELECT TOP 1 Value FROM Table WHERE StartDate <= mydate ORDER BY StartDate desc

Ik zal wel iets over het hoofd zien aangezien dit wel heel simpel zou zijn.

In theorie kan het, maar het zou absoluut niet mijn geprefereerde manier zijn. Je hebt nu namelijk subtiel de vraag veranderd naar "Geef mij de 1e waarde die geldig is op een specifieke peildatum".
Dat hoeft nu geen probleem te zijn, maar als er ooit een situatie komt dat je meerdere waardes hebt op 1 startdatum dan krijg je niet meer alles uit je originele vraag.

Dat zijn nu net van die leuke dingetjes die je of heel expliciet moet opschrijven dat je de beginvraag aangepast hebt, of die je over 2 jaar vele dagen bughunten gaan kosten omdat je niet meer alles terugkrijgt wat je wel verwacht.

[Wes46]

Klopt je krijgt alleen maar 1 waarde terug maar dat werd dus niet gespecificeerd.

Daarvan wil ik de waarde hebben die op een specifieke peildatum geldig is.

Mocht je toch uiteindelijk meerdere waarden willen hebben dan zou ik dat niet afhandelen in SQL maar die logica ergens anders plaatsen.

[Milmoor]

In dit geval is er altijd maar een waarde actief, dus dat gaat goed. Dus voor mij een prima oplossing. Achteraf heel logisch, maar vooraf was dat nog niet duidelijk. Bedankt voor het meedenken.