Getting an average for multiple date ranges

Analytics
Enthusiast

Getting an average for multiple date ranges

My apologies for posting this again, but I am still have some difficulties..

The results should be somewhere around 23k but am getting a decimal less than 1 instead. I'm attempting to look at two date ranges and average the months within that date range. Example -  Oct, Nov, Dec data may be 23,009; 23,145, 23,971; I would need an average of those three months.  Currently this query returns decimals number less than 1 like .0043 which is not an accurate calculation 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

 R.ORG_NBR='8'




Help is appreciated.

4 REPLIES
Enthusiast

Re: Getting an average for multiple date ranges

Mooli,

this query will fail based on the positioning of your 'End' of case statement. It would need to be inside parens

Also,

If you would provide DDL from FROMR_MNTH_CNT it may help.

Rglass

Enthusiast

Re: Getting an average for multiple date ranges

Hi Glass, thank you for your reply and help.

I did move the END inside parens and same result (also had remove one outer paren when I did this so I think it was essentially the same code). I still get a decimal value not sure why. I can sum the 3 months and get an accurate number but AVG of the 3 is not working right. Here is the output...

Also could you please provide instructions on how to get DDL - I have googled this but only find entire DB not just for 1 table. Thank you.






R_ORG_NBR Sum(BR_CVRG_CNT) PERIOD2 PERIOD1
888 530759 0.187404116 0.186782519
Senior Apprentice

Re: Getting an average for multiple date ranges

You get the DDL using SHOW TABLE xy; or SHOW VIEW xy; or SHOW SELECT * FROM xy;

Enthusiast

Re: Getting an average for multiple date ranges




R_CVRG_CNT DECIMAL(2,0) NOT NULL COMPRESS (0. ,1. ),

Here is DDL for above - please advise how to get AVG to calculate properly.

Thank you.