May 11, 2016 at 6:09 am
Question:
I need to write the select statement that will pick the members
who fulfill the following criteria:
1.) Had membership coverage on the month 201512
2.) Had coverage in any months from 201505 through 201512
3.) The coverage must be continuous going all the way to 201502
Ex1: A member who had coverage from 201510 to 201512 qualifies.
Ex2: A member who had coverage in 201506, then stopped and had
coverage again in 201511 and 201512 will not qualify ( because he did not have continuous coverage )
As it is the query below will cover requirements 1 and 2 but not 3. I need
help with requirement #3 ( Which is to filter out those folks who never had continuous coverage )
If object_id( 'tempdb..#coverage' ) IS NOT NULL drop table #coverage;
Create table #coverage ( the_month varchar(6) , member_id varchar(20) ) ;
INSERT INTO #coverage( the_month, member_id )
Select '201502' , '12345'
UNION
Select '201503' , '12345'
UNION
Select '201504' , '12345'
UNION
Select '201505' , '12345'
UNION
Select '201506' , '12345'
UNION
Select '201507' , '12345'
UNION
Select '201508' , '12345'
UNION
Select '201509' , '12345'
UNION
Select '201510' , '12345'
UNION
Select '201511' , '12345'
UNION
Select '201512' , '12345'
UNION
Select '201511' , '54321'
UNION
Select '201512' , '54321'
UNION
Select '201505' , '66666'
UNION
Select '201506' , '66666'
UNION
Select '201509' , '66666'
UNION
Select '201510' , '66666'
UNION
Select '201511' , '66666'
UNION
Select '201512' , '66666'
Select member_id , COUNT(*) as CNT, MAX(RIGHT(the_month,2)) as MAXMON
FROM
#coverage
WHERE
RIGHT(the_month,2) >= '05'
AND
RIGHT(the_month,2) <= '12'
GROUP BY member_id HAVING COUNT(*) > 0
AND MAX(RIGHT(the_month,2)) = 12
May 11, 2016 at 6:25 am
look up itzik ben-gan gaps and islands tsql and you should find some amazing solutions to this problem. You need to get the contiguous range of dates for each grouping and make sure their are no gaps in the range.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 11, 2016 at 6:36 am
Well, there will not be a continuous range of dates for each grouping. That is the challenge here. Some members have coverage all the way up until 201512.
Others have some coverage and then drop and they join again.
May 11, 2016 at 6:46 am
mw112009 (5/11/2016)
Question:I need to write the select statement that will pick the members
who fulfill the following criteria:
1.) Had membership coverage on the month 201512
2.) Had coverage in any months from 201505 through 201512
3.) The coverage must be continuous going all the way to 201502
Ex1: A member who had coverage from 201510 to 201512 qualifies.
Ex2: A member who had coverage in 201506, then stopped and had
coverage again in 201511 and 201512 will not qualify ( because he did not have continuous coverage )
As it is the query below will cover requirements 1 and 2 but not 3. I need
help with requirement #3 ( Which is to filter out those folks who never had continuous coverage )
Check out the links provided by Kevin. They will help you. Kevin's good at posting helpful links.
But if you're looking for the easy solution, the question is, do you keep "drop" history flags? You could check this for the non-contiguous coverage. Or, if you are always looking for the same number of months, you could do a count on coverage. But that would drop out your example number 1.
Alternatively, you could loop through your recordset and check it row-by-agonizing-row (RBAR), but that's not the ideal way to do it.
May 11, 2016 at 9:10 am
Folks,
You may ignore this post from now.
I did find my solution. Works well!
Select
member_id,
COUNT(*) as CNT,
MAX(RIGHT(the_month,2)) as MAXMON,
SUM( CAST(RIGHT(the_month,2) as int ) ) as SUM_OF_MONTH
FROM
#coverage
WHERE
RIGHT(the_month,2) >= '05'
AND
RIGHT(the_month,2) <= '12'
GROUP BY member_id HAVING COUNT(*) > 0
AND MAX(RIGHT(the_month,2)) = 12
AND
SUM(CAST(RIGHT(the_month,2) as int)) = (COUNT(*)*12-((COUNT(*)*(COUNT(*)-1))/2))-- Continuous Coverage
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply