All Forums Database
kthumm11 2 posts Joined 09/16
01 Sep 2016
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

dnoeth 4628 posts Joined 11/04
02 Sep 2016

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

 

Dieter

kthumm11 2 posts Joined 09/16
06 Sep 2016

Thank you Dieter this works!

You must sign in to leave a comment.