Link to home
Start Free TrialLog in
Avatar of woodje
woodjeFlag for United States of America

asked on

Oracle SQL for case or if statement in the where clause

Hello experts,

I have a query that I have built that runs month to date every monday. I dynamically render the date range in my where clause. The issue I have is that my end users have a request that when the monday render falls on the 1st of the month they wish to have the query bring back all of the previous month. If it is not the 1st of the month then it will run month to date to the previous day. I have outlined a sample of the query below. The issue I am having is setting up the where statement to look to see what day it is and then use different date ranges based on the boolan of true or false. This might not be the best way I am open to all suggestions.

Code Sample:
select
*
from css.center_metrics

where trunc(status_dt) between case when extract(day from current_date) = 1 then to_date('2012-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-09-31 00:00:00','yyyy-mm-dd hh24:mi:ss') else to_date('2012-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-10-30 00:00:00','yyyy-mm-dd hh24:mi:ss') end


Thanks,
Jeff
Avatar of Sean Stuber
Sean Stuber

try one of these

the first is the more exact translation of what you asked for
the second is a more efficient way of doing the same thing (i.e. don't use case)

select
*
from css.center_metrics

where case when extract(day from current_date) = 1
     and trunc(status_dt) between  to_date('2012-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-09-31 00:00:00','yyyy-mm-dd hh24:mi:ss')
     then 1
     when extract(day from current_date) <> 1 and
trunc(status_dt) between     to_date('2012-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-10-30 00:00:00','yyyy-mm-dd hh24:mi:ss')
then 1 end = 1;


select
*
from css.center_metrics

where (extract(day from current_date) = 1 and   trunc(status_dt) between  to_date('2012-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-09-31 00:00:00','yyyy-mm-dd hh24:mi:ss'))
or    ( extract(day from current_date) <> 1 and
trunc(status_dt) between     to_date('2012-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-10-30 00:00:00','yyyy-mm-dd hh24:mi:ss'));
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is what I meant in the previous post...
Avatar of woodje

ASKER

All of them worked perfect. Yes we do have that column indexed so the last would be the best for overhead I will use it.