March 17, 2015 at 5:46 pm
I want to assign consecutive numbers to a block of data, where 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
(
SELECTTestId,
TestDate,
ROW_NUMBER() OVER(
PARTITION BYTestId
ORDER BYTestId, TestDate
)AS OrderId
FROMdbo.tblDatesSequenceTest
)
SELECT*
FROMcte
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.
Any help in this is much appreciated.
March 18, 2015 at 8:50 am
Do a web search gaps and islands itzik ben gan. Or dwain camps instead of itzik. This is a very classic problem with MANY solutions, including some very slick, efficient ones in SQL Server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 18, 2015 at 9:02 am
i am reading up on gaps and islands itzik ben gan too now..path to self eductaion or self destruction. 😛
TheSQLGuru (3/18/2015)
Do a web search gaps and islands itzik ben gan. Or dwain camps instead of itzik. This is a very classic problem with MANY solutions, including some very slick, efficient ones in SQL Server.
March 18, 2015 at 11:15 am
It will expand your mind and your TSQL tool kit - both of which are desirable!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2015 at 6:34 pm
TheSQLGuru (3/18/2015)
Do a web search gaps and islands itzik ben gan. Or dwain camps instead of itzik. This is a very classic problem with MANY solutions, including some very slick, efficient ones in SQL Server.
Actually, I would say that a simpler article closer to what the OP is trying to do would be this one by Jeff Moden:
Group Islands of Contiguous Dates (SQL Spackle)[/url]
I think that will ultimately lead you to something like this:
SELECT TestID, TestDate, g
,OrderID=DENSE_RANK() OVER (PARTITION BY TestID ORDER BY g)
FROM
(
SELECT TestID, TestDate
,g=TestDate-ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY TestDate)
FROM tblDatesSequenceTest
) a;
Nothing wrong with a little mind expansion through background reading though!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply