[SQL] Count binnen tijdrange

[armageddon_2k1]

Ik vind het lastig hoe ik dit probleem moet omschrijven, maar het is ongetwijfeld al 100x eerder gedaan, maar ik doe me best het uit te leggen.

code:

3 tabellen:

System
---------------------------
ID | Naam | ....

Repair
---------------------------
ID | SystemID | Date (Datetime)

Crashes
---------------------------
ID | SystemID | Date (Date) | TotalCrashes

Die laatste tabel geeft het aantal crashes aan per dag op een systeem.

Wat wil ik nou?
Ik wil een query maken waar ik de repairs kan opvragen en kan zien hoeveel crashes er binnen een x aantal dagen zijn geweest sinds de repair, inclusief de rijen waar er 0 zijn.

De queries waar ik mee kom geven niet de 0-rijen erbij, maar outer joinen wil ook niet helpen. Waarschijnlijk gaat er dus een kronkel in mijn brein niet helemaal goed. Ik heb nu zo iets:

SQL:

SELECT
  Repair.SystemID,
  Repair.Date as RepairDate,
  SUM(Crashes.TotalCrashes) AS CrashesCount
FROM Repair
INNER JOIN Crashes ON (Crashes.SystemID = Repair.SystemID)
WHERE
  Crashes.Date BETWEEN Repair.Date AND DATEADD(d, 7, Repair.Date)
GROUP BY
  Repair.SystemID,
  Repair.Date

Hieruit krijg ik dus een mooi overzicht, maar geen 0-rijen, terwijl ik die wel verwacht

Heeft iemand een duwtje in de goede richting? Ik heb al gegoogled op moving average (omdat dat mijn inziens vergelijkbare queries op moet leveren), maar daar vond ik voornamelijk oplossing waar je een tabel heel vaak op zich zelf moet joinen. Niet van toepassing hier.

[ Voor 3% gewijzigd door armageddon_2k1 op 03-12-2012 14:59 . Reden: SUM ipv COUNT ]

[Gtoniser]

Moet je geen SUM(Crashes.TotalCrashes) AS CrashesCount gebruiken in plaats van COUNT(Crashes.TotalCrashes) AS CrashesCount ?

Edit: Ik neem trouwens aan dat als er geen crashes waren dit ook in de 'Crashes' tabel staat (maar dan met 0 bij totalcrashes?) anders moet je een LEFT JOIN gebruiken ipv een INNER JOIN

[ Voor 44% gewijzigd door Gtoniser op 03-12-2012 15:01 ]

[armageddon_2k1]

Jup je hebt gelijk. Overtiepfoutje. In feite maakt het niks uit, want de TotalCrashes kolom is altijd > 0.

[Gtoniser]

In dat geval, zie mijn edit mbt LEFT JOIN hierboven.

[armageddon_2k1]

Ik ga het eens proberen. Mijn queries duren wat lang.... (externe SQL server die niet heel erg snel is).

EDIT 1: Schijnt te werken inderdaad. Ik ga de data nog even valideren in Excel, maar het ziet er beter uit. Thanks!

Wat betreft joins heb ik deze link gevonden: http://www.codinghorror.c...anation-of-sql-joins.html
Erg behulpzaam gebleken

EDIT 2: Voor het nageslacht even de uiteindelijke oplossing. Er zat nog een kleine subtiliteit in.
Ik wilde dus een overzicht van alle crashes binnen een week na een repair. Omdat de crashes tabel alleen crashes noteerde heb je genoeg aan een LEFT OUTER JOIN.

Maar dan moet ik vervolgens in de query niet het volgende doen:

SQL:

WHERE 
  Crashes.Date BETWEEN Repair.Date AND DATEADD(d, 7, Repair.Date) 

Want dan krijg ik natuurlijk alleen degene waar wel crashes voor zijn.
Ik moet dus ook de rijen pakken waar er geen data vanuit Crashes is, want daar is het aantal crashes dus 0. Dit resulteert in de volgende totale query:

SQL:

SELECT 
  Repair.SystemID, 
  Repair.Date as RepairDate, 
  SUM(Crashes.TotalCrashes) AS CrashesCount 
FROM Repair 
INNER JOIN Crashes ON (Crashes.SystemID = Repair.SystemID) 
WHERE 
  Crashes.Date BETWEEN Repair.Date AND DATEADD(d, 7, Repair.Date) 
OR
 Crashes.Date IS NULL
GROUP BY 
  Repair.SystemID, 
  Repair.Date

Na validatie met een enorme pivottable in Excel klopt dit

[ Voor 115% gewijzigd door armageddon_2k1 op 03-12-2012 15:34 ]

[Gtoniser]

In de uiteindelijk query staat nog een INNER JOIN maar ik neem aan dat dit een overneemfout is

[Sikkek]

armageddon_2k1 schreef op maandag 03 december 2012 @ 15:05:
...
Maar dan moet ik vervolgens in de query niet het volgende doen:

SQL:

WHERE 
  Crashes.Date BETWEEN Repair.Date AND DATEADD(d, 7, Repair.Date) 

Want dan krijg ik natuurlijk alleen degene waar wel crashes voor zijn.
Ik moet dus ook de rijen pakken waar er geen data vanuit Crashes is, want daar is het aantal crashes dus 0. Dit resulteert in de volgende totale query:

SQL:

SELECT 
  Repair.SystemID, 
  Repair.Date as RepairDate, 
  SUM(Crashes.TotalCrashes) AS CrashesCount 
FROM Repair 
INNER JOIN Crashes ON (Crashes.SystemID = Repair.SystemID) 
WHERE 
  Crashes.Date BETWEEN Repair.Date AND DATEADD(d, 7, Repair.Date) 
OR
 Crashes.Date IS NULL
GROUP BY 
  Repair.SystemID, 
  Repair.Date

...

Je kunt het where statement beter opnemen in de ON clause, dan hoef je dat hele OR gedeelte niet te gebruiken:

SQL:

SELECT 
  Repair.SystemID, 
  Repair.Date as RepairDate, 
  SUM(Crashes.TotalCrashes) AS CrashesCount 
FROM Repair 
LEFT JOIN Crashes 
  ON (Crashes.SystemID = Repair.SystemID) 
  AND Crashes.Date BETWEEN Repair.Date AND DATEADD(d, 7, Repair.Date) 
GROUP BY 
  Repair.SystemID, 
  Repair.Date

[P.O. Box]

als er op een datum geen repair is zal die datum niet meegenomen worden... ik weet niet of die kans aanwezig is, maar als dat zo is, kun je beter eerst een tijdelijke tabel vullen met de gewenste datums en die tijdelijke tabel als basis gebruiken...