Change date range of WHERE clause based on day of week

[sxschech]

Would like to know how to write the following as part of the where clause:

WHERE

If today is Monday then the where clause would be:

Application_Date is >= Last Friday --last Friday, Saturday, Sunday and today, Monday --

Otherwise the where clause would be

Application_Date = Yesterday only

I tried below and received error message

[Error] Execution (5: 57): ORA-00905: missing keyword

SELECT a.*
FROM admissions_application a
WHERE 
 CASE WHEN to_char(sysdate,'D') = '2' THEN --Monday
                   to_char(application_date,'yyyymmdd') >= to_char(sysdate - 3,'yyyymmdd')
              ELSE 
                   to_char(sysdate - 1) = to_char(application_date)
         END
AND academic_period = '201140'
AND...

 

[carp]

Take a look at earljgray's identical requirements in the Oracle 11 forum.

 

[sxschech]

Carp, thanks for the reference. The information there seems to be a select statement and not a where clause, would I need to do this as a select statement and then do a join rather than a where clause, or how would I use it? I tried below and seems to work, have to see how it looks next week. If I've missed something or misunderstood how to apply the solution provided to earljgray, if you have another hint, would be appreciated.

select *
from admissions_application
inner join 
(
select to_char(sysdate,'Dy, Mon dd, yyyy') today
      ,case when to_char(sysdate,'Dy') = 'Mon' then to_char(sysdate-3,'Dy, Mon dd, yyyy')
            else to_char(sysdate-1,'Dy, Mon dd, yyyy') end Execution_Start
      ,to_char(sysdate-1,'Dy, Mon dd, yyyy') Execution_End
 from dual
 )
 on to_char(application_date,'Dy, Mon dd, yyyy') between execution_start and execution_end;

 

[SantaMufasa]

Remember, Sxscheck, that the result of a CASE statement is a non-boolean expression. Your original code attempts to produce boolean results.

Also, as you discovered, your need is different from EarlJGray's Oracle 11 forum question (thread1662-1653636). Your second solution looks like it will work, but I also believe that it is more complex that it needs to be. Unless I am missing something, I think the following will work for you more simply than a CASE- or a DECODE-based solution:

SELECT a.*
FROM admissions_application a
WHERE ((to_char(sysdate,'D') = '2' and application_date >= sysdate - 3)
        or
       (sysdate - 1 = application_date)
      )
  AND academic_period = '201140'
  AND...

Let us know your thoughts about this.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sxschech]

Hi SantaMufasa,

Tried your revision. When I run as is, I get 22 rows, however, the query using the INNER JOIN produced 41 rows. I copied part of your where clause and ran it by itself, (it is commented out below), and that produced 41 rows, so that part of the syntax is ok.

SELECT a.*
FROM admissions_application a
--where  (trunc(sysdate) - 1 = trunc(application_date))
WHERE ((to_char(sysdate,'D') = '2' and trunc(application_date) >= trunc(sysdate) - 3)
        or
       (trunc(sysdate) - 1 = trunc(application_date))
      )
  AND academic_period = '201140';

 

[carp]

sxchech -
I guess I should have been clearer in my response. While you and EarlJGray are making the computations in different parts of your queries, your computations are identical and therefore can use the same logic. I would suggest you take the excellent CASE statement that Santa provides in EarlJGray's thread and incorporate it into your WHERE clause or use it in an inner join (as in your original post). There was nothing wrong with your approach other than you did not provide a comparison value for the results of the CASE statement.

 

[Beilstwh]

Shouldn't it be

SELECT a.*
FROM admissions_application a
WHERE ((to_char(sysdate,'DY') = 'MON' and trunc(application_date) >= trunc(sysdate) - 3)
or (to_char(sysdate,'DY') <> 'MON' and trunc(sysdate) - 1 = trunc(application_date))
)
AND academic_period = '201140';

Bill
Lead Application Developer
New York State, USA

 

[sxschech]

Hi Bill,

Thank you for the revision to the where clause that worked. Would have responded sooner, but wanted to try it on a Monday as well as the day after Monday.

Not sure how to apply the star(s) in this case; to Beilstwh who came up with the final working syntax, or also to santamufasa and carp who got me most of the way there? Thanks to all who helped get this working.