[SQL] Één query met verschillende datums

[sihzies]

Op een database (Microsoft SQL Server 2008) probeer ik in de Management Studio een overzicht te maken van ordernummers met daarbij 2 verschillende datums (start- en einddatum) en de doorlooptijd (verschil in dagen).

ordernummer	startdatum	einddatum	doorlooptijd
123456	2016-06-15	2016-06-20	5

Hiervoor zijn 2 tabellen benodigd, Order en Taak die gekoppeld zijn door Order.id = Taak.order_id.

Om de startdatum te berekenen gebruik ik de volgende Query:

SQL:

SELECT o.ordernummer, MIN(t.startdatum)
FROM Order o
INNER JOIN Taak t
ON o.id = t.order_id
WHERE t.omschrijving='Start' 
GROUP BY o.ordernummer

En om de einddatum te berekenen gebruik ik de volgende Query:

SQL:

SELECT o.ordernummer, MAX(t.einddatum)
FROM Order o
INNER JOIN Taak t
ON o.id = t.order_id
WHERE t.omschrijving='Eind' 
GROUP BY o.ordernummer

Ik gebruik de GROUP BY clausule in combinatie met de MIN en MAX functies omdat het (theoretisch) kan voorkomen dat een taak met de specifieke omschrijving meerder keren kan voorkomen.

Het lukt me dus wel om de begin- en einddatum in aparte queries te zetten, maar ik krijg het niet voor elkaar om dit in 1 query te vatten. Wie geeft me een zetje de goede kant op?

[Robicide]

Volgens mij zou dit 't moeten doen:

SQL:

SELECT o.ordernummer, MIN(t.startdatum), MAX(t1.einddatum)
FROM Order o
INNER JOIN Taak t
ON o.id = t.order_id AND t.omschrijving='Start'
INNER JOIN Taak t1
ON o.id = t1.order_id AND t1.omschrijving='Eind'
GROUP BY o.ordernummer

Ik ben alleen niet in de gelegenheid om voor je te testen.

[nescafe]

Je kunt aggregates op deelselecties uitvoeren door binnen de functie niet-relevante waarden te onderdrukken in een berekende waarde (CASE WHEN..).

Bijvoorbeeld:

SQL:

SELECT o.ordernummer,
  MIN(CASE WHEN t.omschrijving = 'Start' THEN t.startdatum END),
  MAX(CASE WHEN t.omschrijving = 'Eind' THEN t.einddatum END)
FROM Order o
INNER JOIN Taak t
ON o.id = t.order_id
WHERE t.omschrijving IN ('Start', 'Eind')
GROUP BY o.ordernummer

Werkt ook voor conditionele count (SUM(CASE WHEN .. THEN 1 END)) etc.

[ACM]

nescafe schreef op woensdag 22 juni 2016 @ 21:20:
Je kunt aggregates op deelselecties uitvoeren door binnen de functie niet-relevante waarden te onderdrukken in een berekende waarde (CASE WHEN..).

Bijvoorbeeld:

SQL:

SELECT o.ordernummer,
  MIN(CASE WHEN t.omschrijving = 'Start' THEN t.startdatum END),
  MAX(CASE WHEN t.omschrijving = 'Eind' THEN t.einddatum END)

Wellicht is het ook nog handig om te melden waarom dat werkt

En dat komt door twee aspecten van die expressions: MIN en MAX - en de meeste andere aggregation functions - negeren NULL's.

En daarnaast geeft de impliciete default case van een CASE-statement ook NULL. Effectief dwing je dus af dat de startdatum wordt genegeerd in de MIN() bij een record dat niet Start heeft als omschrijving.

Werkt ook voor conditionele count (SUM(CASE WHEN .. THEN 1 END)) etc.

En SUM kan dat inderdaad ook, je zou ook gewoon count kunnen nemen:
COUNT(CASE WHEN x THEN x END) of COUNT(CASE WHEN x THEN x ELSE NULL END)
Of als het veld zelf null kan zijn wordt het natuurlijk nog simpeler.

Er zijn overigens nog veel meer wegen naar Rome, zoiets zou ook kunnen als de group by je in de weg zit. Of als je zo een index op Taak efficienter kan gebruiken, bijv een op [order_id, omschrijving, startdatum] of [order_id, omschrijving] (omschrijving mag ook eerst in beide gevallen):

SQL:

SELECT o.ordernummer, 
(SELECT MIN(t.startdatum) FROM Taak t WHERE t.order_id = o.id AND t.omschrijving = 'Start') as start,
(SELECT MAX(t.startdatum) FROM Taak t WHERE t.order_id = o.id AND t.omschrijving = 'Eind') as einde
FROM Order o

[sihzies]

Bedankt voor jullie reacties,

Ik heb zowel de oplossing van Robicide en nescafe getest, en deze geven mij het gewenste resultaat!