How do i get clients who have been enrolled for 12 months continously.

Question

How do i get clients who have been enrolled for 12 months continously.

Karen Roslund

SSCommitted

Points: 1724
More actions
June 15, 2009 at 6:51 am

#155829

I have a requirement where i have to find those clients who have been enrolled continously for 12 months based on the enddate..
So the query i have written is as follows
[Code]
Declare @strdate datetime,
@enddate datetime
set @strdate = '1/1/2009'
set @enddate = '1/31/2009'
Select Client_Id from tbl_Cli_Insu where Plan_Id = 4 and eff_dt > dateadd(month,-12,@enddt)
[/Code]
I am not sure whether the above query would it... find those clients who have been enrolled continously for previous twelve months.
Any help will be appreciated
Thanks,
Karen

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Karen Roslund SSCommitted Points: 1724 More actions · Answer 2

Actually My table structure is at follows

Client_Id eff_dt exp_dt

1 1/1/2006 1/12/2099

2 1/1/2007 1/12/2012

3 1/1/2007 1/1/2008

3 6/1/2008 12/31/2099

so if a client whose enrollment period is ended and he has been re enrolled again he would have a new eff_dt and exp_dt

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

And the expected results from that sample data?

Please can you take a read through the article I referenced?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Karen Roslund SSCommitted Points: 1724 More actions · Answer 4

My expected results should be clients that have been continously enrolled for the previous 12 months

thats the first two records.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ok. What about this, would this one qualify or not?

Insert Into ClientEnrollment (ClientID, eff_dt, exp_dt) Values (4, '2008/01/01', '2009/01/01')

Insert Into ClientEnrollment (ClientID, eff_dt, exp_dt) Values (4, '2009/01/01', '2010/01/01')

or does the mere fact that there are two entries for them over the last 12 months disqualify them?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Karen Roslund SSCommitted Points: 1724 More actions · Answer 6

To be continuously enrolled, there should not be a gap in the dates of enrollment for the 12 month period.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

There's no gap in the one I just posted. That's why I'm asking if that one qualifies or not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

Karen,

Looks like there's something missing in your where clause. I believe you should also be checking that the difference between the effective date and the termination date is at least 12 months.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Karen Roslund SSCommitted Points: 1724 More actions · Answer 9

This is my actual requirement...

The previous 12 months should start counting on the end date of the date range entered (selected) and it is based on the enrolled status. To be continuously enrolled, there should not be a gap in the dates of enrollment for the 12 month period.

Gail based on your example i would consider that guy as continuous cause there is no gap in between the exp_dt and the new eff_Dt..

if there is a few days or a few month i wouldnt consider that guy..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Karen Roslund (6/15/2009)
Gail based on your example i would consider that guy as continuous cause there is no gap in between the exp_dt and the new eff_Dt.

Ok, that makes it a hell of a lot more difficult. Will give it some thought.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Lester Hall of Fame Points: 3647 More actions · Answer 11

Will this work for what you are trying to accomplish? This code essentially condenses down span records to one record for each continuously enrolled period, from there it is just a matter of selecting those with effective and expiration dates fitting your criteria.

[Code]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Continous_Enrollment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Continous_Enrollment]

CREATE TABLE [dbo].[Continous_Enrollment] (

[Client_ID] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Eff_Date] [datetime] NULL ,

[Exp_Date] [datetime] NULL

)

INSERT INTO Continous_Enrollment (Client_ID, Eff_Date, Exp_Date)

Select '42721' as Client_ID, '08/01/2004' as Eff_Date, '09/30/2004' as Exp_Date

Union all

Select '42721' as Client_ID, '10/01/2004' as Eff_Date, '12/31/2004' as Exp_Date

Union all

Select '42721' as Client_ID, '01/01/2005' as Eff_Date, '12/31/2005' as Exp_Date

Union all

Select '42721' as Client_ID, '01/01/2006' as Eff_Date, '12/31/2006' as Exp_Date

Union all

Select '42721' as Client_ID, '01/01/2007' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '42721' as Client_ID, '01/01/2009' as Eff_Date, '06/30/2009' as Exp_Date

Union all

Select '42875' as Client_ID, '08/01/2004' as Eff_Date, '06/30/2006' as Exp_Date

Union all

Select '42875' as Client_ID, '07/01/2006' as Eff_Date, '03/31/2009' as Exp_Date

Union all

Select '42875' as Client_ID, '04/01/2009' as Eff_Date, '06/30/2009' as Exp_Date

Union all

Select '42875' as Client_ID, '07/01/2009' as Eff_Date, '09/30/2010' as Exp_Date

Union all

Select '43039' as Client_ID, '08/01/2004' as Eff_Date, '05/31/2005' as Exp_Date

Union all

Select '43039' as Client_ID, '06/01/2005' as Eff_Date, '12/31/2006' as Exp_Date

Union all

Select '43039' as Client_ID, '01/01/2007' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '43039' as Client_ID, '01/01/2009' as Eff_Date, '05/31/2009' as Exp_Date

Union all

Select '43039' as Client_ID, '06/01/2009' as Eff_Date, '11/30/2010' as Exp_Date

Union all

Select '43583' as Client_ID, '08/01/2004' as Eff_Date, '04/30/2005' as Exp_Date

Union all

Select '43583' as Client_ID, '05/01/2005' as Eff_Date, '05/31/2005' as Exp_Date

Union all

Select '43583' as Client_ID, '06/01/2005' as Eff_Date, '09/30/2005' as Exp_Date

Union all

Select '43583' as Client_ID, '10/01/2005' as Eff_Date, '06/30/2008' as Exp_Date

Union all

Select '43583' as Client_ID, '07/01/2008' as Eff_Date, '06/30/2009' as Exp_Date

Union all

Select '44434' as Client_ID, '08/01/2004' as Eff_Date, '12/31/2004' as Exp_Date

Union all

Select '44434' as Client_ID, '01/01/2005' as Eff_Date, '12/31/2006' as Exp_Date

Union all

Select '44434' as Client_ID, '01/01/2007' as Eff_Date, '12/31/2007' as Exp_Date

Union all

Select '44434' as Client_ID, '01/01/2008' as Eff_Date, '06/30/2008' as Exp_Date

Union all

Select '44434' as Client_ID, '07/01/2008' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '44434' as Client_ID, '01/01/2009' as Eff_Date, '12/31/2009' as Exp_Date

Union all

Select '44677' as Client_ID, '07/01/2006' as Eff_Date, '02/28/2009' as Exp_Date

Union all

Select '44677' as Client_ID, '03/01/2009' as Eff_Date, '04/30/2009' as Exp_Date

Union all

Select '44677' as Client_ID, '05/01/2009' as Eff_Date, '08/31/2010' as Exp_Date

Union all

Select '44730' as Client_ID, '08/01/2004' as Eff_Date, '12/31/2007' as Exp_Date

Union all

Select '44730' as Client_ID, '01/01/2008' as Eff_Date, '04/30/2008' as Exp_Date

Union all

Select '44730' as Client_ID, '05/01/2008' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '44730' as Client_ID, '01/01/2009' as Eff_Date, '10/31/2009' as Exp_Date

Union all

Select '44757' as Client_ID, '08/01/2004' as Eff_Date, '12/31/2007' as Exp_Date

Union all

Select '44757' as Client_ID, '01/01/2008' as Eff_Date, '04/30/2008' as Exp_Date

Union all

Select '44757' as Client_ID, '05/01/2008' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '44757' as Client_ID, '01/01/2009' as Eff_Date, '10/31/2009' as Exp_Date

Union all

Select '45322' as Client_ID, '08/01/2004' as Eff_Date, '05/31/2005' as Exp_Date

Union all

Select '45322' as Client_ID, '06/01/2005' as Eff_Date, '06/30/2006' as Exp_Date

Union all

Select '45322' as Client_ID, '07/01/2006' as Eff_Date, '09/30/2007' as Exp_Date

Union all

Select '45322' as Client_ID, '10/01/2007' as Eff_Date, '12/31/2007' as Exp_Date

Union all

Select '45322' as Client_ID, '01/01/2008' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '45322' as Client_ID, '01/01/2009' as Eff_Date, '03/31/2009' as Exp_Date

Union all

Select '45322' as Client_ID, '04/01/2009' as Eff_Date, '02/28/2010' as Exp_Date

Union all

Select '45337' as Client_ID, '08/01/2004' as Eff_Date, '12/31/2004' as Exp_Date

Union all

Select '45337' as Client_ID, '01/01/2005' as Eff_Date, '09/23/2005' as Exp_Date

Union all

Select '45337' as Client_ID, '09/24/2005' as Eff_Date, '12/31/2005' as Exp_Date

Union all

Select '45337' as Client_ID, '01/01/2006' as Eff_Date, '01/31/2006' as Exp_Date

Union all

Select '45337' as Client_ID, '03/01/2006' as Eff_Date, '12/31/2006' as Exp_Date

Union all

Select '45337' as Client_ID, '01/01/2007' as Eff_Date, '03/31/2008' as Exp_Date

Union all

Select '45337' as Client_ID, '04/01/2008' as Eff_Date, '04/30/2008' as Exp_Date

Union all

Select '45337' as Client_ID, '05/01/2008' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '45337' as Client_ID, '01/01/2009' as Eff_Date, '04/30/2009' as Exp_Date

Union all

Select '45337' as Client_ID, '05/01/2009' as Eff_Date, '10/31/2010' as Exp_Date

Union all

Select '45534' as Client_ID, '09/01/2004' as Eff_Date, '12/31/2005' as Exp_Date

Union all

Select '45534' as Client_ID, '01/01/2006' as Eff_Date, '01/31/2007' as Exp_Date

Union all

Select '45534' as Client_ID, '02/01/2007' as Eff_Date, '08/31/2007' as Exp_Date

Union all

Select '45534' as Client_ID, '09/01/2007' as Eff_Date, '04/30/2008' as Exp_Date

Union all

Select '45534' as Client_ID, '05/01/2008' as Eff_Date, '08/31/2010' as Exp_Date

Union all

Select '46489' as Client_ID, '09/01/2004' as Eff_Date, '12/31/2004' as Exp_Date

Union all

Select '46489' as Client_ID, '01/01/2005' as Eff_Date, '12/31/2005' as Exp_Date

Union all

Select '46489' as Client_ID, '01/01/2006' as Eff_Date, '12/31/2006' as Exp_Date

Union all

Select '46489' as Client_ID, '01/01/2007' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '46489' as Client_ID, '01/01/2009' as Eff_Date, '04/30/2009' as Exp_Date

Union all

Select '46489' as Client_ID, '05/01/2009' as Eff_Date, '10/31/2010' as Exp_Date

Union all

Select '47478' as Client_ID, '09/01/2004' as Eff_Date, '12/31/2005' as Exp_Date

Union all

Select '47478' as Client_ID, '01/01/2006' as Eff_Date, '08/31/2006' as Exp_Date

Union all

Select '47478' as Client_ID, '12/01/2006' as Eff_Date, '11/30/2008' as Exp_Date

Union all

Select '47478' as Client_ID, '12/01/2008' as Eff_Date, '02/28/2009' as Exp_Date

Union all

Select '47478' as Client_ID, '03/01/2009' as Eff_Date, '08/31/2010' as Exp_Date

Union all

Select '48079' as Client_ID, '10/01/2004' as Eff_Date, '12/31/2004' as Exp_Date

Union all

Select '48079' as Client_ID, '01/01/2005' as Eff_Date, '12/31/2005' as Exp_Date

Union all

Select '48079' as Client_ID, '01/01/2006' as Eff_Date, '12/31/2008' as Exp_Date

Union all

Select '48079' as Client_ID, '01/01/2009' as Eff_Date, '02/28/2009' as Exp_Date

Union all

Select '48079' as Client_ID, '03/01/2009' as Eff_Date, '11/30/2009' as Exp_Date

SELECT StartDates.Client_ID, MIN(StartDates.Eff_Date) AS Start_Continuous_Enroll_Date, MAX(EndDates.Exp_Date) AS End_Continuous_Enroll_Date

FROM Continous_Enrollment StartDates

INNER JOIN Continous_Enrollment EndDates

ON StartDates.Client_ID = EndDates.Client_ID AND DATEADD(d, 1, StartDates.Exp_Date) = EndDates.Eff_Date

GROUP BY StartDates.Client_ID

HAVING (MIN(StartDates.Eff_Date) = GETDATE())

[/Code]

doug.klear Newbie Points: 2 More actions · Answer 12

The trick is to create a matrix of all possible values then see which clients do not cover all possibilities.

create table tbl_Cli_Insu (Client_Id int, eff_dt datetime, exp_dt datetime)

insert into tbl_Cli_Insu (Client_Id, eff_dt, exp_dt)

select 1, '1/1/2006', '1/12/2099' union all

select 2, '1/1/2007', '1/12/2012' union all

select 3, '1/1/2007', '1/1/2008' union all

select 3, '6/1/2008', '12/31/2099'

Declare @strdate datetime,

@enddate datetime

declare @dts table (dtdatetime)

set @strdate = '1/1/2008'

set @enddate = '12/31/2008'

while @strdate <= @enddate

begin

insert into @dts (dt) values (@strdate)

select @strdate = dateadd(month, 1, @strdate)

end

Select c.Client_Id

from tbl_Cli_Insu c

join @dts d on d.dt between c.eff_dt and c.exp_dt

--where c.Plan_Id = 4

group by c.client_id

having count(*) < 12

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 13

Can enrollment dates overlap, such as this?

Insert Into tbl_Cli_Insu(ClientID, eff_dt, exp_dt) Values (9, '20080101', '20080301')

Insert Into tbl_Cli_Insu(ClientID, eff_dt, exp_dt) Values (9, '20080201', '20080501')

Insert Into tbl_Cli_Insu(ClientID, eff_dt, exp_dt) Values (9, '20080401', '20080531')

Insert Into tbl_Cli_Insu(ClientID, eff_dt, exp_dt) Values (9, '20080601', '20090131')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 14

don't make it too hard, dump all your records into a table ordered by client and then effdate, including an identity column and a flag for continuouslyEnrolled

self-join, where the tableA.ID = (tableB.ID + 1) AND tableA.client = tableB.client

if datediff(dd,tableA.termdate,tableB.effdate)>0, then they're not continuously enrolled. flag them as such.

Then run an additional update where there is nothing following the last enrollment span.

SELECT from your table where continuouslyEnrolled is true

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."