Partitioning to Count Consecutive Occurrences?

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26945 More actions · Answer 1

Try this

CREATE TABLE #Data(ShortDate DATETIME,FlowDirection INT)

INSERT INTO #Data(ShortDate,FlowDirection)

SELECT '20010101', 0 UNION ALL

SELECT '20010102', 1 UNION ALL

SELECT '20010103', 1 UNION ALL

SELECT '20010104', 0 UNION ALL

SELECT '20010105', -1 UNION ALL

SELECT '20010106', 0 UNION ALL

SELECT '20010107', 0 UNION ALL

SELECT '20010108', -1 UNION ALL

SELECT '20010109', -1 UNION ALL

SELECT '20010110', -1 UNION ALL

SELECT '20010111', 1;

WITH CTE AS (

SELECT ShortDate,FlowDirection,

ROW_NUMBER() OVER(ORDER BY ShortDate) - ROW_NUMBER() OVER(PARTITION BY FlowDirection ORDER BY ShortDate) AS rnDiff

FROM #Data)

SELECT ShortDate,FlowDirection,

ROW_NUMBER() OVER(PARTITION BY FlowDirection,rnDiff ORDER BY ShortDate) AS ConsecDays

FROM CTE

ORDER BY ShortDate;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

bkmooney SSCrazy Points: 2041 More actions · Answer 2

Thanks! Works in my small example, now I've just got to implement it with everything else!