MNTH_END_DT(format'YYYYMM') (char(6)) as RPT_MNTH,
FROM MBRSHP_MNTH_CNT MMC
WHERE MMC.CLNDR_MNTH_END_DT between '2010-01-01' and '2010-06-30'
GROUP BY 1
Rather than look at one date range like the above query is doing, I would like to be able to compare 2 separate time periods (YEAR/QTR TO YEAR/QTR)
How would I write a case when statement that will calculate average members only for a particular quarter/year? Can I use system date to determine months to include for QTR?
Would like output to average the 4th quarter (take an average of the 3 months) for 2 time periods (2013 and 2014)
AVG(MMC.MMBR_CVRG_CNT) AS PERIOD_2_Q4_AVG_MDL
AVG(MMC.MMBR_CVRG_CNT) AS PERIOD_1_Q4_AVG_MDL
e.g. for the previous year's Q4:
AVG(case when MNTH_END_DT
between cast(extract(year from add_months(current_date, -12)) || '-10-01' as date)
and cast(extract(year from add_months(current_date, -12)) || '-12-31' as date)
Thank you for the reply. Could you provide a little explanation as I'm not sure I understand this enough to duplicate into my query. If I specifically want to look at Q42013 and Q42014 data, how/where would I include that criteria?
I'm not sure what the -12 is doing or the '-10-01' etc. is doing.
Your help is greatly appreciated
add_months(current_date, -12) substracts 1 year from today's date, resulting in last year 2014.
Concatenating 2014 and '-10-01' results in a valid date.
If you want Q42013/14 hardcoded instead of dynamic you might simply do
AVG(case when MNTH_END_DT between date '2014-10-01' and date '204-12-31'
The data should be in the 10 thousand, and example of Oct, Nov, Dec data may be 23,009; 23,145, 23,971; I would need an average of those three. Currently this query returns decimals number less than 1 like .0043 which is not an accurate relfection of an average of three months of data.
Here is entire query
AVG(case when R.MNTH_END_DT between date '2014-10-01' and date '2014-12-31' then R.CVRG_CNT) end AS "PERIOD2",
AVG(CASE WHEN R.MNTH_END_DT between date '2013-10-01' and date '2013-12-31' then R.CVRG_CNT) end AS "PERIOD1"
These are the values that get returned from above query when attempting to get an average. Period 1 and 2 should be something around 23,000 (Oct, Nov, Dec data 23,009; 23,145; 23,971 respectively)
Also please note the "and" after the WHERE clause should be removed so no need to point that out, that is not the issue here. Thank you.