Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.