Period-to-Date Values without CTE or Subquery

Database

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?

Tags (3)
2 REPLIES
Supporter

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?

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!