June 21, 2012 at 12:49 pm
Hello,
I've come across a challenging aspect of trying to pull eligibility data for members. My data looks something like this:
ID.........EffectiveDate......TermDate...............Status.....................Translates to:
123.........01/01/2012.........01/31/2012...............A..............................Active
123.........02/01/2012.........02/29/2012...............A..............................Active
123.........03/01/2012.........03/31/2012...............D..............................Inactive
123.........04/01/2012.........NULL.........................A..............................Active
555.........01/01/2012.........NULL.........................D..............................Inactive
999.........02/01/2012.........04/30/2012................A..............................Active
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:54 pm
Duplicated topic. Please direct all conversation here:
http://www.sqlservercentral.com/Forums/Topic1319548-8-1.aspx
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply