CODEWITHSUNDEEP
sql-serverrow-number
mike
mike

Reputation: 13

SQL Server - Resetting row count within the window

I have some data in linear months and need to get a count of only the recent consecutive months. For example if a person was a member of a health club for the first two months of 2014, then dropped out for 4 months, then signed up for the remaining 6 months, I need to show that they have been members for 6 consecutive months.

Here is the query and table data I am using to try and get a row count. The YearMo tracks the possible list of years and months. The ismember column tracks whether or not they were a member - 1 means they were a member, nc followed by the yearmo value (nc201403) indicates months when they were not a member.

In the monthcount column you can see that the row_number does not recognize the months they were not a member, it just picks up counting once they become a member again. Instead of showing 8 as the final monthcount, I need it to be 6 with the count having started over again in 201407 (july 2014).

SELECT member_id, YearMo, ismember, 
ROW_NUMBER() OVER(PARTITION BY member_id, ismember ORDER BY members.yearmo ) AS 'monthcount'
FROM #temp_members WHERE member_id = '12345678' ORDER BY yearmo 


member_id   YearMo  ismember    monthcount
12345678    201401  1               1
12345678    201402  1               2
12345678    201403  nm201403        1
12345678    201404  nm201404        1
12345678    201405  nm201405        1
12345678    201406  nm201406        1
12345678    201407  1           3
12345678    201408  1           4
12345678    201409  1           5
12345678    201410  1           6
12345678    201411  1           7
12345678    201412  1           8

Any thoughts on how to get my count reset after the break in membership would be appreciated! Also open to methods of doing it without the window /partition. (No cursors please. Sixteen million records to deal with )

Upvotes: 1

Views: 643

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using difference of ROW_NUMBERs:

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY member_id ORDER BY YearMo)
            - ROW_NUMBER() OVER(PARTITION BY member_id, CASE WHEN ismember <> '1' THEN 0 ELSE 1 END ORDER BY YearMo)
    FROM #temp_members
)
SELECT
    member_id, 
    YearMo,
    ismember,
    monthcount = ROW_NUMBER() OVER(PARTITION BY member_id, rn, ismember ORDER BY YearMo)
FROM Cte ORDER BY member_id, YearMo

**TRY IT HERE**

Upvotes: 1

Related Questions