Determine Continous Member Eligibility

sam22 · March 17, 2023, 5:14pm

CREATE TABLE [dbo].[EligInput](
[ID] [float] NULL,
[EffectiveDate] [datetime] NULL,
[StratDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Status] nvarchar NULL
) ON [PRIMARY]
GO
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (131333186, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-09-30T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (131333186, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-10-01T00:00:00.000' AS DateTime), CAST(N'2022-12-31T00:00:00.000' AS DateTime), N'D')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (131333186, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'D')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2012-01-01T00:00:00.000' AS DateTime), CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2022-03-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2012-01-01T00:00:00.000' AS DateTime), CAST(N'2022-04-01T00:00:00.000' AS DateTime), CAST(N'2022-06-30T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2012-01-01T00:00:00.000' AS DateTime), CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-09-30T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2012-01-01T00:00:00.000' AS DateTime), CAST(N'2022-10-01T00:00:00.000' AS DateTime), CAST(N'2022-12-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2012-01-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (185935730, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-09-30T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (185935730, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-10-01T00:00:00.000' AS DateTime), CAST(N'2022-12-31T00:00:00.000' AS DateTime), N'D')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (185935730, CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (187950850, CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (230389221, CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'A')
GO

Checking continuous Eligibility, output should be like this. If a ID has continous Eligibility, it should be part of o/p if not it should be ignored. If an ID is inactive and reenrolled it should be picked with latest Eligibility date
--output
ID EffectiveDate
157499166 2012-01-01
185935730 2023-01-01
185935730 2023-01-01
187950850 2023-01-01
230389221 2011-12-01

harishgg1 · March 17, 2023, 6:37pm

hi

hope this helps

your output is very confusing

i am trying to make sense of what you are describing with Table Data and Output
does not match

could please explain clearly ? and put the correct output

thank you

sam22 · March 17, 2023, 8:37pm

Sorry I correct the script.

drop table [EligInput]

CREATE TABLE [dbo].[EligInput](
[ID] [float] NULL,
[EffectiveDate] [datetime] NULL,
[StratDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Status] nvarchar NULL
) ON [PRIMARY]
GO
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (131333186, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-09-30T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (131333186, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-10-01T00:00:00.000' AS DateTime), CAST(N'2022-12-31T00:00:00.000' AS DateTime), N'D')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (131333186, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'D')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2022-03-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2022-04-01T00:00:00.000' AS DateTime), CAST(N'2022-06-30T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-09-30T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2022-10-01T00:00:00.000' AS DateTime), CAST(N'2022-12-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (157499166, CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (185935730, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-09-30T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (185935730, CAST(N'2022-07-01T00:00:00.000' AS DateTime), CAST(N'2022-10-01T00:00:00.000' AS DateTime), CAST(N'2022-12-31T00:00:00.000' AS DateTime), N'D')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (185935738, CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (187950850, CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'A')
INSERT [dbo].[EligInput] ([ID], [EffectiveDate], [StratDate], [EndDate], [Status]) VALUES (230389221, CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-01-01T00:00:00.000' AS DateTime), CAST(N'2023-03-31T00:00:00.000' AS DateTime), N'A')
GO

Checking continuous Eligibility, output should be like this. If a ID has continous Eligibility, it should be part of o/p if not it should be ignored.
--output
ID EffectiveDate
157499166 1/1/2022
185935738 1/1/2023
187950850 1/1/2023
230389221 1/1/2023

harishgg1 · March 19, 2023, 9:21am

hi

hope this helps

1st way to do this

SELECT 
     id 
   , EffectiveDate
   , sum(case when status <> 'A' then 1 else 0 end)
FROM 
   dbo.EligInput
GROUP BY 
     id 
   , EffectiveDate
HAVING 
    sum(case when status <> 'A' then 1 else 0 end) = 0

2nd way to do this

SELECT 
     id 
   , EffectiveDate
   , max(status)
FROM 
   dbo.EligInput
GROUP BY 
     id 
   , EffectiveDate
HAVING  
    max(status) = 'A'

sam22 · March 20, 2023, 6:30pm

thank you. it worked.

sam22 · March 20, 2023, 8:09pm

on the same notes, my request has changed slightly different. check continuous Active enrollment for current calendar year.

If a ID is active in current quarter and inactive in prior quarter for that Calendar year or missing a quarter, it should be marked as Inactive
If a ID is active in current quarter and Active in prior quarter for that Calendar year, it should be marked as Active

CREATE TABLE [dbo].[EligInput](
[ID] [float] NULL,
[ED] [datetime] NULL,
[Duration] nvarchar NULL,
[Status] nvarchar NULL
) ON [PRIMARY]
GO
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (200, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'01/01/2022-03/31/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (200, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'04/01/2022-06/30/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (200, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'07/01/2022-09/30/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (300, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'01/01/2022-03/31/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (300, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'04/01/2022-06/30/2022', N'D')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (300, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'07/01/2022-09/30/2022', N'D')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (400, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'01/01/2022-03/31/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (400, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'07/01/2022-09/30/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (500, CAST(N'2022-07-01T00:00:00.000' AS DateTime), N'07/01/2022-09/30/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (600, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'01/01/2022-03/31/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (600, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'04/01/2022-06/30/2022', N'D')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (600, CAST(N'2022-07-01T00:00:00.000' AS DateTime), N'07/01/2022-09/30/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (600, CAST(N'2022-07-01T00:00:00.000' AS DateTime), N'10/01/2022-12/31/2022', N'D')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (600, CAST(N'2023-01-01T00:00:00.000' AS DateTime), N'01/01/2023-03/31/2023', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (700, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'01/01/2022-03/31/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (700, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'04/01/2022-06/30/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (700, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'07/01/2022-09/30/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (700, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'10/01/2022-12/31/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (700, CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'01/01/2023-03/31/2023', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (800, CAST(N'2022-07-01T00:00:00.000' AS DateTime), N'07/01/2022-09/30/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (800, CAST(N'2022-07-01T00:00:00.000' AS DateTime), N'10/01/2022-12/31/2022', N'A')
INSERT [dbo].[EligInput] ([ID], [ED], [Duration], [Status]) VALUES (800, CAST(N'2022-07-01T00:00:00.000' AS DateTime), N'01/01/2023-03/31/2023', N'A')

harishgg1 · March 21, 2023, 5:28am

hi

hope this helps

SQL depends on how you want to see your data ?

; WITH cteIden   as ( SELECT id , max(status) as maxStatus FROM  dbo.EligInput GROUP BY  id  HAVING  max(status) = 'A')
     , cteSplit  as ( SELECT *,cast(LEFT(Duration, 10) as date) as StartDT, cast(RIGHT( Duration, 10) as date ) AS EndDT FROM [dbo].[EligInput]  WHERE ID in ( SELECT ID FROM cteiden ) )
     , cteStatus as ( SELECT * , case when dateadd(dd,1,endDT) <> LEAD(StartDT) over(partition by ID order by ID,StartDt) then 'InActive' else 'Active' end as StatusOK FROM cteSplit )
SELECT 
    ID 
  , Max(StatusOK)  as Status 
FROM 
    cteStatus 
GROUP BY 
       ID

sam22 · March 21, 2023, 6:23am

200, 400, 500 - should be inactive. (they are missing other Qs in 2022)
600 - should be active, because we are in Q1 of 2023

thank you.

harishgg1 · March 21, 2023, 7:09am

hi

there is this case scenario

ID = 700 , 800 have both 2023 and 2022

what to do if 2023 is ok and 2022 having missing quarters
what to do if 2023 is ok and 2022 having ALL quarters

thank you

sam22 · March 21, 2023, 7:38am

Hi, this is the complete scenarios i need to check.

what to do if 2023 is ok and 2022 having missing quarters: you are only checking for current calendar year quarters.
what to do if 2023 is ok and 2022 having ALL quarters :

If date='12/31/2022' check if all Q are active for 2022, if anyone of it is Inactive or missing a Q, then flag it as Inactive.
If date='3/21/2023' check if all Q are active for 2023, if anyone of it is Inactive or missing a Q, then flag it as Inactive.(technically we will only have 1 Q for 2023 as of today.)

current date is our parameter, we are checking active records for given calendar year quarters.

If a record is active in Q1 and inactive in rest of the Qs for that calendar year or active bt Q1-3 and inactive in Q4 or missing any quarter, it should be flagged as Inactive.

In other words, All the quarter in a calendar year should be active to be flagged as Active, if any of the Q is inactive, it should flag as Inactive.

harishgg1 · March 21, 2023, 8:03am

hi

please check and verify

data scenarios can be plenty

what if you have data for 2021 , 2022 , 2023

what if data is like this 2001, 2008 , 2010 , 2023 for ID = 200

; WITH cte AS (SELECT ID,Status,datepart(year,cast(left(Duration,10) as date)) as yr,sum(datepart(quarter,cast(left(Duration,10) as date))) as sm FROM EligInput GROUP BY ID,Status,datepart(year,cast(left(Duration,10) as date)))
 SELECT 
      ID
	, max(status)
    , case when sum(sm) >= 11 then 'Active' else 'InActive' end 
 FROM 
    cte 
 GROUP BY 
    id 
 HAVING 
    max(status) = 'A' 
 ORDER BY 
     id

sam22 · March 21, 2023, 9:02am

Did not work. missing 800.

you are only supposed to check current year data, quarters.

same logic applies for any year backwards. Q1-Q4 active = active else inactive

let us connect if you are okay.

harishgg1 · March 22, 2023, 12:37pm

thanks SAM

We Connected ( Remote DeskTop )

I was able to look at your code and give you suggestion to use WHERE EXISTS instead of JOIN

It resolved your issue ( whole process took 30 seconds )

The reason I am putting this post is so that People in this Forum know

Thank You