Restart Counting with Row_Number()
-
I have a puzzle, I need to find out how many days an employee had a certain status in a row, like 1 at work, 9 vacation, 5 sick day ... I need to order by day ... I am using row_number() to count, but I need to restart counting everytime a status has changed. Can't figure it out. Any ideas are highly appreciated.
Result Set
MID /DATE /Status /Count of Consecutive Status
472006-07-13 91
472006-07-19 11
472006-09-18 12
472006-09-19 13
472006-09-21 14
472006-09-25 15
472006-09-26 16
472006-09-27 17
472006-09-28 18
472006-10-09 51
472006-10-10 19Restart with 1
472006-10-11 110
472006-10-17 111
472006-10-18 92
472006-10-24 112Restart with 1
My Query
select mid,datum,id_termine_status
,ROW_NUMBER() OVER (partition by mid,t.id_termine_status order by mid,datum) as nrOfStatus
from ZDB_PROD.PHS.phs_termine t
order by mid,datum
-
Can you provide us with Create table and insert table scripts. Makes it easier for us to help you better.
Do you have only one status per day? When you say " a certain status in a row" In a row actually means adajacent days..correct?
Regards,
http://Strictlysql.blogspot.com
Raj -
;WITH phs_termine AS (
SELECT mid = 47,datum = '2006-07-13',id_termine_status = 9 UNION ALL
SELECT 47,'2006-07-19',1 UNION ALL
SELECT 47,'2006-09-18',1 UNION ALL
SELECT 47,'2006-09-19',1 UNION ALL
SELECT 47,'2006-09-21',1 UNION ALL
SELECT 47,'2006-09-25',1 UNION ALL
SELECT 47,'2006-09-26',1 UNION ALL
SELECT 47,'2006-09-27',1 UNION ALL
SELECT 47,'2006-09-28',1 UNION ALL
SELECT 47,'2006-10-09',5 UNION ALL
SELECT 47,'2006-10-10',1 UNION ALL
SELECT 47,'2006-10-11',1 UNION ALL
SELECT 47,'2006-10-17',1 UNION ALL
SELECT 47,'2006-10-18',9 UNION ALL
SELECT 47,'2006-10-24',1
),
GroupedData AS (
SELECT mid, datum, id_termine_status,
GroupID = ROW_NUMBER() OVER (PARTITION BY mid ORDER BY datum, id_termine_status) -
DENSE_RANK() OVER (PARTITION BY mid, id_termine_status ORDER BY datum, id_termine_status)
FROM phs_termine
)
SELECT mid, datum, id_termine_status,
nrOfStatus = ROW_NUMBER() OVER(PARTITION BY GroupID ORDER BY datum)
FROM GroupedData
ORDER BY mid,datum
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Thanks, yes I mean adajacent days. Here a table script:
CREATE TABLE dbo.EmployeeStatus
(
MID intnot null
Datum datenot null
ID_Status intnot null
);
GO
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-02',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-03',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-04',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-05',5);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-07',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-08',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-10',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-11',5);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-12',5);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-13',5);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-14',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-15',9);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-17',9);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-18',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(7, '2012-01-19',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-02',9);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-03',9);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-04',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-05',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-07',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-08',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-10',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-11',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-12',1);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-13',5);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-14',5);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-15',5);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-17',9);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-18',9);
INSERT INTO dbo.EmployeeStatus(MID, Datum, ID_Status) VALUES(11, '2012-01-19',1);
-
[font="Verdana"]SWEEEEEET --- thank you!!!![/font] 😛
-
Much better sample set, thanks:
;WITH GroupedData AS (SELECT mid, datum, id_status,
GroupID = ROW_NUMBER() OVER (PARTITION BY mid ORDER BY datum, id_status) -
DENSE_RANK() OVER (PARTITION BY mid, id_status ORDER BY datum, id_status)
FROM dbo.EmployeeStatus
)
SELECT mid, datum, id_status,
nrOfStatus = ROW_NUMBER() OVER(PARTITION BY mid, id_status, GroupID ORDER BY datum)
FROM GroupedData
ORDER BY mid, datum
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Sample data copied from the first post:
DECLARE @data AS TABLE
(
MID integer NOT NULL,
[Date] date NOT NULL,
[Status] tinyint NOT NULL,
PRIMARY KEY (MID, [Date])
);
INSERT @data
(MID, [Date], [Status])
VALUES
(47, '2006-07-13', 9),
(47, '2006-07-17', 1), -- Changed!
(47, '2006-09-18', 1),
(47, '2006-09-19', 1),
(47, '2006-09-21', 1),
(47, '2006-09-25', 1),
(47, '2006-09-26', 1),
(47, '2006-09-27', 1),
(47, '2006-09-28', 1),
(47, '2006-10-09', 5),
(47, '2006-10-10', 1),
(47, '2006-10-11', 1),
(47, '2006-10-17', 1),
(47, '2006-10-18', 9),
(47, '2006-10-24', 1);
Solution:
WITH RecursiveCTE AS
(
-- Anchor: First date record per MID
SELECT
d.MID,
d.[Date],
d.[Status],
Sequence = 1
FROM @data AS d
WHERE
d.[Date] =
(
SELECT
MIN(d2.[Date])
FROM @data AS d2
WHERE
d2.MID = d.MID
)
UNION ALL
-- Recursive part
SELECT
q.MID,
q.[Date],
q.[Status],
q.Sequence
FROM
(
-- Next row in sequence of [Date]
SELECT
d.MID,
d.[Date],
d.[Status],
Sequence =
CASE
-- Same status, increment sequence
WHEN d.[Status] = r.[Status]
THEN R.Sequence + 1
-- Otherwise, restart sequence at 1
ELSE 1
END,
rn = ROW_NUMBER() OVER (
ORDER BY d.[Date])
FROM RecursiveCTE AS r
JOIN @data AS d ON
d.MID = R.MID
AND d.[Date] > R.[Date]
) AS q
WHERE
-- Current date is the first one
-- that is higher than the current
-- recursive date
q.rn = 1
)
SELECT *
FROM RecursiveCTE
OPTION (MAXRECURSION 0);
Output:
Query Plan:
-
Heh Paul that's too funny!
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
ChrisM@home (1/26/2012)
Heh Paul that's too funny!I thought the same thing! We must have been working on the opposite solutions at the same time. I must confess I still find the ROW_NUMBER/DENSE_RANK method quite counter-intuitive, but that may just be a reflection of how my mind works. The pattern I generally follow is the ROW_NUMBER/ROW_NUMBER method in Itzik's books, but I've never really warmed to that either - I always have to look it up. Thankfully, gaps & islands problems crop up rather infrequently in my real world. My attempts at forum questions of this kind are quite possibly more for my own benefit than the questioner's.
-
SQL Kiwi (1/26/2012)
ChrisM@home (1/26/2012)
Heh Paul that's too funny!I thought the same thing! We must have been working on the opposite solutions at the same time. I must confess I still find the ROW_NUMBER/DENSE_RANK method quite counter-intuitive, but that may just be a reflection of how my mind works. The pattern I generally follow is the ROW_NUMBER/ROW_NUMBER method in Itzik's books, but I've never really warmed to that either - I always have to look it up. Thankfully, gaps & islands problems crop up rather infrequently in my real world. My attempts at forum questions of this kind are quite possibly more for my own benefit than the questioner's.
Interesting. I've not read Itzik's books - perhaps I should - so I have to build the query from scratch each time, examining the results from each of the window functions in order to figure out the partitions. With a small data set this can be perilous as my first effort shows! Also, as you know, I don't yet fully trust the results from this type of query. However, it's early days for the rCTE method and folks expect to see the dual window function method. I was going to write the rCTE equivalent as a second post and explain a little about the differences between the two.
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
ChrisM@home (1/26/2012)
Interesting. I've not read Itzik's books - perhaps I shouldI definitely recommend them - particularly Inside Microsoft SQL Server 2008: T-SQL Querying.
...so I have to build the query from scratch each time, examining the results from each of the window functions in order to figure out the partitions. With a small data set this can be perilous as my first effort shows! Also, as you know, I don't yet fully trust the results from this type of query.
Me too - I refer to the book for the general method, and much T-SQLing ensues.
However, it's early days for the rCTE method and folks expect to see the dual window function method. I was going to write the rCTE equivalent as a second post and explain a little about the differences between the two.
Using an rCTE for this sort of sequencing problem isn't as new as that other thread we participated on recently, and the logic (to me anyway) is a lot clearer and easier to verify as correct.
-
SQL Kiwi (1/26/2012)
...Using an rCTE for this sort of sequencing problem isn't as new as that other thread we participated on recently, and the logic (to me anyway) is a lot clearer and easier to verify as correct.It's an obvious use for rCTE's really. I wanted to see if rCTEs can outperform the dual window method - and the results of testing in that thread show that they can, and suggest when an rCTE might be best choice. As to which is more intuitive, you only have to glance at the rCTE query to understand how it works. Maybe it's just me but the dual-window method only makes sense when I look at the column results!
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
ChrisM@home (1/26/2012)
SQL Kiwi (1/26/2012)
...Using an rCTE for this sort of sequencing problem isn't as new as that other thread we participated on recently, and the logic (to me anyway) is a lot clearer and easier to verify as correct.It's an obvious use for rCTE's really. I wanted to see if rCTEs can outperform the dual window method - and the results of testing in that thread show that they can, and suggest when an rCTE might be best choice. As to which is more intuitive, you only have to glance at the rCTE query to understand how it works. Maybe it's just me but the dual-window method only makes sense when I look at the column results!
I started down the rCTE path last night and then rechecked this thread. I saw the dense_rank solution and abandoned my rCTE solution. Kudos on that solution Chris.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (1/26/2012)
ChrisM@home (1/26/2012)
SQL Kiwi (1/26/2012)
...Using an rCTE for this sort of sequencing problem isn't as new as that other thread we participated on recently, and the logic (to me anyway) is a lot clearer and easier to verify as correct.It's an obvious use for rCTE's really. I wanted to see if rCTEs can outperform the dual window method - and the results of testing in that thread show that they can, and suggest when an rCTE might be best choice. As to which is more intuitive, you only have to glance at the rCTE query to understand how it works. Maybe it's just me but the dual-window method only makes sense when I look at the column results!
I started down the rCTE path last night and then rechecked this thread. I saw the dense_rank solution and abandoned my rCTE solution. Kudos on that solution Chris.
Cheers Jason 🙂
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
ChrisM@home (1/26/2012)
SQLRNNR (1/26/2012)
ChrisM@home (1/26/2012)
SQL Kiwi (1/26/2012)
...Using an rCTE for this sort of sequencing problem isn't as new as that other thread we participated on recently, and the logic (to me anyway) is a lot clearer and easier to verify as correct.It's an obvious use for rCTE's really. I wanted to see if rCTEs can outperform the dual window method - and the results of testing in that thread show that they can, and suggest when an rCTE might be best choice. As to which is more intuitive, you only have to glance at the rCTE query to understand how it works. Maybe it's just me but the dual-window method only makes sense when I look at the column results!
I started down the rCTE path last night and then rechecked this thread. I saw the dense_rank solution and abandoned my rCTE solution. Kudos on that solution Chris.
Cheers Jason 🙂
You showed me another thing I have to learn.:hehe:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply