DateDIff ???
-
I am presently pulling data into a report from a maintenance database. I have a problem trying to use the datediff function. I only have one date that I can use to get the information that I need. I have a start date for different maintenance procedures and need to calculate the difference between the start date of events on a certian machine. I need to query for the same column but I need tha data from 2 consecutive rows, the start date from the first event and the start date each of the next events and the datediff from each. I am grouping the data by machine and sorting by start date. Hope this makes sense. Is there a way around this or another function I should be using. My table looks something like this- fairly simple report but having problems with this formula.
Equipment_No, WO_NO, Start Date, Length of Downtime
9171 00000 5-5-85 3
9171 00501 12-10-04 12
9171 00888 12-15-04 5
Thanks Kurt
Kurt Kracaw
-
Here's an example from my Punch table
SELECT FkEmploye, DateDebut, DATEDIFF(hh, DateDebut,
(SELECT TOP 1 Datedebut
FROM dbo.Punch P2
WHERE P2.FkEmploye = PMain.FkEmploye AND P2.DateDebut > PMain.DateDebut
ORDER BY DateDebut)) AS Diff
FROM dbo.Punch PMain
your query would look something like this :
Select Equipment_No, WO_NO, [Start Date],
DATEDIFF(d, DateDebut,
(SELECT TOP 1 [Start Date] FROM dbo.YourTable P2
WHERE P2.Equipment_No = PMain.Equipment_No AND P2.[Start Date] > PMain.[Start Date] ORDER BY [Start Date])) AS [Length of downtime] FROM dbo.YourTable PMain
BTW make sure Equipment_No and [Start date] are indexed. It's even better if it's a unique compound index (much much faster to select the date from the next row... that being to longest operation of the query).
-
Fantastic!! I am still not sure how it works (this is still all very new to me) but it works and gave exactly what I am looking for. Thanks for the great and timely response.
Kurt Kracaw
-
This is the part that does it :
(SELECT TOP 1 [Start Date] FROM dbo.YourTable P2
WHERE P2.Equipment_No = PMain.Equipment_No AND P2.[Start Date] > PMain.[Start Date] ORDER BY [Start Date])
This query is rerun at every line that is returned. Sql server fetches the first date (p2.[Start Date]) that is bigger than the date of the current line (PMain.[Start Date]). This is why there's an order by clause in the subquery. Also
P2.Equipment_No = PMain.Equipment_No tells sql server to get the next date for the current machine instead of any next date.
-
Thanks again this will be very useful in the future!
Kurt Kracaw
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply