June 21, 2012 at 12:38 pm
Hello,
I've come across a challenging aspect of trying to pull eligibility data for members. My data looks something like this:
set identity_insert #mytable on
insert into #mytable (ID, EffectiveDate, TermDate, Status, TranslatesTo)
select '123', '01/01/2012', '01/31/2012', 'A', 'Active' Union all
select '123', '02/01/2012', '02/29/2012', 'A', 'Active' Union all
select '123', '03/01/2012', '03/31/2012', 'D', 'Inactive' Union all
select '123', '04/01/2012', Null, 'A', 'Active' Union all
select '555', '01/01/2012', Null, 'D', 'Inactive' Union all
select '999', '02/01/2012', '04/30/2012', 'A', 'Active' Union all
select '999', '05/01/2012', Null, 'A', 'Active'
What I need to determine is if each ID is active continuosly between a start date and an end date (as in their Status is A without any break between their eligibility period, eg. ID 999 is the only ID that would satisfy all conditions). I can easily tell if they are active at some point between let's say 01/01/2012 and today, but to determine that they didn't have any months between their active statuses and didn't have a status of D is what is challenging me.
If their eligibility were determined for each line on a month by month basis, it would be much simpler, but because the date difference between their Effective and Term dates can span more than one month (or be Null and still considered active), I've run into this issue.
Any ideas on how to approach this?
Thank you!
June 21, 2012 at 12:44 pm
What are you expecting for results? Also, could put that into a consumable format as described in the first link in my signature? It'll help us give you tested code instead of flailing a bit at it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2012 at 12:51 pm
Not sure what your problem/question is here, care to elaborate?
June 21, 2012 at 3:32 pm
Sorry, the post was posted before I was done with it, and it then posted twice. I've made edits to the post that actually describe my challenge. Thanks!
June 21, 2012 at 3:36 pm
Wrong post, sorry.
June 21, 2012 at 3:38 pm
Based on the data provided, what is your expected output?
June 21, 2012 at 4:08 pm
I just want the ID as long as it is continuously eligible from let's say January through June.
June 21, 2012 at 4:13 pm
themangoagent (6/21/2012)
I just want the ID as long as it is continuously eligible from let's say January through June.
So
select * from #mytable
should return 999.
Again, what are we trying to accomplish here?
June 21, 2012 at 4:16 pm
And before I go any further, please verify what version of SQL Server you are running. I just noticed that this is in a SQL Server 7/2000 forum, and I'd hate to provide you with a SQL Server 2005/2008/2008R/2012 solution if this is correct.
June 21, 2012 at 5:07 pm
Lynn Pettis (6/21/2012)
And before I go any further, please verify what version of SQL Server you are running. I just noticed that this is in a SQL Server 7/2000 forum, and I'd hate to provide you with a SQL Server 2005/2008/2008R/2012 solution if this is correct.
Hi Lynn. I am actually running SQL 2000, but am in the progress of migrating to 2008.
I did post what it is that I am trying to accomplish:
What I need to determine is if each ID is active continuosly between a start date and an end date (as in their Status is A without any break between their eligibility period, eg. ID 999 is the only ID that would satisfy all conditions).
So they would need to EffectiveDate and TermDate with a status of A that spans each month between any two given dates without any gaps in months.
So they would need to be Active for January, for February, March, April, May and June to be considered continuous. They couldn't be allowed to have a gap anywhere between January and June. The only scenario that I've provided that meets this is 999. So I need something that dynamically can determine if they are continous between any two given months.
Does this make sense?
June 22, 2012 at 10:30 am
Here is a larger code set with some actual data. I need to select only those ID's who have an effective date on or before 12/01/2011, and I need to see that they have an status of A for December of 2011, January, February, March, April, May and June of 2012.
The lines are not created individually for each month (Ie 01/01/2012 through 01/31/2012), and the Null value for TermDate indicates that the ID is still Active.
create table #mytable
(
ID varchar(12),
EffectiveDate datetime,
TermDate datetime,
Status varchar(1)
)
insert into #mytable values ('503484823','01/01/2012',Null,'A')
insert into #mytable values ('592879472','01/01/2012',Null,'A')
insert into #mytable values ('929921311','01/01/2012',Null,'A')
insert into #mytable values ('239826234','01/01/2012',Null,'A')
insert into #mytable values ('616546060','12/01/2011',Null,'A')
insert into #mytable values ('978047548','12/01/2011',Null,'A')
insert into #mytable values ('524739641','12/01/2011',Null,'A')
insert into #mytable values ('428547606','12/01/2011','03/31/2012','A')
insert into #mytable values ('951720863','12/01/2011',Null,'A')
insert into #mytable values ('131333186','12/01/2011',Null,'A')
insert into #mytable values ('260895908','12/01/2011',Null,'A')
insert into #mytable values ('553625622','12/01/2011',Null,'A')
insert into #mytable values ('919168813','12/01/2011','01/31/2012','A')
insert into #mytable values ('557902697','12/01/2011',Null,'A')
insert into #mytable values ('379088668','12/01/2011','02/29/2012','A')
insert into #mytable values ('602081252','12/01/2011',Null,'A')
insert into #mytable values ('716393277','12/01/2011','01/31/2012','A')
insert into #mytable values ('300006158','12/01/2011',Null,'A')
insert into #mytable values ('157499166','01/01/2012','03/31/2012','A')
insert into #mytable values ('249228354','01/01/2012',Null,'A')
insert into #mytable values ('446726968','01/01/2012','04/30/2012','A')
insert into #mytable values ('975883457','01/01/2012','03/31/2012','A')
insert into #mytable values ('847326744','01/01/2012',Null,'A')
insert into #mytable values ('434075176','01/01/2012','03/31/2012','A')
insert into #mytable values ('296098224','01/01/2012','04/30/2012','A')
insert into #mytable values ('370215156','12/01/2011',Null,'A')
insert into #mytable values ('647742333','12/01/2011',Null,'A')
insert into #mytable values ('528683544','12/01/2011','01/31/2012','A')
insert into #mytable values ('717022168','12/01/2011',Null,'A')
insert into #mytable values ('417045232','12/01/2011','02/29/2012','A')
insert into #mytable values ('485375006','12/01/2011',Null,'A')
insert into #mytable values ('963527175','12/01/2011','04/30/2012','A')
insert into #mytable values ('328693654','12/01/2011',Null,'A')
insert into #mytable values ('631187903','12/01/2011','02/29/2012','A')
insert into #mytable values ('903184004','12/01/2011',Null,'A')
insert into #mytable values ('833363486','12/01/2011',Null,'A')
insert into #mytable values ('185935730','12/01/2011',Null,'A')
insert into #mytable values ('479006496','12/01/2011','01/31/2012','A')
insert into #mytable values ('302562202','12/01/2011',Null,'A')
insert into #mytable values ('230389221','12/01/2011',Null,'A')
insert into #mytable values ('187950850','12/01/2011','02/29/2012','A')
insert into #mytable values ('543426566','12/01/2011',Null,'A')
insert into #mytable values ('387473716','01/01/2012','04/30/2012','A')
insert into #mytable values ('365407230','01/01/2012','04/30/2012','A')
insert into #mytable values ('667394514','01/01/2012',Null,'A')
insert into #mytable values ('572158672','01/01/2012',Null,'A')
insert into #mytable values ('952548642','01/01/2012',Null,'A')
insert into #mytable values ('803871629','01/01/2012',Null,'A')
insert into #mytable values ('594658511','01/01/2012',Null,'A')
insert into #mytable values ('430877573','01/01/2012',Null,'A')
June 22, 2012 at 10:39 am
themangoagent (6/22/2012)
Here is a larger code set with some actual data. I need to select only those ID's who have an effective date on or before 12/01/2011, and I need to see that they have an status of A for December of 2011, January, February, March, April, May and June of 2012.The lines are not created individually for each month (Ie 01/01/2012 through 01/31/2012), and the Null value for TermDate indicates that the ID is still Active.
create table #mytable(
ID varchar(12),
EffectiveDate datetime,
TermDate datetime,
Status varchar(1)
)
insert into #mytable values ('503484823','01/01/2012',Null,'A')
insert into #mytable values ('592879472','01/01/2012',Null,'A')
insert into #mytable values ('929921311','01/01/2012',Null,'A')
insert into #mytable values ('239826234','01/01/2012',Null,'A')
insert into #mytable values ('616546060','12/01/2011',Null,'A')
insert into #mytable values ('978047548','12/01/2011',Null,'A')
insert into #mytable values ('524739641','12/01/2011',Null,'A')
insert into #mytable values ('428547606','12/01/2011','03/31/2012','A')
insert into #mytable values ('951720863','12/01/2011',Null,'A')
insert into #mytable values ('131333186','12/01/2011',Null,'A')
insert into #mytable values ('260895908','12/01/2011',Null,'A')
insert into #mytable values ('553625622','12/01/2011',Null,'A')
insert into #mytable values ('919168813','12/01/2011','01/31/2012','A')
insert into #mytable values ('557902697','12/01/2011',Null,'A')
insert into #mytable values ('379088668','12/01/2011','02/29/2012','A')
insert into #mytable values ('602081252','12/01/2011',Null,'A')
insert into #mytable values ('716393277','12/01/2011','01/31/2012','A')
insert into #mytable values ('300006158','12/01/2011',Null,'A')
insert into #mytable values ('157499166','01/01/2012','03/31/2012','A')
insert into #mytable values ('249228354','01/01/2012',Null,'A')
insert into #mytable values ('446726968','01/01/2012','04/30/2012','A')
insert into #mytable values ('975883457','01/01/2012','03/31/2012','A')
insert into #mytable values ('847326744','01/01/2012',Null,'A')
insert into #mytable values ('434075176','01/01/2012','03/31/2012','A')
insert into #mytable values ('296098224','01/01/2012','04/30/2012','A')
insert into #mytable values ('370215156','12/01/2011',Null,'A')
insert into #mytable values ('647742333','12/01/2011',Null,'A')
insert into #mytable values ('528683544','12/01/2011','01/31/2012','A')
insert into #mytable values ('717022168','12/01/2011',Null,'A')
insert into #mytable values ('417045232','12/01/2011','02/29/2012','A')
insert into #mytable values ('485375006','12/01/2011',Null,'A')
insert into #mytable values ('963527175','12/01/2011','04/30/2012','A')
insert into #mytable values ('328693654','12/01/2011',Null,'A')
insert into #mytable values ('631187903','12/01/2011','02/29/2012','A')
insert into #mytable values ('903184004','12/01/2011',Null,'A')
insert into #mytable values ('833363486','12/01/2011',Null,'A')
insert into #mytable values ('185935730','12/01/2011',Null,'A')
insert into #mytable values ('479006496','12/01/2011','01/31/2012','A')
insert into #mytable values ('302562202','12/01/2011',Null,'A')
insert into #mytable values ('230389221','12/01/2011',Null,'A')
insert into #mytable values ('187950850','12/01/2011','02/29/2012','A')
insert into #mytable values ('543426566','12/01/2011',Null,'A')
insert into #mytable values ('387473716','01/01/2012','04/30/2012','A')
insert into #mytable values ('365407230','01/01/2012','04/30/2012','A')
insert into #mytable values ('667394514','01/01/2012',Null,'A')
insert into #mytable values ('572158672','01/01/2012',Null,'A')
insert into #mytable values ('952548642','01/01/2012',Null,'A')
insert into #mytable values ('803871629','01/01/2012',Null,'A')
insert into #mytable values ('594658511','01/01/2012',Null,'A')
insert into #mytable values ('430877573','01/01/2012',Null,'A')
Would help if you created an ExpectedResults table and populated it with the expected results based on the sample data above.
June 22, 2012 at 10:46 am
Couple things I have noticed in the sample data, no multiple records for the same ID and no inactive IDs.
June 22, 2012 at 11:13 am
Lynn, there are only a few that actually are 'Continuously' active though. They have to be active in December 2011, and January, February, March, April, May, and June of 2012 with a Status of 'A' to be considered Continuous.
Like this:
create table #Expected
(
ID varchar(12),
EffectiveDate datetime,
TermDate datetime,
Status varchar(1),
Continuous bit
)
insert into #Expected values ('503484823','01/01/2012',Null,'A',0)
insert into #Expected values ('592879472','01/01/2012',Null,'A',0)
insert into #Expected values ('929921311','01/01/2012',Null,'A',0)
insert into #Expected values ('239826234','01/01/2012',Null,'A',0)
insert into #Expected values ('616546060','12/01/2011',Null,'A',1)
insert into #Expected values ('978047548','12/01/2011',Null,'A',1)
insert into #Expected values ('524739641','12/01/2011',Null,'A',1)
insert into #Expected values ('428547606','12/01/2011','03/31/2012','A',0)
insert into #Expected values ('951720863','12/01/2011',Null,'A',1)
insert into #Expected values ('131333186','12/01/2011',Null,'A',1)
insert into #Expected values ('260895908','12/01/2011',Null,'A',1)
insert into #Expected values ('553625622','12/01/2011',Null,'A',1)
insert into #Expected values ('919168813','12/01/2011','01/31/2012','A',0)
insert into #Expected values ('557902697','12/01/2011',Null,'A',1)
insert into #Expected values ('379088668','12/01/2011','02/29/2012','A',0)
insert into #Expected values ('602081252','12/01/2011',Null,'A',1)
insert into #Expected values ('716393277','12/01/2011','01/31/2012','A',0)
insert into #Expected values ('300006158','12/01/2011',Null,'A',1)
insert into #Expected values ('157499166','01/01/2012','03/31/2012','A',0)
insert into #Expected values ('249228354','01/01/2012',Null,'A',0)
insert into #Expected values ('446726968','01/01/2012','04/30/2012','A',0)
insert into #Expected values ('975883457','01/01/2012','03/31/2012','A',0)
insert into #Expected values ('847326744','01/01/2012',Null,'A',0)
insert into #Expected values ('434075176','01/01/2012','03/31/2012','A',0)
insert into #Expected values ('296098224','01/01/2012','04/30/2012','A',0)
insert into #Expected values ('370215156','12/01/2011',Null,'A',1)
insert into #Expected values ('647742333','12/01/2011',Null,'A',1)
insert into #Expected values ('528683544','12/01/2011','01/31/2012','A',0)
insert into #Expected values ('717022168','12/01/2011',Null,'A',1)
insert into #Expected values ('417045232','12/01/2011','02/29/2012','A',0)
insert into #Expected values ('485375006','12/01/2011',Null,'A',1)
insert into #Expected values ('963527175','12/01/2011','04/30/2012','A',0)
insert into #Expected values ('328693654','12/01/2011',Null,'A',1)
insert into #Expected values ('631187903','12/01/2011','02/29/2012','A',0)
insert into #Expected values ('903184004','12/01/2011',Null,'A',1)
insert into #Expected values ('833363486','12/01/2011',Null,'A',1)
insert into #Expected values ('185935730','12/01/2011',Null,'A',1)
insert into #Expected values ('479006496','12/01/2011','01/31/2012','A',0)
insert into #Expected values ('302562202','12/01/2011',Null,'A',1)
insert into #Expected values ('230389221','12/01/2011',Null,'A',1)
insert into #Expected values ('187950850','12/01/2011','02/29/2012','A',0)
insert into #Expected values ('543426566','12/01/2011',Null,'A',1)
insert into #Expected values ('387473716','01/01/2012','04/30/2012','A',0)
insert into #Expected values ('365407230','01/01/2012','04/30/2012','A',0)
insert into #Expected values ('667394514','01/01/2012',Null,'A',0)
insert into #Expected values ('572158672','01/01/2012',Null,'A',0)
insert into #Expected values ('952548642','01/01/2012',Null,'A',0)
insert into #Expected values ('803871629','01/01/2012',Null,'A',0)
insert into #Expected values ('594658511','01/01/2012',Null,'A',0)
insert into #Expected values ('430877573','01/01/2012',Null,'A',0)
June 22, 2012 at 11:19 am
themangoagent (6/22/2012)
Lynn, there are only a few that actually are 'Continuously' active though. They have to be active in December 2011, and January, February, March, April, May, and June of 2012 with a Status of 'A' to be considered Continuous.Like this:
create table #Expected(
ID varchar(12),
EffectiveDate datetime,
TermDate datetime,
Status varchar(1),
Continuous bit
)
insert into #Expected values ('503484823','01/01/2012',Null,'A',0)
insert into #Expected values ('592879472','01/01/2012',Null,'A',0)
insert into #Expected values ('929921311','01/01/2012',Null,'A',0)
insert into #Expected values ('239826234','01/01/2012',Null,'A',0)
insert into #Expected values ('616546060','12/01/2011',Null,'A',1)
insert into #Expected values ('978047548','12/01/2011',Null,'A',1)
insert into #Expected values ('524739641','12/01/2011',Null,'A',1)
insert into #Expected values ('428547606','12/01/2011','03/31/2012','A',0)
insert into #Expected values ('951720863','12/01/2011',Null,'A',1)
insert into #Expected values ('131333186','12/01/2011',Null,'A',1)
insert into #Expected values ('260895908','12/01/2011',Null,'A',1)
insert into #Expected values ('553625622','12/01/2011',Null,'A',1)
insert into #Expected values ('919168813','12/01/2011','01/31/2012','A',0)
insert into #Expected values ('557902697','12/01/2011',Null,'A',1)
insert into #Expected values ('379088668','12/01/2011','02/29/2012','A',0)
insert into #Expected values ('602081252','12/01/2011',Null,'A',1)
insert into #Expected values ('716393277','12/01/2011','01/31/2012','A',0)
insert into #Expected values ('300006158','12/01/2011',Null,'A',1)
insert into #Expected values ('157499166','01/01/2012','03/31/2012','A',0)
insert into #Expected values ('249228354','01/01/2012',Null,'A',0)
insert into #Expected values ('446726968','01/01/2012','04/30/2012','A',0)
insert into #Expected values ('975883457','01/01/2012','03/31/2012','A',0)
insert into #Expected values ('847326744','01/01/2012',Null,'A',0)
insert into #Expected values ('434075176','01/01/2012','03/31/2012','A',0)
insert into #Expected values ('296098224','01/01/2012','04/30/2012','A',0)
insert into #Expected values ('370215156','12/01/2011',Null,'A',1)
insert into #Expected values ('647742333','12/01/2011',Null,'A',1)
insert into #Expected values ('528683544','12/01/2011','01/31/2012','A',0)
insert into #Expected values ('717022168','12/01/2011',Null,'A',1)
insert into #Expected values ('417045232','12/01/2011','02/29/2012','A',0)
insert into #Expected values ('485375006','12/01/2011',Null,'A',1)
insert into #Expected values ('963527175','12/01/2011','04/30/2012','A',0)
insert into #Expected values ('328693654','12/01/2011',Null,'A',1)
insert into #Expected values ('631187903','12/01/2011','02/29/2012','A',0)
insert into #Expected values ('903184004','12/01/2011',Null,'A',1)
insert into #Expected values ('833363486','12/01/2011',Null,'A',1)
insert into #Expected values ('185935730','12/01/2011',Null,'A',1)
insert into #Expected values ('479006496','12/01/2011','01/31/2012','A',0)
insert into #Expected values ('302562202','12/01/2011',Null,'A',1)
insert into #Expected values ('230389221','12/01/2011',Null,'A',1)
insert into #Expected values ('187950850','12/01/2011','02/29/2012','A',0)
insert into #Expected values ('543426566','12/01/2011',Null,'A',1)
insert into #Expected values ('387473716','01/01/2012','04/30/2012','A',0)
insert into #Expected values ('365407230','01/01/2012','04/30/2012','A',0)
insert into #Expected values ('667394514','01/01/2012',Null,'A',0)
insert into #Expected values ('572158672','01/01/2012',Null,'A',0)
insert into #Expected values ('952548642','01/01/2012',Null,'A',0)
insert into #Expected values ('803871629','01/01/2012',Null,'A',0)
insert into #Expected values ('594658511','01/01/2012',Null,'A',0)
insert into #Expected values ('430877573','01/01/2012',Null,'A',0)
Have to ask, based on this: I need to select only those ID's who have an effective date on or before 12/01/2011, why are there records with an effective date of 01/01/2012 in the expected results?
Also, why are there records that have a termination date that would invalidate the premise that thay are active during the entire period of December 2011, and January, February, March, April, May, and June of 2012?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply