Creating a Dynamic YoY Date Range in Case Statement for Current Quarter QTD

Database

Creating a Dynamic YoY Date Range in Case Statement for Current Quarter QTD

Let's say I want to find YoY QTD Q3 unique customers and today is 9/1/2016.  How do I make it so I do not need to manually need to update the bolded date ranges in the case when statement.

If today were 10/5/2016 the date ranges would automatically change to '2016-10-01' and '2016-10-04' then 'Q4_2016' '2015-10-01' and '2015-10-04' then 'Q4_2015'

Col 1: Date

Col 2: UserId

select

count(distinct(userid)),

case when date between '2016-07-01' and '2016-08-31' then 'Q3_2016'

when date between '2015-07-01' and '2015-08-31' then 'Q3_2015' else '' end as dte_range

from table 1

where dte_range <> ''

Thanks

K

2 REPLIES
Senior Apprentice

Re: Creating a Dynamic YoY Date Range in Case Statement for Current Quarter QTD

You can apply TRUNC & TO_CHAR like this:

CASE
WHEN date between TRUNC(CURRENT_DATE-1, 'Q') AND CURRENT_DATE-1
THEN TO_CHAR(CURRENT_DATE-1, '"Q"Q"_"yyyy')
WHEN date between TRUNC(ADD_MONTHS(CURRENT_DATE-1,-12), 'Q') AND ADD_MONTHS(CURRENT_DATE-1,-12)
THEN TO_CHAR(ADD_MONTHS(CURRENT_DATE-1,-12), '"Q"Q"_"yyyy')
END

Re: Creating a Dynamic YoY Date Range in Case Statement for Current Quarter QTD

Thank you Dieter this works!