Volatile Tables and Dates

Teradata Debugger
Fan

Volatile Tables and Dates

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.

 

sel
AVG(rrs_conc_medrx),
count(distinct mbrs.mbr_key)


From mbrs

Left Join RSK_SCR_VIEWS.Scores R
ON mbrs.mbr_key = r.mbr_key

Where
eom_dt between '2017-01-01' and  '2017-05-31'

order by 1 DESC
;

4 REPLIES
Junior Contributor

Re: Volatile Tables and Dates

From which tables are the columns rrs_conc_medrxeom_dt?

Fan

Re: Volatile Tables and Dates

Hi -

 

rrs_conc_medrx & eom_dt are both being pulled from the RSK_SCR_VIEWS.Scores R table which is being left joined in.

 

Junior Contributor

Re: Volatile Tables and Dates

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?

 

Fan

Re: Volatile Tables and Dates

Hi -

 

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.