SQL Query to find Timedifference between 2 items whose ID is same but different Status

[DrukenProgrammer]

Hello Experts, I have my data like below, ID Fname Date_TimeWorked Status 1 MyName 2006-10-25 10:00:05 Ready 1 MYName 2006-10-25 11:00:05 Completed 1 MyName 2006-10-25 10:30:00 I want to know the time difference Between Ready-completed, ready-canceled. How do I query this? Its urgent. Thanks in advance!!

[Wendelius]

For example:

SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes
FROM TableName start,
TableName end
WHERE start.ID = end.ID
AND start.Status = 'Ready'
AND end.Status = 'Completed'

Mika

[DrukenProgrammer]

I have this in one table, what is TableName start,TableName end? Thank you

[Wendelius]

Since I didn't know the actual name for your table I used only TableName. Let's say your table is named WorkData then the query would look like:

...
FROM WorkData start,
WorkData end
...

Start and end are aliases for the same table so that it can be referred twice. I used start alias (the alias can be whatever you want) for the row that defines the starting point for time and respectively end for ending point. Hope this helps, Mika

[DrukenProgrammer]

I just created a simple access database for it and tried, but it is prompting me Minute.. Entere Parameter value. ID Fname Date_TimeWorked Status 1 MyName 2006-10-25 10:00:05 Ready 1 MYName 2006-10-25 11:00:05 Completed 1 MyName 2006-10-25 10:30:00 Cancled

[Wendelius]

The code was for SQL Server which most of the people here use. It's been ages since I last used Access, but try just subtracting the two times (end.Date_TimeWorked - start.Date_TimeWorked). I'm not sure but it may give you the result you want.

[DrukenProgrammer]

Well I was playing with Excel sheets to database, so I was trying something on access database. and thank you for your reply, the query looks like it works.. but I got the idea which i was looking for, thanks :) :)

[Wendelius]

You're welcome :)

[DrukenProgrammer]

Hello Mika, I need your help on this query, you suggested me SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes FROM TableName start, TableName end WHERE start.ID = end.ID AND start.Status = 'Ready' AND end.Status = 'Completed' Ready-Complete ok, but I want one more condition added to it, I need time between Date_Timeworked when status is Ready and Rejected?

[Wendelius]

If you want them on separate rows, you can use UNION and duplicate the query (just modify Status for end condition) If you want them on the same row it would be something like (I'll change the aliases so that this becomes more clear):

SELECT DATEDIFF(minute, readyRow.Date_TimeWorked, completedRow.Date_TimeWorked) AS FromReadyToCompleted,
DATEDIFF(minute, readyRow.Date_TimeWorked, rejectedRow.Date_TimeWorked) AS FromReadyToRejected
FROM TableName readyRow
LEFT OUTER JOIN
TableName completedRow
ON readyRow.ID = completedRow.ID
LEFT OUTER JOIN
TableName rejectedRow
ON readyRow.ID = rejectedRow.ID
WHERE readyRow.Status = 'Ready'
AND completedRow.Status = 'Completed'
AND rejectedRow.Status = 'Rejected'

The query is now modified to outer joins since I believe that the same ID cannot be both completed and rejected at the same time. The example may contain typos, but you'll get the idea Hope this helps, Mika