I am trying to create an SQL query in TOAD that retrieves the data from an Oracle DB. What it should do is retrieve all data from previous workday back to one week, but it should exclude Saturday and Sunday. So far I am created the query below. In this format it returns me a "Missing keyword" error message. TOAD highlights my first "Between" operator. I have the idea that I am nearly there. Who can give me the final push into the right direction.
Select orderkey, actualshipdate from orders Case to_char(sysdate, 'DAY') When 'MONDAY' then to_char(actualshipdate,'YYYYMMDD') between to_char(sysdate, 'YYYYMMDD') -11 and to_char(sysdate, 'YYYYMMDD')-3 else to_char(actualshipdate,'YYYYMMDD') between to_char(sysdate, 'YYYYMMDD') -8 and to_char(sysdate, 'YYYYMMDD')-1 end order by actualshipdate asc
Based on your description (and replacing the awful to_char of the dates):
select orderkey, actualshipdate from orders where 1=1 and actualshipdate between trunc(sysdate) - 8 and trunc(sysdate)-(1/86400) -- midnight last night minus 1 second and to_char(actualshipdate, 'D') not in (6,7) -- 6: Sat, 7: Sun