Auto-Incremented Number Series to a block

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 2008 Forums

Transact-SQL (2008)

Auto-Incremented Number Series to a block

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-18 : 05:39:54

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.

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-18 : 07:40:30

I managed to calculate difference between second and previous row:


SELECT	TestId,
		TestDate,
		DIFF
FROM	(
			SELECT		a.*,
					b.TestDate AS PreviousRecordDate,
					CASE WHEN DATEDIFF(DAY, b.TestDate, a.TestDate) <=1 THEN 0 ELSE 1 END AS DIFF
			FROM		dbo.tblDatesSequenceTest AS a
						LEFT OUTER JOIN	dbo.tblDatesSequenceTest AS b
							ON	a.TestId	=	b.TestId	AND
								a.TestDate	=	b.TestDate + 1
			ORDER BY	a.TestId, a.TestDate
		)AS a
ORDER BY	TestDate

Output:


TestId      TestDate                PreviousRecordDate      DIFF
----------- ----------------------- ----------------------- -----------
1           2011-07-21 00:00:00.000 NULL                    1
1           2011-07-22 00:00:00.000 2011-07-21 00:00:00.000 0
1           2011-07-27 00:00:00.000 NULL                    1
1           2011-07-29 00:00:00.000 NULL                    1
1           2011-07-30 00:00:00.000 2011-07-29 00:00:00.000 0
1           2011-07-31 00:00:00.000 2011-07-30 00:00:00.000 0
1           2011-08-01 00:00:00.000 2011-07-31 00:00:00.000 0
1           2011-08-10 00:00:00.000 NULL                    1
1           2011-08-12 00:00:00.000 NULL                    1
1           2011-08-13 00:00:00.000 2011-08-12 00:00:00.000 0
2           2013-01-02 00:00:00.000 NULL                    1
2           2013-01-03 00:00:00.000 2013-01-02 00:00:00.000 0
2           2013-01-04 00:00:00.000 2013-01-03 00:00:00.000 0
2           2013-08-03 00:00:00.000 NULL                    1
2           2013-08-05 00:00:00.000 NULL                    1
2           2013-09-02 00:00:00.000 NULL                    1

Now thinking about the next step

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2015-03-18 : 12:37:13

Perhaps, it is simpler than you think.

Try this


SELECT TestId,TestDate, OrderId = DENSE_RANK()OVER(ORDER BY GroupDate) FROM
(
	SELECT *, GroupDate = DATEADD(DAY,-ROW_NUMBER()OVER(ORDER BY TestDate),TestDate)
	FROM tblDatesSequenceTest
)temp

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-18 : 12:47:53

sweet....thanks for your help.

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

quote:

Originally posted by namman

Perhaps, it is simpler than you think.

Try this


SELECT TestId,TestDate, OrderId = DENSE_RANK()OVER(ORDER BY GroupDate) FROM
(
	SELECT *, GroupDate = DATEADD(DAY,-ROW_NUMBER()OVER(ORDER BY TestDate),TestDate)
	FROM tblDatesSequenceTest
)temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources