How to find last 7 working/week days in Oracle SQL?

user_13020654 0
Viewed 161

I have a table with dates populated from last 10 years & next 20 years. I want to calculate if the date is in last 7 working (week) days with reference to today (sysdate) as shown in the sample table below (populate last column IsInLast7WorkingDays).

Assumption: Saturday & Sunday are non-working days.

TODAY       CAL_DATE  ISWEEKDAY   IsInLast7WorkingDays
8/12/2021   8/1/2021      N             N
8/12/2021   8/2/2021      Y             N
8/12/2021   8/3/2021      Y             Y
8/12/2021   8/4/2021      Y             Y
8/12/2021   8/5/2021      Y             Y
8/12/2021   8/6/2021      Y             Y
8/12/2021   8/7/2021      N             N
8/12/2021   8/8/2021      N             N
8/12/2021   8/9/2021      Y             Y
8/12/2021   8/10/2021     Y             Y
8/12/2021   8/11/2021     Y             Y
8/12/2021   8/12/2021     Y             N
8/12/2021   8/13/2021     Y             N
8/12/2021   8/14/2021     N             N
8/12/2021   8/15/2021     N             N
8/12/2021   8/16/2021     Y             N
8/12/2021   8/17/2021     Y             N
8/12/2021   8/18/2021     Y             N
8/12/2021   8/19/2021     Y             N
8/12/2021   8/20/2021     Y             N

TODAY       CAL_DATE  ISWEEKDAY   IsInLast7WorkingDays
8/14/2021   8/1/2021      N             N
8/14/2021   8/2/2021      Y             N
8/14/2021   8/3/2021      Y             N
8/14/2021   8/4/2021      Y             N
8/14/2021   8/5/2021      Y             Y
8/14/2021   8/6/2021      Y             Y
8/14/2021   8/7/2021      N             N
8/14/2021   8/8/2021      N             N
8/14/2021   8/9/2021      Y             Y
8/14/2021   8/10/2021     Y             Y
8/14/2021   8/11/2021     Y             Y
8/14/2021   8/12/2021     Y             Y
8/14/2021   8/13/2021     Y             Y
8/14/2021   8/14/2021     N             N
8/14/2021   8/15/2021     N             N
8/14/2021   8/16/2021     Y             N
8/14/2021   8/17/2021     Y             N
8/14/2021   8/18/2021     Y             N
8/14/2021   8/19/2021     Y             N
8/14/2021   8/20/2021     Y             N

Thanks in advance.

Edit: I guess I should have mentioned that I already have a table populated with dates and "isweekday". Today is derived from sysdate in the below query.

select b.today, a.cal_date, a.isweekday
from DATE_SRK a, (select trunc(sysdate)+4 today from dual) b
order by a.cal_date;
plsql oracle sql
3 Answers

If I understand correctly, you can use row_number() and case logic:

select t.*,
       (case when isweekday = 'N' then 'N'
             when cal_date > today then 'N'
             when row_number() over (partition by isweekday, (case when cal_date > today then 1 else 0 end)
                                     order by cal_date desc
                                    ) <= 7
             then 'Y'
             else 'N'
        end) as IsInLast7WorkingDays
from t;

The trick with row_number() is to only enumerate the rows that are 'Y' and less than or equal to today (the first two case conditions set the other rows to 'N').

user_11440350

Problem is the abstraction of "workingDay" what about holidays?

I dont know of any way to get holidays in a database you would need a predefined table w/ those.

if you limited working day to is "Mon to Fri" then you could use recursive SQL to generate a table expression to get them.

user_115794830

Something like this expression might work in a query:

SELECT T_Dates.*, 
IIf(Weekday([T_Dates]![TODAY],2)<6 
And 
IIf([T_Dates]![TODAY]>=DateAdd("d",-9,Now()),1,0)=1,1,0) 
AS [Weekday & Last 7 Days]
FROM T_Dates;

In this example, T_Dates is a table with the data you showed earlier. It will return 1 if the [TODAY] is a weekday within the last 9 days from the date of query run, 0 otherwise.

user_164026360
Related