All Forums Analytics
Mooli 21 posts Joined 12/14
21 Feb 2015
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.
 

Glass 225 posts Joined 04/10
23 Feb 2015

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

Mooli 21 posts Joined 12/14
23 Feb 2015

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

dnoeth 4628 posts Joined 11/04
23 Feb 2015

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

Dieter

Mooli 21 posts Joined 12/14
23 Feb 2015

 

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.
 
 

You must sign in to leave a comment.