Member eligibility continuity

AK Sam 0 Reputation points
2023-03-17T17:10:03.44+00:00 
CREATE TABLE [dbo].[EligInput](
	[ID] [float] NULL,
	[EffectiveDate] [datetime] NULL,
	[StratDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[Status] [nvarchar](255) 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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,767 questions