datediff between 2 rows of the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

datediff between 2 rows of the same table

Author

Topic

draylan
Starting Member

5 Posts

Posted - 2012-04-26 : 12:20:32

Hi,

I am new to SQL so please excuse my noob questions.
Per sample table below - for each distinct ID number, I would like to find the difference in Timestamp where Field1 goes from Open to InWork respectively. Note also the ID may not be in order.

ID Field1 Timestamp
1 Open 2009-03-06 04:27:00:000
1 InWork 2009-03-06 04:30:00:000
2 Open 2009-03-08 02:15:12:000
3 Open 2009-03-08 12:05:00:000
1 Other 2009-03-06 04:36:00:000
3 InWork 2009-03-08 12:31:00:000
4 Closed 2009-03-11 01:00:26:000
5 Other 2009-03-12 03:03:22:000
6 Other 2009-03-13 08:23:22:000

Output should look like:
ID Diff
1 3
3 26

All other rows are ignored. Can anyone please assist here? Thanks a lot in advance!

Regards,
David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 12:23:44

[code]
SELECT ID,
DATEDIFF(minute,MAX(CASE WHEN Field1 ='Open' THEN [Timestamp] END),MAX(CASE WHEN Field1 ='InWork' THEN [Timestamp] END)) AS Diff
FROM Table
WHERE Field1 IN ('Open','Inwork')
GROUP BY ID
HAVING MIN(Field1) <> MAX(Field1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

draylan
Starting Member

5 Posts

Posted - 2012-04-26 : 12:30:36

Wow that was fast. Thanks a lot. Seemed to work great! One more thing. Would it be more complex if instead of Open to InWork, it was

(Open OR Request) to InWork

Much appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 12:34:13

Welcome..
Ok for answer that i need few clarifications

1. can there be only one occurance of Open/request per ID? can both of them occur at same time?
2. if there are multiples, which value should be considered for calculating Diff? is it earliest or latest occurance?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

draylan
Starting Member

5 Posts

Posted - 2012-04-26 : 12:40:22

Hi,

There actually can be multiple, so the earliest would be great.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 12:43:27

So can i assume its multiples per Open/Request and what you want is earliest one out of them regardless of whether its Open or Request and take diff between that and Inwork?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

draylan
Starting Member

5 Posts

Posted - 2012-04-26 : 13:03:23

Hi,

Yes, sorry if this is confusing. So only 1 Open and/or 1 Request per ID. Not multiple Opens or Requests per distict ID. Hope that's more clear. Then the earliest of either minus the time for InWork.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 15:29:23

then it should be


SELECT ID,
DATEDIFF(minute,MIN(CASE WHEN Field1 IN ('Open','Request') THEN [Timestamp] END),MAX(CASE WHEN Field1 ='InWork' THEN [Timestamp] END)) AS Diff
FROM Table
WHERE Field1 IN ('Open','Request','Inwork')
GROUP BY ID
HAVING MIN(Field1) <> MAX(Field1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

draylan
Starting Member

5 Posts

Posted - 2012-04-27 : 10:03:38

Hi, sorry for the delay. That worked great! Thank you so much for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:16:46

welcome