Database

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

03-11-2012
04:44 PM

03-11-2012
04:44 PM

Period-to-Date Values without CTE or Subquery

I'm curious to see if there is a way to get period-to-date values (e.g. WTD, MTD, QTD, YTD) without using a CTE or subquery. Here is an example query using a CTE:

with calendar(cal_dt, week_beg_dt, week_end_dt, month_beg_dt, month_end_dt, qtr_beg_dt, qtr_end_dt, year_beg_dt, year_end_dt)

as (

select cal_dt, week_beg_dt, week_end_dt, month_beg_dt, month_end_dt, qtr_beg_dt, qtr_end_dt, year_beg_dt, year_end_dt

from dim_calendar

where cal_dt = (date - 1)

)

select

sum(case when sales.cal_dt between cal.week_beg_dt and cal.week_end_dt then ssa.gmv_usd_amt end) as WTD,

sum(case when sales.cal_dt between cal.month_beg_dt and cal.month_end_dt then ssa.gmv_usd_amt end) as MTD,

sum(case when sales.cal_dt between cal.qtr_beg_dt and cal.qtr_end_dt then ssa.gmv_usd_amt end) as QTD,

sum(case when sales.cal_dt between cal.year_beg_dt and cal.year_end_dt then ssa.gmv_usd_amt end) as YTD

from

fact_sales sales, calendar cal

Is it possible to do this in a single query without using a CTE or subquery?

2 REPLIES

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

03-12-2012
12:30 AM

03-12-2012
12:30 AM

Re: Period-to-Date Values without CTE or Subquery

Why do not simply join directly?

select

sum(case when sales.cal_dt between cal.week_beg_dt and cal.week_end_dt then ssa.gmv_usd_amt end) as WTD,

sum(case when sales.cal_dt between cal.month_beg_dt and cal.month_end_dt then ssa.gmv_usd_amt end) as MTD,

sum(case when sales.cal_dt between cal.qtr_beg_dt and cal.qtr_end_dt then ssa.gmv_usd_amt end) as QTD,

sum(case when sales.cal_dt between cal.year_beg_dt and cal.year_end_dt then ssa.gmv_usd_amt end) as YTD

from fact_sales sales

join

calendar cal

on sales.cal_dt between cal.year_beg_dt and cal.year_end_dt

where cal_dt = (date - 1)

;

The condition

sales.cal_dt between cal.year_beg_dt and cal.year_end_dt

should cover all the other three as well.

Or did I miss something?

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

03-12-2012
09:28 PM

03-12-2012
09:28 PM

Re: Period-to-Date Values without CTE or Subquery

No, you aren't missing anything. I knew I was missing something easy, but I was drawing a blank. Thanks!