How to get previous working date using trunc(sysdate) in oracle-CodePudding

Greetings for the day!

i have written a python script that will run a select query using oracle database and will share the result with users based on the result it gets from that query. My aim is to run it through task scheduler on which it should automatically adjust the date mentioned in sql query and should always pick the last business day means if its monday, it should run the query with asof day as friday, if tuesday then asod day as Monday and so on.

Note : the report in the query runs on T 1 basis means if asof date is 21st Apr 2022 means it's actual start time would be 22 Apr 2022, so when it will run on 25th Apr (Monday) the asof date would be 22nd Apr

select* from snap_states
where asof = trunc(sysdate)-1
  and upper(system) like ('LOANSL%')
order by start_time;***

CodePudding user response：

If you're skipping weekends, then you could

SQL> with datum (sys_date) as
  2    (select date '2022-04-23' from dual union all -- Saturday
  3     select date '2022-04-24' from dual union all -- Sunday
  4     select date '2022-04-25' from dual union all -- Monday
  5     select date '2022-04-26' from dual           -- Tuesday
  6    )
  7  select to_char(sys_date, 'dd.mm.yyyy, Dy') sys_date,
  8         trunc(sys_date - case to_char(sys_date, 'Dy', 'nls_date_language = english')
  9                                 when 'Sun' then 2
 10                                 when 'Mon' then 3
 11                                 else 1
 12                          end) as prev_work_day
 13  from datum;

SYS_DATE                 PREV_WORK_
------------------------ ----------
23.04.2022, Sat          22.04.2022
24.04.2022, Sun          22.04.2022
25.04.2022, Mon          22.04.2022
26.04.2022, Tue          25.04.2022

SQL>

Applied to your query:

select * 
from snap_states
where asof = trunc(sysdate - case to_char(sysdate, 'Dy', 'nls_date_language = english') 
                                when 'Sun' then 2
                                when 'Mon' then 3
                                else 1
                             end)
  and upper(system) like 'LOANSL%'
order by start_time;