Subtract dates in two separate records

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

Transact-SQL (2000)

Subtract dates in two separate records

Author

Topic

c4e4
Starting Member

16 Posts

Posted - 2006-04-05 : 20:20:16

I want to subtract the enddate in one record from the startdate in the prior record for each memberid in the database. If there is only one record for a memberid then the value would be 0. For the last record in the series for each memberid, the value would also be 0. The values should be days. The data is ordered by memberid and startdate.
Here is my data

memberid, startdate, enddate
1236 2005-01-10 2005-01-14
1236 2005-01-24 2005-02-03
1236 2005-06-27 2005-06-30
1236 2005-07-06 2005-07-11
1236 2005-07-25 2005-07-28
1236 2005-12-28 2005-12-30
3345 2005-02-01 2005-03-31
3345 2005-04-01 2005-05-30

I am familiar with DateDiff when querying the same row, however I am uncertain how to query across rows in a database.

Thanks for you help!

JLH

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-05 : 20:33:38

Odd thing to want to do
something like

update tbl
set startdate = t1.startdate - coalesce(t2.enddate, t1.startdate)
from tbl t1
left join tbl t2
on t1.memberid = t2.memberid
and t2.startdate = (select min(t3.startdate) from tbl t3 where t3.startdate > t1.startdate and t3.memberid = t1.memberid)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

c4e4
Starting Member

16 Posts

Posted - 2006-04-05 : 21:01:02

I did the update using your sql, but the values in the column are in a datetime format, instead of # of days.
startdate enddate startdate2
2005-01-10 2005-01-14 1899-12-08
2005-01-24 2005-02-03 1899-07-28
The task is not so odd if you are in the healthcare field and looking at a member's eligiblity in a healthplan -
I am trying to show the number of days in any gaps between a member's endddate and next startdate.
Again, your assistance is greatly appreciated!

JLH

c4e4
Starting Member

16 Posts

Posted - 2006-04-05 : 21:11:09

I tweaked the sql a bit and this seems to work:

Select t1.memberid,t1.startdate,t1.enddate,0 - DateDiff(dd,t2.startdate,t1.enddate)
from tbl t1
Left Outer join tbl t2
on t1.memberid = t2.memberid
and t2.startdate = (select min(t3.startdate) from tbl t3 where t3.startdate > t1.startdate and t3.memberid = t1.memberid)

This was a great help! Thanks!!!!

JLH

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-06 : 10:17:55

t1.startdate - t2.enddate
will give the number of days and time from start date to end date (in a datetime but you can convert to integer). You probably want it the other way round.
The coalesce is to get the value zero if there is no later date.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-06 : 10:44:16

0 - DateDiff(dd,t2.startdate,t1.enddate)???????

Are posters supplying their own obfuscatory NZDF answers now?

c4e4
Starting Member

16 Posts

Posted - 2006-04-06 : 22:53:54

Hey blindman, the code works, your sarcasm isn't necessary.

JLH

c4e4
Starting Member

16 Posts

Posted - 2006-04-06 : 23:08:56

And thanks NR - I appreciate the clarification!

JLH

c4e4
Starting Member

16 Posts

Posted - 2006-04-06 : 23:51:04

OK
This is what I ended up with:

Select t1.memberid,t1.startdat,t1.enddate,
Case
When DateDiff(dd,t1.enddate,t2.startdate) IS NOT NULL
THEN DateDiff(dd,t1.enddate,t2.startdate)
Else 0
End as DaysBtwn
From TBL t1
Left Outer Join TBL t2
on t1.memberid = t2.memberid
and t2.startdat = (select min(t3.startdat) from TBL t3 where t3.startdate > t1.startdate and t3.memberid = t1.memberid)

I couldn't seem to get the coalesce integrated into my code so I went with CASE.

Thanks again nr.
Any other helpful comments are welcome.

JLH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-07 : 00:37:33

same solution. 3 variation of using isnull and coalesce


Select  t1.memberid, t1.startdate, t1.enddate, 
        isnull(datediff(day, t1.enddate, t2.startdate), 0) as DaysBtwn
From 	TBL t1
	Left Outer Join TBL t2
on 	t1.memberid = t2.memberid
and 	t2.startdate = (select min(t3.startdate) from TBL t3 
                        where t3.startdate > t1.startdate and t3.memberid = t1.memberid)

Select  t1.memberid, t1.startdate, t1.enddate, 
        datediff(day, t1.enddate, isnull(t2.startdate, t1.enddate)) as DaysBtwn
From 	TBL t1
	Left Outer Join TBL t2
on 	t1.memberid = t2.memberid
and 	t2.startdate = (select min(t3.startdate) from TBL t3 
                         where t3.startdate > t1.startdate and t3.memberid = t1.memberid)

Select  t1.memberid, t1.startdate, t1.enddate, 
        datediff(day, t1.enddate, coalesce(t2.startdate, t1.enddate)) as DaysBtwn
From 	TBL t1
	Left Outer Join TBL t2
on 	t1.memberid = t2.memberid
and 	t2.startdate = (select min(t3.startdate) from TBL t3 
                         where t3.startdate > t1.startdate and t3.memberid = t1.memberid)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-07 : 09:08:19

Case
When DateDiff(dd,t1.enddate,t2.startdate) IS NOT NULL
THEN DateDiff(dd,t1.enddate,t2.startdate)
Else 0
End as DaysBtwn

is the same as
coalsece (DateDiff(dd,t1.enddate,t2.startdate),0) as DaysBtwn

but go with whatever you're happiest.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-07 : 09:43:50

[quote]Originally posted by c4e4

Hey blindman, the code works, your sarcasm isn't necessary./quote]Sure it works. As does this:

power((0 - DateDiff(dd, t2.startdate, t1.enddate))/1, 2)/(0 - DateDiff(dd, t2.startdate, t1.enddate))

But being a lazy typist I prefer simple code over needlessly complex or redundant code, hence:

DateDiff(dd, t1.enddate, t2.StartDate)

There is a lot of really bad code out there that "works" fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources