DateDiff accross rows - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

DateDiff accross rows

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-06-07 : 08:59:21

I have the followin table:
Item | StatusID | ChangeDate
-----------------------------
1 | 1 | 20020101
1 | 2 | 20020103
1 | 3 | 20020112
2 | 1 | 20020201
...

I need a statement that will present the following:

Item | Status1Duration(days) | Status2Duration | Status3Duration
-----------------------------------------------------------------
1 | 3 | 9 | 146 (GetDate - 20020112)
2 | 126 (GetDate - 20020201) | NULL | NULL

Any Ideas?

Edited by - scott on 06/07/2002 09:00:09

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-07 : 09:13:10

Scott,
Post the DDL for these tables, and include insert statements for the sample data you've shown in your post.

setBasedIsTheTruepath
<O>

Scott
Posting Yak Master

145 Posts

Posted - 2002-06-07 : 09:24:28

CREATE TABLE [StatusDuration] (
[Item] [varchar] (50) NOT NULL ,
[StatusID] [int] NULL ,
[ChangeDate] [smalldatetime] NULL
) ON [PRIMARY]

Insert INTO StatusDuration (Item, StatusID, ChangeDate) VALUES (1, 1, '20020101');
Insert INTO StatusDuration (Item, StatusID, ChangeDate) VALUES (1, 2, '20020103');
Insert INTO StatusDuration (Item, StatusID, ChangeDate) VALUES (1, 3, '20020112');
Insert INTO StatusDuration (Item, StatusID, ChangeDate) VALUES (2, 1, '20020201');

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-07 : 09:35:27


select 
	s1.item, 
	datediff( dd, s1.changedate, isnull(s2.changedate,current_timestamp) ) as status1duration,
	datediff( dd, s2.changedate, s3.changedate ) as status2duration,
	datediff( dd, s3.changedate, current_timestamp ) as status3duration
from #statusduration s1
	left join #statusduration s2 on ( s1.item = s2.item and s2.statusID = 2 )
	left join #statusduration s3 on ( s2.item = s3.item and s3.statusID = 3 )
where s1.statusID = 1

one question: your example gives the status1duration on item 1 as three days, but the data says two days.

setBasedIsTheTruepath
<O>

Edited by - setbasedisthetruepath on 06/07/2002 09:35:56

Scott
Posting Yak Master

145 Posts

Posted - 2002-06-07 : 09:46:09

That's what happens when you do it in your head!

Thanks

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 10:02:28

OK setbasedisthetruepath, you beat me. But here goes anyway.

select s1.item,
datediff(dd, s1.changedate,coalesce(s2.changedate,getdate())) Status1Duration,
datediff(dd, coalesce(s2.changedate,null),coalesce(s3.changedate,getdate())) Status2Duration,
datediff(dd, coalesce(s3.changedate,null),getdate()) Status3Duration
from statusduration s1
left outer join statusduration s2 on s1.item = s2.item
and s1.statusid+1 = s2.statusid
left outer join statusduration s3 on s2.item = s3.item
and s2.statusid+1 = s3.statusid
where s1.statusid = 1

enough said.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources