continuous membership
-
Hi,
I am trying to find everyone who had membership for a continuous year without any gaps. The sample below shows two members, one who is correct,760890291, and should be returned but the other one, 82928404, should not but it does. My code is below. What would I need to change? Thanks.
CREATE TABLE TEST
(
ID_NUM INT
,EFF_DATE DATE NULL
,EXP_DATE DATE NULL
)
INSERT INTO ADMITS (ID_NUM, EFF_DATE, EXP_DATE)
VALUES
(82928404, '12/7/2023', '5/31/2024')
,(82928404, '6/1/2024', '11/30/2024')
,(82928404, '12/23/2024', NULL)
,(760890291, '1/2/2014', '10/31/2024')
,(760890291, '11/1/2024', NULL)
;
with test as
(
SELECT DISTINCT MB.ID_NUM
,EFF_DATE
,EXP_DATE
,ROW_NUMBER() OVER(PARTITION BY ID_NUM ORDER BY EFF_DATE) AS EFF_ORDER
FROM BI4.DBO.DW_MEMBER MB
LEFT JOIN BI4.DBO.SBHM_COUNTY COUNTY ON MB.COUNTY_ID = COUNTY.COUNTY_ID
WHERE ((MB.EXP_DATE BETWEEN '2024-01-01' and '2024-12-31') OR (MB.EXP_DATE IS NULL))
AND UPPER(MB.ID_NUM) NOT LIKE 'TEMP%'
AND UPPER(MB.ID_NUM) NOT LIKE '%UNKN%'
AND UPPER(MB.ID_NUM) NOT LIKE 'PA%'
AND MB.COUNTY_ID IN ('20','43','61')
and id_num IN ('082928404','760890291')
)
,re as
(
SELECT DISTINCT ID_NUM
,count(id_num) as t
from test
group by ID_NUM
having count(id_num) > 1
)
--SELECT * FROM RE
,f1 as
(
select test.id_num
,test.eff_date
,test.exp_date
,LEAD(EFF_DATE) OVER(PARTITION BY test.ID_NUM ORDER BY EFF_DATE) AS NEXT_EFF
,EFF_ORDER
from test
inner join re on test.id_num = re.id_num
)
,f2 as
(
select id_num
,eff_date
,exp_date
,NEXT_EFF
,datediff(day,exp_date,next_eff) as df
,EFF_ORDER
from f1
)
--SELECT * FROM F2
,F3 AS
(
select ID_NUM
,EFF_DATE
,EXP_DATE
,EFF_ORDER
,CASE WHEN EFF_ORDER = 1 AND EFF_DATE <= '2024-01-01' THEN 1 END AS STARTING
,CASE WHEN (EXP_DATE >= '2024-12-31' OR EXP_DATE IS NULL) THEN 2 END AS ENDING
from f2
where (df = 1 or df is null)
)
--SELECT * FROM F3
,F4 AS
(
SELECT ID_NUM
,MIN(STARTING) AS FIRST_DATE
,MAX(ENDING) AS LAST_DATE
FROM F3
GROUP BY ID_NUM
)
--SELECT * FROM F4
SELECT F3.ID_NUM
,EFF_DATE
,EXP_DATE
FROM F3
INNER JOIN F4 ON F3.ID_NUM = F4.ID_NUM
AND F4.FIRST_DATE = 1
AND F4.LAST_DATE = 2- This topic was modified 5 days, 11 hours ago by smattiko83.
- This topic was modified 5 days, 11 hours ago by smattiko83.
-
Please provide data in usable format: CREATE TABLE and INSERT statment(s). That is way more useful to us than a "picture" of data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
In case you feel like doing some reading.... You're trying to find the size of each island. here's an article on it: https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/introduction-to-gaps-and-islands-analysis/
-
"a continuous year without any gaps" might mean different things. This assumes it to mean (any) 365 days in a row
If the EXP_DATE is null does that mean the account is currently active? This assumes it does
with
lag_cte as (
select *, lag(EXP_DATE) over (partition by ID_NUM order by EFF_DATE) lag_exp
from #test),
grp_cte as (
select *, sum(iif(lag_exp is null or datediff(day, lag_exp, EFF_DATE) <> 1,
1,
0)) over (partition by ID_NUM order by EFF_DATE) grp
from lag_cte)
select ID_NUM, grp,
min(EFF_DATE) grp_start_date,
max(isnull(EXP_DATE, getdate())) grp_end_date, --assume currently active
datediff(day, min(EFF_DATE), max(isnull(EXP_DATE, getdate()))) grp_days_diff
from grp_cte
group by ID_NUM, grp
order by ID_NUM, grp_start_date;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply