Include counting QTR year in SUM statement

Analytics
Enthusiast

Include counting QTR year in SUM statement

SELECT

MNTH_END_DT(format'YYYYMM') (char(6)) as RPT_MNTH,

NBR,

SUM(MMBR_CVRG_CNT)

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

SAMPLE OUTPUT

NBR

PERIOD_2_Q4_AVG_MDL

PERIOD_1_Q4_AVG_MDL

 

Thank you

Tags (1)
8 REPLIES
Senior Apprentice

Re: Include counting QTR year in SUM statement

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)
then MMC.MMBR_CVRG_CNT
end)
Enthusiast

Re: Include counting QTR year in SUM statement

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

Senior Apprentice

Re: Include counting QTR year in SUM statement

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'
then MMC.MMBR_CVRG_CNT
end)
Enthusiast

Re: Include counting QTR year in SUM statement

Thank you again, I am getting closer to getting this to work but the average is not calculating correctly. I chose to hard code:

AVG(CASE WHEN MC.MNTH_END_DT between date '2014-10-01' and date '2014-12-31' then (MC.CVRG_CNT) end) AS "PERIOD2",

AVG(CASE WHEN MC.MNTH_END_DT between date '2013-10-01' and date '2013-12-31' then (MC.CVRG_CNT) end) AS "PERIOD1"

It returns a decimal number less than 1 for period 1 and 2 which isn't correct, I need an average of MC.CVRG_CNT for Oct, Nov, & Dec. Any suggestions where I'm going wrong??

Thanks




Senior Apprentice

Re: Include counting QTR year in SUM statement

Without knowing your data and the full query it's hard to tell what's wrong, the syntax is ok.

Enthusiast

Re: Include counting QTR year in SUM statement

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

SELECT

R.ORG_NBR,

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"

FROMR_MNTH_CNT R

WHERE

AND R.ORG_NBR='8'

Enthusiast

Re: Include counting QTR year in SUM statement

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)





P0 PERIOD2 PERIOD1
0052 0.187404116 0.186782519
Enthusiast

Re: Include counting QTR year in SUM statement

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.