Create date range with 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)

Create date range with records

Author

Topic

jgandara
Starting Member

18 Posts

Posted - 2004-12-06 : 18:13:46

I have a transaction table like this:
Key Date
1 10/12/2003
1 11/12/2003
1 12/12/2003

From this talbe, I want to get something like this:
Key StartDate EndDate
1 10/12/2003 11/12/2003
1 11/12/2003 12/12/2003

I want to create date ranges for every 2 records. Is it possible to get this in a query? I can use a cursor, but I want to avoid it.

Thx

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-06 : 19:35:32

select t1.key, t1.date, t2.date
from tbl t1
join tbl t2
on t2.date = (select min(t3.date) from tbl t3 where t3.date > t1.date and t1.key = t3.key)

==========================================
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.

jgandara
Starting Member

18 Posts

Posted - 2004-12-07 : 11:43:35

You got it, it works pretty well, thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources