I have created a volatile table called "mbrs" for members of a specific population. The next step is to pull an average score for the entire population of members. The code below is what I have but when I run it and check my join condition there are multiple eom_dt (end of month dates) per member. So for instance, member 5675 shows up 4 times with four different end of month dates. Since multiple dates are being pulled for each members, it skews the average score that I am trying to attain for the entire population. I am trying to bring back one member and one score for a specified time frame.
Any idea of how I can limit my answer set to only bring back one unique member and member score without having multiple rows of members with multiple end of months dates and multiple scores.
Left Join RSK_SCR_VIEWS.Scores R
ON mbrs.mbr_key = r.mbr_key
eom_dt between '2017-01-01' and '2017-05-31'
order by 1 DESC
When both columns are from the Scores table the Where-condition changes the Outer Join to an Inner.
Are the rrs_conc_medrx values the same when member 5675 shows up 4 times with four different end of month dates?
SEL Avg(R.rrs_conc_medrx), Count(DISTINCT mbrs.mbr_key) -- DISTINCT is probably no longer needed FROM mbrs LEFT JOIN -- do you really need an outer join? ( -- return a single row per mbr_key, if there are multple values for rrs_conc_medrx you can switch to MIN/MAX/AVG +GROUP BY SELECT DISTINCT mbr_key, rrs_conc_medrx FROM RSK_SCR_VIEWS.Scores WHERE eom_dt BETWEEN '2017-01-01' AND '2017-05-31' ) AS R ON mbrs.mbr_key = R.mbr_key ORDER BY 1 DESC -- why do you order by when there's only a single row?
So when member 5675 shows up 4 times, the score at the end of month date is different each time. I am essentially trying to average the scores for each member then take an overall average of the entire population.