September 13, 2022 at 4:53 pm
Find Members who have lost eligibility for more than 3 years or more and now returned in 2122.
--I need to find the members who have lost eligibility for more than 3 or more years and now have returned in 2122.
--Programyearvalue is having first 2 digits of last year and last 2 digits for next year.
-- for example 1011 = left 2 digits = 10 that is year 2010 and last 2 digits = 11 that is year 2011
-- for example 2021 = left 2 digits = 20 that is year 2020 and last 2 digits = 21 that is year 2021
-- for example 2122 = left 2 digits = 21 that is year 2021 and last 2 digits = 22 that is year 2022
DROP TABLE IF EXISTS #ProgramYear;
CREATE TABLE #ProgramYear
(
ID INT
,MemberID INT
,ProgramYearID INT
,programYearValue INT
);
INSERT #ProgramYear
(
ID
,MemberID
,ProgramYearID
,programYearValue
)
VALUES
(1,111,1, 1011)
,(2,111,2, 1112)
,(3,111,3, 1213)
,(4,111,4, 1314)
,(5,111,9, 1819)
,(6,111,10, 1920)
,(7,111,11, 2021)
,(8,111,12, 2122)
,(1,222,1, 1011)
,(5,222,6, 1516)
,(5,222,7, 1617)
,(5,222,8, 1718)
,(5,222,9, 1819)
,(6,222,10, 1920)
,(7,222,11, 2021)
,(8,222,12, 2122)
,(1,333,8, 1819)
,(2,333,9, 1920)
,(3,333,10, 2021)
,(4,333,12, 2122)
,(1,444,1, 1011)
,(2,444,2, 1112)
,(3,444,3, 1213)
,(4,444,4, 1314)
,(5,444,5, 1415)
,(5,444,6, 1516)
,(5,444,7, 1617)
,(5,444,8, 1718)
,(5,444,9, 1819)
,(6,444,10, 1920)
,(7,444,11, 2021)
select * from #programyear
My out put should be 111 and 222 Members as these members lost eligibity for 3 years and now they have program year value record.
September 13, 2022 at 9:27 pm
To make the comparison between rows you could use the LAG function. Then use integer division programYearValue/100 to extract the start year and take the modulo 100 of the lagged value to derive the prior end year
with lag_cte as (
select *, lag(programYearValue) over (partition by MemberID order by ProgramYearID) lag_pyv
from #ProgramYear
where programYearValue/2000<=1)
select *, programYearValue/100 start_yr, lag_pyv%100 end_yr
from lag_cte
where programYearValue/100-lag_pyv%100>=3;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 13, 2022 at 10:37 pm
Assuming that all ProgramYearValues correspond to one year spans, the gap between sequential year spans is 101, so a 3 year gap is a difference of 303. You can identify members with a current span and look for gaps >= 303. It doesn't work if your dates span a century, but I think it does from 0001-0102 to 9798-9899. Edit: I think 101 is the same as modulo 100 = 1, so the logic is pretty much the same as above.
DECLARE @CurrentYearValue INT = 2122,
@NumberOfYears INT = 3;
WITH Eligibility AS
(
SELECT *, ProgramYearValue - ISNULL(LAG(ProgramYearValue) OVER (PARTITION BY MemberID ORDER BY ProgramyearID),ProgramYearValue) AS Gap
FROM #ProgramYear
)
SELECT MemberID, ProgramyearValue
FROM #ProgramYear AS a
WHERE ProgramyearValue = @CurrentYearValue
AND EXISTS ( SELECT 1
FROM Eligibility
WHERE MemberID = a.MemberID
AND Gap >= @NumberOfYears * 101);
September 13, 2022 at 11:12 pm
If you just want the MemberID, then as below. Of course you can adjust the query against the cte to pull more columns if you want them. Either way, I don't think you need to futz with the program year value at all.
;WITH CteYearDifferences AS (
SELECT *,
ProgramYearID - LAG(ProgramYearID) OVER(PARTITION BY MemberID ORDER BY ProgramYearID) AS YearDifference
FROM #ProgramYear
)
SELECT DISTINCT MemberID
FROM CteYearDifferences
WHERE YearDifference >= 3
ORDER BY MemberID
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".
September 14, 2022 at 12:02 am
--I need to find the members who have lost eligibility for more than 3 or more years and now have returned in 2122.
Add this data so you have a member with a 3 year gap who is not eligible in 2122.
INSERT #ProgramYear
VALUES (1,999,5, 1415), (2,999,10, 1920) ,(3,999,11, 2021)
Are the ProgramYearIDs supposed to be sequential? They appear to be with the exception of member 333 who has different ids for the same year and is missing 11. Is this a typo? Meaning that the ProgramYearValues are not needed as Scott suggested.
DECLARE @CurrentYearValue INT = 2122,
@NumberOfYears INT = 3;
WITH Eligibility AS
(
SELECT *, ProgramYearID - LAG(ProgramYearID) OVER (PARTITION BY MemberID ORDER BY ProgramyearID) AS Gap,
MAX(ProgramYearValue) OVER (PARTITION BY MemberID) AS MaxYear
FROM #ProgramYear
)
SELECT DISTINCT MemberID
FROM Eligibility AS a
WHERE MaxYear = @CurrentYearValue
AND Gap >= @NumberOfYears;
September 14, 2022 at 2:22 pm
Great point; sorry, I accidentally left out that "current year" requirement.
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".
September 15, 2022 at 9:08 pm
Thank you I will take a look and keep posted here if it solves my issue. Thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply