CODEWITHSUNDEEP
sql-server-2008
sam
sam

Reputation: 1304

how to split the time column values into rows in sql server 2008R2

I have the table as

|start              ||        end1         |

1/06/2015 1:00      ||       1/06/2015 1:30

1/06/2015 2:00      ||       1/06/2015 3:00

1/06/2015 3:20      ||       1/06/2015 4:00

1/06/2015 4:00      ||       NULL

I want the output as : -

|start          ||    end1         |

1/06/2015 1:00  ||   1/06/2015 1:30

1/06/2015 1:30  ||   1/06/2015 2:00

1/06/2015 2:00  ||   1/06/2015 3:00

1/06/2015 3:00  ||   1/06/2015 3:20

1/06/2015 3:20  ||   1/06/2015 4:00

1/06/2015 4:00  ||   NULL

I am trying the below mentioned code but it is not giving me the desired output..

with cte as
(select 
     start
    ,end1
    ,ROW_NUMBER() over (order by (select 1)) as rn
 from #b 
),cte1 as 
( select top 1
    start
    ,end1
    ,rn
 from cte 
    union all
  select 
     a.end1
     ,(case when (b.rn) %2 = 0 then b.start else b.end1 end)
     ,b.rn
 from cte1 as a
    inner join cte as b
    on b.rn = a.rn +1 

)
select start,end1 
from cte1

I am getting wrong output as - 

| start        ||       end1    |  

1/06/2015 1:00 ||   1/06/2015 1:30  

1/06/2015 1:30 ||   1/06/2015 2:00  

1/06/2015 2:00 ||   1/06/2015 4:00  

1/06/2015 4:00 ||   1/06/2015 4:00

can someone help me please i am trying since past 2 hours and I am not getting the desired output.

Upvotes: 2

Views: 86

Answers (2)

nazark
nazark

Reputation: 1240

Lag may not work in sql 2008, try this

    declare @tb  table ([start] datetime, end1 datetime)
    insert into @tb ([start],end1) values(
    '1/06/2015 1:00','1/06/2015 1:30'),
    ('1/06/2015 2:00','1/06/2015 3:00'),
    ('1/06/2015 3:20','1/06/2015 4:00'),
    ('1/06/2015 4:00',NULL)   


    ;with ct as( select start,row_number() over (order by start) as rno
 from (select [start]  from @tb union select end1 from @tb ) t 
where start is not null)
    select start,end1 from ct t left join (select rno, start end1 from ct) t1 
on t.rno+1=t1.rno

Upvotes: 1

FutbolFan
FutbolFan

Reputation: 13763

You could use lead() function to do this:

;WITH cte1
AS (
    SELECT start
    FROM timeline --replace this with your tablename

    UNION ALL

    SELECT end1 start
    FROM timeline --replace this with your tablename
    )
    ,cte2
AS (
    SELECT start
        ,lead(start) OVER (
            ORDER BY start
            ) end1
    FROM cte1
    )
SELECT *
FROM cte2
WHERE start <> coalesce(end1, '1900-01-01 00:00')

Demo

Upvotes: 0

Related Questions