April 25, 2016 at 9:13 am
what I need is a list of members who were enrolled continously for the last 9 months.
For each they are enrolled in a plan there will be a row in the table.
What I want is those members who have had continuous coverage for the
last 9 months.
( Now, I purposely hard coded some values just so you get the idea what I am looking for.. But what I need is the query to have a dynamic expression so that I can feed in any mmyy and it will pull the results )
Declare @monthyear as varchar(4) --mmyy
@monthyear = '052016'
Select distinct MemberNbr
FROM
MHPDW.edw.[MEMBER].[MemberMonthHistory]
WHERE
PlanCode IN ( 'MMP','MCR' )
AND
EFFECTIVEMONTH in ( '042016' )
AND
EFFECTIVEMONTH in ( '032016' )
AND
EFFECTIVEMONTH in ( '022016' )
AND ....
April 25, 2016 at 9:16 am
Sorry, please used the SQL below,
The effectivemonth is in the format YYYYMM
So please use this syntax
Declare @monthyear as varchar(4) --yyyymm
@monthyear = '201605'
Select distinct MemberNbr
FROM
MHPDW.edw.[MEMBER].[MemberMonthHistory]
WHERE
PlanCode IN ( 'MMP','MCR' )
AND
EFFECTIVEMONTH in ( '201604' )
AND
EFFECTIVEMONTH in ( '201603' )
AND
EFFECTIVEMONTH in ( '201602' )
AND
.....
April 25, 2016 at 9:20 am
mw112009 (4/25/2016)
Sorry, please used the SQL below,The effectivemonth is in the format YYYYMM
So please use this syntax
Declare @monthyear as varchar(4) --yyyymm
@monthyear = '201605'
Select distinct MemberNbr
FROM
MHPDW.edw.[MEMBER].[MemberMonthHistory]
WHERE
PlanCode IN ( 'MMP','MCR' )
AND
EFFECTIVEMONTH in ( '201604' )
AND
EFFECTIVEMONTH in ( '201603' )
AND
EFFECTIVEMONTH in ( '201602' )
AND
.....
Really not enough to give you a good answer. It would help if you provided the DDL (CREATE TABLE statement) for the table involved, some sample (not production) data that models the problem domain, and the expected results (best provided as a separate table).
For help with this please read the first article I reference in my signature block regarding asking for help.
April 25, 2016 at 9:32 am
I was going to suggest using a tally table to create one row for each possible month. But that was only because the format MMYYYY doesn't work with ranges. When using YYYYMM a range can work perfectly.
I'm including both solutions.
Declare @monthyear as char(6); --yyyymm
SET @monthyear = '201605';
Select distinct MemberNbr
FROM MHPDW.edw.[MEMBER].[MemberMonthHistory]
WHERE PlanCode IN ( 'MMP','MCR' )
AND EFFECTIVEMONTH in ( SELECT CONVERT( char(6), DATEADD(MM, -n, @monthyear + '01'), 112)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))E(n)
);
Select distinct MemberNbr
FROM MHPDW.edw.[MEMBER].[MemberMonthHistory]
WHERE PlanCode IN ( 'MMP','MCR' )
AND EFFECTIVEMONTH < @monthyear
AND EFFECTIVEMONTH >= CONVERT( char(6), DATEADD(MM, -9, @monthyear + '01'), 112);
One thing to note on your original post is that when using
EFFECTIVEMONTH in ( '201604' )
AND
EFFECTIVEMONTH in ( '201603' )
AND
EFFECTIVEMONTH in ( '201602' )
AND
You're giving conditions that define different values for the same column. That won't ever be true. For something like that you should use EFFECTIVEMONTH in ( '201604' , '201603' , '201602' ). That's what the IN operator is for.
April 25, 2016 at 9:33 am
See data attached...
Member 00028414401 has been enrolled since 201301 onwards.
For each month he/she gets a row in the table.
I think this is a simple question but I need the SQl skills from you.
Here is a another way to ask the question.
How can I write a query to validate that member 00028414401 has atleast
12 rows in the table and each row will cover a month for the year 2013.
So that means there are 12 rows ( at least ) and they represent 201301, 201302,
....201312
How about that ? Did I explain what I need ?
April 25, 2016 at 9:37 am
Luis C: Thanks
That original query that I wrote is just for explaining what I was looking for.. Did not
bother to include perfect syntax.
Let me try your solution. Thanks again.
April 25, 2016 at 9:45 am
Luis C:
Your solution is close but not perfect.
Select distinct MemberNbr
FROM MHPDW.edw.[MEMBER].[MemberMonthHistory]
WHERE PlanCode IN ( 'MMP','MCR' )
AND EFFECTIVEMONTH in ( SELECT CONVERT( char(6), DATEADD(MM, -n, @monthyear + '01'), 112)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))E(n)
);
This picks the past nine months perfectly and only lists members who had membership in atleast one or more of the 9 months.
What I really need is a list of members who had membership in all 9 months.
So that means a member will have 9 rows in the table for the past 9 months.
I only want the members who have the 9 rows and those must represent the past 9 months. ( The objective here is o see who had continous coverage for all 9 months )
Thanks
April 25, 2016 at 9:51 am
mw112009 (4/25/2016)
Luis C:Your solution is close but not perfect.
Select distinct MemberNbr
FROM MHPDW.edw.[MEMBER].[MemberMonthHistory]
WHERE PlanCode IN ( 'MMP','MCR' )
AND EFFECTIVEMONTH in ( SELECT CONVERT( char(6), DATEADD(MM, -n, @monthyear + '01'), 112)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))E(n)
);
This picks the past nine months perfectly and only lists members who had membership in atleast one or more of the 9 months.
What I really need is a list of members who had membership in all 9 months.
So that means a member will have 9 rows in the table for the past 9 months.
I only want the members who have the 9 rows and those must represent the past 9 months. ( The objective here is o see who had continous coverage for all 9 months )
Thanks
Understood.
Can you provide DDL and sample data?
I'm not creating data to test if I'm giving the right solution. No, an image doesn't work. Read the articles in my signature if you need help posting what we're asking.
April 25, 2016 at 10:08 am
something like this? Sorry, but with nothing to really work with or test against, this is best I could come up.
declare @WorkingDate varchar(6) = '201605';
with Prev9 as (
select
convert(varchar(6), dateadd(month,-(n),TestDate), 112) TestDate
from
(values (cast(@WorkingDate + '01' as date)))dt(TestDate)
cross apply (select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9))dt(n))dt1(n)
), basedata as (
select
rn = count(EffectiveMonth) over (partition by MemberNbr),
mmh.*
from
MHPDW.edw.member.MemberMonthHistory mmh
inner join Prev9 p9
on (mmh.EffectiveMonth = p9.TestDate)
)
select distinct
bd.MemberNbr
from
basedata bd
where
bd.rn = 9;
go
April 25, 2016 at 10:32 am
Luis C:
Here you go ( See attached )
The attached DDL will create a table and has INSERT statements to cover member data for 2 members. That should be sufficient.
Hope this helps.
April 25, 2016 at 10:40 am
Based on your sample data, this should work for you.
Select MemberNbr
FROM #MemberMonthHistory
WHERE EFFECTIVEMONTH < @monthyear
AND EFFECTIVEMONTH >= CONVERT( char(6), DATEADD(MM, -9, @monthyear + '01'), 112)
GROUP BY MemberNbr
HAVING COUNT(DISTINCT EFFECTIVEMONTH) = 9;
If you can be sure that MemberNbr and EFFECTIVEMONTH create a unique key (and it's enforced), you can remove the DISTINCT.
April 25, 2016 at 10:42 am
Luis C and others:
Thanks but I found my solution.....( see below )
Thanks again for the SQL help.
Declare @monthyear as char(6); --yyyymm
SET @monthyear = '201605';
Select MemberNbr, count(*) as CNT
FROM
(
Select distinct MemberNbr, EFFECTIVEMONTH
FROM MHPDW.edw.[MEMBER].[MemberMonthHistory]
WHERE PlanCode IN ( 'MMP','MCR' )
AND EFFECTIVEMONTH in ( SELECT CONVERT( char(6), DATEADD(MM, -n, @monthyear + '01'), 112)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))E(n)
)
)XYZ
GROUP BY MemberNbr HAVING COUNT(*) = 9
April 25, 2016 at 10:47 am
Check the remark on my previous post. It can bring inaccurate results if you use your query.
April 25, 2016 at 11:15 am
I see your point,
So if a member had 9 records and then 2 of them were fro one particular month, then yes it would be inaccurate...
Thanks I will check to make sure that there is the uniqueness you mentioned.
Good catch
April 25, 2016 at 11:52 am
mw112009 (4/25/2016)
Luis C:Here you go ( See attached )
The attached DDL will create a table and has INSERT statements to cover member data for 2 members. That should be sufficient.
Hope this helps.
A couple of comments regarding your sample data.
You didn't include any data for an individual or set of individuals that would not meet the criteria to be returned by your query. Only providing data that will be returned is not sufficient for really testing potential solutions. As Luis also pointed out, what about entries for individuals where the EffectiveDate is duplicated.
Also, you didn't include the plan code in your sample data (and I forgot to check it in my suggested query earlier).
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply