Interesting Date subtraction - 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 2005 Forums

Transact-SQL (2005)

Interesting Date subtraction

Author

Topic

killtacularmania
Starting Member

22 Posts

Posted - 2009-12-04 : 13:54:48

Hey guys I am totally stuck on a task I need to complete...I have an enrollment table that contains peoples "coverage". If you were to open the table it would look like something below

PersonID Name StartDt EndDt CovGap
123456 John Doe 1/1/2008 NULL NULL
123456 John Doe 1/15/2007 12/31/2007 1
123456 John Doe 1/1/2006 12/31/2006 16
987654 Jane Doe 2/1/2008 NULL NULL
987654 Jane Doe 12/31/2006 12/31/2007 NULL
654321 Monica Doe 1/1/2009 NULL NULL

I need to figure out the CovGap which is going to be the difference in days between their EndDt and their NEXT StartDt. So for John Doe I would need to take his oldest coverage Enddt which is 12/31/2006 and see how many days are between that and the next StartDt which for him would be 1/15/2007. This is some new coding so I don't have anything to go off of and I am not even sure where to begin. Thanks a lot guys

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-04 : 14:55:31

Here's one way:

With policies (id, personid, name, startdt, enddt)
as (
	select	row_number() over(partition by personid order by startdt), personid, name, startdt, enddt from yourTable
)

update	yourTable
set	CovGap = datediff(day, old.enddt, new.startdt)
from	yourTable t
join	policies old
on	old.personid = t.personid
and	old.enddt = t.enddt
join	policies new
on	new.personid = old.personid
and	new.id = old.id + 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources