I got a bit of challenge here, well a challenge for me in SQL, to assign consecutive numbers to a block of data.
A block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
1 2011-07-29 00:00:00.000
1 2011-07-30 00:00:00.000
1 2011-07-31 00:00:00.000
1 2011-08-01 00:00:00.000
1 2011-08-10 00:00:00.000
1 2011-08-12 00:00:00.000
1 2011-08-13 00:00:00.000
2 2013-01-02 00:00:00.000
2 2013-01-03 00:00:00.000
2 2013-01-04 00:00:00.000
2 2013-08-03 00:00:00.000
2 2013-08-05 00:00:00.000
2 2013-09-02 00:00:00.000
My Attempt:
WITH cte AS
(
SELECT TestId,
TestDate,
ROW_NUMBER() OVER (
PARTITION BY TestId
ORDER BY TestId, TestDate
)AS OrderId
FROM dbo.tblDatesSequenceTest
)
SELECT *
FROM cte
Create Table with Data to Test:
CREATE TABLE dbo.tblDatesSequenceTest ( TestId INT NOT NULL, TestDate DATETIME NOT NULL )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-07-21 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-07-22 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-07-27 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-07-29 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-07-30 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-07-31 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-08-01 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-08-10 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-08-12 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 1, '2011-08-13 00:00:00.000' )
-- Test 2
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 2, '2013-01-02 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 2, '2013-01-03 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 2, '2013-01-04 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 2, '2013-08-03 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 2, '2013-08-05 00:00:00.000' )
INSERT INTO dbo.tblDatesSequenceTest VALUES ( 2, '2013-09-02 00:00:00.000' )
Expected Output:
TestId TestDate OrderId
----------- ----------------------- --------------------
1 2011-07-21 00:00:00.000 1
1 2011-07-22 00:00:00.000 1
1 2011-07-27 00:00:00.000 2
1 2011-07-29 00:00:00.000 3
1 2011-07-30 00:00:00.000 3
1 2011-07-31 00:00:00.000 3
1 2011-08-01 00:00:00.000 3
1 2011-08-10 00:00:00.000 4
1 2011-08-12 00:00:00.000 5
1 2011-08-13 00:00:00.000 5
2 2013-01-02 00:00:00.000 6
2 2013-01-03 00:00:00.000 6
2 2013-01-04 00:00:00.000 6
2 2013-08-03 00:00:00.000 7
2 2013-08-05 00:00:00.000 8
2 2013-09-02 00:00:00.000 9
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.