By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE Addresses (
ID VARCHAR(512),
FILE_MONTH int,
ADDRESS VARCHAR(512)
);
INSERT INTO Addresses (ID, FILE_MONTH, ADDRESS) VALUES
('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');
SELECT * FROM Addresses
| 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 |
WITH CTE as
(
SELECT ID, Address,
ROW_NUMBER() OVER (PARTITION BY ID,ADDRESS ORDER BY ID, ADDRESS) as RN
FROM Addresses
)
SELECT ID, CASE WHEN Max(RN) >2 THEN 'Y' ELSE 'N' END as SAME_ADDRESS_3_MONTHS
FROM CTE
GROUP BY ID
| ID | SAME_ADDRESS_3_MONTHS |
|---|---|
| 5555555 | Y |
| 6666666 | N |
| 7777777 | Y |