Assign Consecutive Numbers to a block of data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Assign Consecutive Numbers to a block of data

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-17 : 13:18:46

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

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.

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' )

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-18 : 03:30:54

[code]
;WITH tblDatesSequenceTestCTE
AS
(SELECT
TestId
, TestDate
,LAG(TestDate,1,TestDate) OVER(ORDER BY TestDate) AS PrevTestDate
FROM
tblDatesSequenceTest)

SELECT
TestId, TestDate
,SUM(DIFF) OVER(ORDER BY TestDate) + 1 AS OrderId
FROM
(
SELECT
TestId, TestDate
,CASE WHEN DATEDIFF(DAY,PrevTestDate,TestDate)<=1 THEN 0 ELSE 1 END AS DIFF
FROM
tblDatesSequenceTestCTE
)A
ORDER BY TestDate
[/code]

result:
[code
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
[/code]

sabinWeb MCP

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-18 : 05:10:01

Error Message:

Msg 195, Level 15, State 10, Line 6
'LAG' is not a recognized built-in function name.

quote:
Originally posted by stepson

;WITH tblDatesSequenceTestCTE
AS
(SELECT
TestId
, TestDate
,LAG(TestDate,1,TestDate) OVER(ORDER BY TestDate) AS PrevTestDate
FROM
tblDatesSequenceTest)

SELECT
TestId, TestDate
,SUM(DIFF) OVER(ORDER BY TestDate) + 1 AS OrderId
FROM
(
SELECT
TestId, TestDate
,CASE WHEN DATEDIFF(DAY,PrevTestDate,TestDate)<=1 THEN 0 ELSE 1 END AS DIFF
FROM
tblDatesSequenceTestCTE
)A
ORDER BY TestDate

result:
[code
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
[/code]

sabinWeb MCP

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-18 : 05:15:48

My SQL version is: Microsoft SQL Server 2008 R2 (SP2)

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-18 : 05:37:46

Sorry, I realised that I should have posted this on 2008 forum.

Any help here will be appreciated.

quote:
Originally posted by umertahir

My SQL version is: Microsoft SQL Server 2008 R2 (SP2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-19 : 05:47:17

[code]-- SwePeso
WITH cteData(TestID, TestDate, grp)
AS (
SELECT TestID,
TestDate,
DATEDIFF(DAY, '19000101', TestDate) - ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY TestDate) AS grp
FROM dbo.tblDatesSequenceTest
)
SELECT TestID,
TestDate,
DENSE_RANK() OVER (PARTITION BY TestID ORDER BY grp) AS OrderID
FROM cteData;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-19 : 06:05:04

spot on....this works like a charm.

Expected Output Achieved:


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 1
2           2013-01-03 00:00:00.000 1
2           2013-01-04 00:00:00.000 1
2           2013-08-03 00:00:00.000 2
2           2013-08-05 00:00:00.000 3
2           2013-09-02 00:00:00.000 4

quote:

Originally posted by SwePeso

-- SwePeso
WITH cteData(TestID, TestDate, grp)
AS (
	SELECT	TestID,
		TestDate,
		DATEDIFF(DAY, '19000101', TestDate) - ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY TestDate) AS grp
	FROM	dbo.tblDatesSequenceTest
)
SELECT	TestID,
	TestDate,
	DENSE_RANK() OVER (PARTITION BY TestID ORDER BY grp) AS OrderID
FROM	cteData;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

huangchen
Starting Member

37 Posts

Posted - 2015-04-02 : 05:54:49

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources