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;***
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;