March 6, 2011 at 3:24 am
hi all,
i hava table with account number and datetime ( as exmple)
name datatime
xxx 16:00
xxx 17:00
xxx 21:00
yy 15:30
yy 16:00
i need to get resault set like this :
nam startdatetime anddatetime
xxx 16:00 17:00
xxx 17:00 21:00
xxx 21:00 24:00
yy 15:30 16:00
thanks sh
March 6, 2011 at 6:10 am
I would do it like this. I've done it in the past for DW slowly changing dimension generation and it performed quite well.
declare @table Table (name nvarchar (10), StartTime datetime)
Insert into @table (name, StartTime)
SELECT 'xxx', '06 Mar 2011 16:00' UNION
SELECT 'xxx', '06 Mar 2011 17:00' UNION
SELECT 'xxx', '06 Mar 2011 21:00' UNION
SELECT 'yyy', '06 Mar 2011 15:30' UNION
SELECT 'yyy', '06 Mar 2011 16:00'
SELECT start.name, start.starttime, finish.starttime as finish/*, start.row as startrownumber, finish.row as finishrownumber*/
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY starttime) as Row, name, starttime
from @table) as start
LEFT OUTER JOIN
(SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY starttime) as Row, name, starttime
from @table) as finish ON start.row = (finish.row - 1) and start.name = finish.name
March 6, 2011 at 11:25 pm
thank you , its work great.
sharon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply