sql - Get a result set flagging where there is a history going back 3 months - Stack Overflow

admin管理员组

文章数量:1387286

I have a table called Addresses that stores address history for our members like this:

ID	FILE_MONTH	ADDRESS
5555555	202501	201 E RIDGEWAY DR
5555555	202502	201 E RIDGEWAY DR
5555555	202503	201 E RIDGEWAY DR
6666666	202501	906 BRET LANE
6666666	202502	906 BRET LANE
6666666	202503	100 W 4TH ST
7777777	202503	808 E OAK ST
7777777	202412	808 E OAK ST
7777777	202410	808 E OAK ST

I have a table called Addresses that stores address history for our members like this:

ID	FILE_MONTH	ADDRESS
5555555	202501	201 E RIDGEWAY DR
5555555	202502	201 E RIDGEWAY DR
5555555	202503	201 E RIDGEWAY DR
6666666	202501	906 BRET LANE
6666666	202502	906 BRET LANE
6666666	202503	100 W 4TH ST
7777777	202503	808 E OAK ST
7777777	202412	808 E OAK ST
7777777	202410	808 E OAK ST

I want to create a results set where there is a flag column if the member has been at the same address 3 consecutive months or more, with one row per member like this:

ID	SAME_ADDRESS_3_MONTHS
5555555	Y
6666666	N
7777777	N

Only member 5555555 has been at the same address in the past 3 consecutive months. Member 7777777 has been at the same address but not for the past 3 consecutive months, there is gaps in their enrollment history.

I tried using ROW_NUMBER like:

with cte as (
SELECT MEDICAID_ID, FILE_MONTH,RES_ADDRESS_1, 
RES_CITY, 
ROW_NUMBER() OVER (PARTITION BY ID,ADDRESS ORDER BY ID, ADDRESS) as RN
FROM Addresses
WHERE FILE_MONTH > '202412'
)

SELECT MEDICAID_ID, CASE WHEN RN >= 3 THEN 'Y' ELSE 'N' END AS SAME_ADDRESS_3_MONTHS
FROM cte

But I get rows where SAME_ADDRESS_3_MONTHS is N even when they have been there the past 3 months and I get multiple rows per member. Im not sure how to get this.

• sql
• sql-server

Share Improve this question Follow edited Mar 17 at 21:16 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Mar 17 at 15:58 Ben SmithBen Smith 39211 gold badge44 silver badges1414 bronze badges 3

• Can you have holes in the months of your data? Or duplicates month-wise – siggemannen Commented Mar 17 at 16:02
• You likely want a calendar table here; you appear to be assuming that ROW_NUMBER can infer you want to increment once per month, not once per row. otherwise you'll want to check that the rows are continuous, which is a little harder when you have non-date value for a month. – Thom A Commented Mar 17 at 16:04
• @siggemannen There cant be holes in the months like with 7777777, the months have to be consecutive for the past 3 months. 202503 begin the most recent. There wont be duplicates month-wise – Ben Smith Commented Mar 17 at 16:06

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I think you can solve your issue with a query that uses a CTE and Row_Number(). The final query needs to group by ID and check the Max(RN).

WITH CTE as
(
SELECT ID,  Address, 
  ROW_NUMBER() OVER (PARTITION BY ID,ADDRESS ORDER BY ID, ADDRESS) as RN
FROM Addresses
WHERE   FILE_MONTH > 202412
)
SELECT ID, CASE WHEN Max(RN) >2 THEN 'Y' ELSE 'N' END as SAME_ADDRESS_3_MONTHS
FROM CTE
GROUP BY ID

fiddle

ID	SAME_ADDRESS_3_MONTHS
5555555	Y
6666666	N
7777777	N

本文标签： sqlGet a result set flagging where there is a history going back 3 monthsStack Overflow