Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2015
08:23 PM

02-16-2015
08:23 PM

**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

8 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2015
09:24 PM

02-16-2015
09:24 PM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2015
10:18 PM

02-16-2015
10:18 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-17-2015
01:12 AM

02-17-2015
01:12 AM

**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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-17-2015
08:54 PM

02-17-2015
08:54 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-18-2015
03:03 AM

02-18-2015
03:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-18-2015
06:50 AM

02-18-2015
06:50 AM

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"

**FROM**R_MNTH_CNT R

**WHERE**

**AND** R.ORG_NBR='8'

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-18-2015
09:04 AM

02-18-2015
09:04 AM

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 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-18-2015
11:12 AM

02-18-2015
11:12 AM

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.

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(

CASEWHEN 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