April 25, 2016 at 12:09 pm
Luis C:
Slight problem with your second query.
Certainly It gurantees that the member had a total of 9 records for the last nine months. However it does not gurantee that the total number of records each member had is 9.
Ex: A member can have memebership starting 201501 till 201605.
So yes, your query will select this member. However what I need is one more condition. That is they do not have a anything more than a total of 9 records in the table.
So my result set is for those members who joined for the first time 9 months back.
and their total membership months should not be more than 9 months.
Sorry if I did not mention this earlier
April 25, 2016 at 12:21 pm
Never mind, don't worry, I figured out...
By including a CTE and joining with it I was able to filter the records..
Done!
Good Job, Thanks
Declare @monthyear as char(6); --yyyymm
SET @monthyear = '201605';
With CTE
as
(
Select MEMBERNBR , COUNT(*) as CNT
FROM
MHPDW2.edw.[MEMBER].[MemberMonthHistory]
WHERE
PlanCode IN ( 'MMP','MCR' )
GROUP BY MEMBERNBR HAVING COUNT(*) = 9
)
Select
M.MemberNbr as MemberNbr,
ISNULL(M.MEMBERNAME,'') as Name,
ISNULL(M.MemberDateOfBirth,'') as Dob,
ISNULL(M.MEmberHomePhoneNbr,'') as HomeTel,
RTRIM(ISNULL(M.MemberAddress1,'')) + RTRIM(ISNULL(M.MemberAddress2,''))
+
CASE WHEN ISNULL(M.MemberCity,'') > '' THEN ' ,' + RTRIM(ISNULL(M.MemberCity,'')) ELSE '' END
+
CASE WHEN ISNULL(M.MemberState,'') > '' THEN ' ,' + RTRIM(ISNULL(M.MemberState,'')) ELSE '' END
+
CASE WHEN ISNULL(M.MemberZipCode,'') > '' THEN ' ,' + RTRIM(ISNULL(M.MemberZipCode,'')) ELSE '' END
as ADDR
FROM MHPDW2.edw.[MEMBER].Dmember M
INNER JOIN CTE C on ( C.MEMBERNBR = M.MemberNbr )
WHERE
M.MEMBERNBR in
(
Select Distinct MemberNbr
FROM MHPDW2.edw.[MEMBER].[MemberMonthHistory]
WHERE
PlanCode IN ( 'MMP','MCR' )
AND
EFFECTIVEMONTH < @monthyear
AND EFFECTIVEMONTH >= CONVERT( char(6), DATEADD(MM, -9, @monthyear + '01'), 112)
GROUP BY MemberNbr
HAVING COUNT(DISTINCT EFFECTIVEMONTH) = 9
)
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply