Overlaping Dates - 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)

Overlaping Dates

Author

Topic

Hus
Starting Member

4 Posts

Posted - 2011-06-23 : 13:18:34

Can someone please assist me this? I 'm creating a spell episode date however the dates are overlapping.
E.g
Data
id Seqno Start date Enddate
123 2 01/04/2007 to 05/05/2007
123 2 10/10/2008 to 20/12/2008
123 2 18/12/2008 to 01/02/2009
123 2 01/03/2009 to
123 2 04/04/2009 to 05/05/2009
156 3 01/01/2011 to 03/03/2011
160 3 01/01/2011

How would I do the query which will show me result as

123 01/04/2007 to 05/05/2007. Spell 1
123 10/10/2008 to 01/02/2009 Spell 2
123 01/03/2009 to 05/05/2009 Spell 3
156 01/01/2011 to 03/03/2011 Spell 1
160 01/01/2011 Spell 1

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-06-24 : 03:10:54

SELECT id ,Seqno, Start date, Enddate, ROW_NUMBER() OVER (PARTITION BY id ORDER BY Start) AS Spell
FROM YourTable

--------------------------
http://connectsql.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources