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

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

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

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)

- 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

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

- 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

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)

- 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

Re: Include counting QTR year in SUM statement

- 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

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.

- 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

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"

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

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 |

- 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

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.

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