CODEWITHSUNDEEP
sqlsql-server-2008
user410911
user410911

Reputation: 65

How to return consecutive date ranges frm a set of dates in sql

I have a set of dates from a table in sql. I want to return date ranges and I need help.

So if I had dates like this

PK  - Date                                      
160 - 2013-04-16 12:09:00   
160 - 2013-04-17 11:07:00   
162 - 2013-04-16 12:10:00   
160 - 2013-04-20 12:10:00

I want for example 

PK   - beg                   -  end
160  -  2013-04-16 12:09:00  -  2013-04-17 11:07:00
160  -  2013-04-17 11:07:00  -  2013-04-20 12:10:00
162  -  2013-04-16 12:10:00  -  2013-04-16 12:10:00

Can you please help me.

Thank you

I am using Microsoft SQL Server Management Studio 10.0.1600.22

Upvotes: 0

Views: 487

Answers (1)

zimdanen
zimdanen

Reputation: 5626

Here's an example using MS SQL Server 2008 (though should work for 2005+):

WITH CTE AS
(
  SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY PK ORDER BY Date ASC), *
  FROM Dates
)
SELECT
  A.PK, A.Date [beg], COALESCE(B.Date, A.Date) [end]
FROM
  CTE A
  LEFT JOIN CTE B
    ON B.PK = A.PK AND B.RowNum = A.RowNum + 1
WHERE
  B.RowNum IS NOT NULL OR A.RowNum = 1

This will get row numbers for each PK, then provide the joins between the table and itself, filtering out any rows that don't have a match UNLESS they are the first row for that PK.

Here's the table:

CREATE TABLE Dates (PK INT, Date DATETIME)

INSERT INTO Dates VALUES
  (160, '2013-04-16 12:09:00'),
  (160, '2013-04-17 11:07:00'),
  (162, '2013-04-16 12:10:00'),
  (160, '2013-04-20 12:10:00')

SQL Fiddle here (hopefully - been having issues).

Upvotes: 1

Related Questions