CODEWITHSUNDEEP
sql-server
Zee Shan
Zee Shan

Reputation: 27

Split one date column into two

I have a scenario where i want to split my date column into two date columns in SQL based on Date and Time difference. for example

I have a date column with date and time.

       Date
2011-12-31 15:10:00
2011-12-31 19:20:00
2011-12-31 20:33:00

Now i want to split it like.

     From Date                To Date
2011-12-31 15:10:00     2011-12-31 19:20:00
2011-12-31 19:20:00     2011-12-31 20:33:00

and so on....

Also, if there is any date difference i want to split again e.g;

     From Date                To Date
2011-12-31 15:10:00     2012-1-30 19:20:00
2011-1-30 19:20:00      2012-2-28 20:33:00

I hope i make it understandable. Please let me know if you need more clarifications.

Upvotes: 0

Views: 432

Answers (4)

Devart
Devart

Reputation: 122042

Try this one -

Query:

DECLARE @temp TABLE
(
      col DATETIME
)

INSERT INTO @temp (col)
VALUES 
    ('2011-12-31 15:10:00'),
    ('2011-12-31 19:20:00'),
    ('2011-12-31 20:33:00')

SELECT * 
FROM @temp t
OUTER APPLY (
    SELECT TOP 1 t2.col
    FROM @temp t2
    WHERE t2.col > t.col
    ORDER BY t2.col
) t2
WHERE t2.col IS NOT NULL

;WITH cte AS
(
  SELECT col, ROW_NUMBER() OVER(ORDER BY col) rn
  FROM @temp
)
SELECT f.col, t.col
FROM cte f
JOIN cte t ON f.rn + 1 = t.rn

Output:

col                     col
----------------------- -----------------------
2011-12-31 15:10:00.000 2011-12-31 19:20:00.000
2011-12-31 19:20:00.000 2011-12-31 20:33:00.000

Upvotes: 1

David Söderlund
David Söderlund

Reputation: 998

If I understood correctly you want to pair records together by how they are ordered. Here is an SQL-fiddle for the problem: http://sqlfiddle.com/#!6/02afb/7

rn in the cte rankedDates let us pair records by order.

WITH rankedDates as 
(
 SELECT
  id_Date
  ,ROW_NUMBER() over (order by id_Date)  as rn
  FROM test
)
SELECT
startd.id_Date as startDateTime
,endd.id_Date as endDateTime
FROM rankedDates as startd
INNER JOIN rankedDates as endd
  ON startd.rn +1 = endd.rn

Upvotes: 1

Arun Kumar
Arun Kumar

Reputation: 437

 You can try that using looping through the column , might not be perfect answer you look need to insert the result into a temp but this logic might work , the advantage is that you have have any other logic in to this code 

if object_id('tempdb..#Temp','u') is not  null
    Drop table #Temp
    Create Table #Temp
    (
    sno int identity(1,1),
    datevalue datetime 
    )

    insert into #temp values ('2011-12-31 15:10:00'),
            ('2011-12-31 19:20:00'),
            ('2011-12-31 20:33:00')
     Select * from #temp
     DEclare @loop int=1, @column1   datetime,@column2 datetime
     While (@loop<=(select max(sno) from #temp))
     Begin
           Select @column1 =(select datevalue from #temp where sno=@loop) ,@column2=(select datevalue from #temp where sno=@loop+1)
           Select @column1,@column2
    set @loop=@loop+1

     End

Thanks, Arun

Upvotes: 1

Sebastian Meine
Sebastian Meine

Reputation: 11823

Is this what you are looking for?

WITH numbered AS(
  SELECT [Date], ROW_NUMBER()OVER(ORDER BY [Date]) rn
  FROM dbo.YourTable
)
SELECT [From].Date AS [From Date], [To].Date AS [To Date]
FROM numbered AS [From]
JOIN numbered AS [To]
ON [From].rn + 1 = [To].rn
ORDER BY [From].Date;

Upvotes: 4

Related Questions