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

• summer12in

  SSC Enthusiast

  Points: 161

  More actions

  June 6, 2015 at 9:13 am

  #304720

  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.

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  June 6, 2015 at 10:40 am

  #1803514

  You are being too complicated, this is actually quite simple.

  This works for the data you have posted:

  --create test data

  set dateformat dmy ;

  create table #tim (start datetime2, end1 datetime2);

  insert #tim 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) ;

  --create and display result

  with dts (dt) as (select start as dt from #tim union select end1 as dt from# tim),

  dtsordered (dt, rn) as (select dt, row_number() over(order by isnull(dt,'31/12/9999 23:59:59.999999999')) as rn from dts)

  select a.dt as start, b.dt as end1 from dtsordered a, dtsordered b where a.rn+1 = b.rn order by a.rn ;

  --tidy up

  drop table #tim

  If you are using datetime instead of datetime2, you will need to change 31/12/9999 23:59:59.999999999 to the latest possible value in that type.

  Tom