I need to perform calculations using a calendar table, whose specifications I present below:

1. Add or Subtract N workdays, excluding weekends and holydays.
2. Get the last workday from previous month, excluding weekends and holydays, from a given date.

• Columns explanation:

ref_date : days of the year - (the date we need to calc...)

civil_util : '0' -> holydays and weekends --- '1' are workdays

target_util : '0' -> weekends --- '1' are workdays

ano : correspondent year.

prev_wkday : previous ref_date, using Lag() function

next_wkday : next ref_date, using Lead() function.

• SQL that generates the example below:

  select *, LAG (to_date(ref_date),1) OVER (ORDER BY to_date(ref_date)) AS prev_wkday, Lead (to_date(ref_date),1) OVER (ORDER BY to_date(ref_date)) AS next_wkday

  from cd_estruturais.calendario_datas

  where ano = 2022 and ref_date between '2022-11-30' and date_add('2022-11-30',5) --and civil_util = 1 --limit 1

I need to answer both questions 1) and 2), using SQL Impala/Oracle. Regarding question 1), to get the next (1) workday from '2022-11-30', we could add in the above query, the both criteria (civil_util = 1 with limit 1), because civil_util = 1 selects only workdays, excluding weekends and holydays. The answer is '2022-12-02'.

I need the most efficient sql to calc the (n) workdays after and before '2022-11-30'.

Regarding question 2), to get the last workday from previous month from '2022-11-30', we must get the ref_date '2022-10-31'. this was the last workday of previous month.

Can anyone help please?

CodePudding user response：

Try

with calendar(ref_date, civil_util, target_util) as (
    select to_date('2022-11-30','yyyy-mm-dd'), 1, 1 from dual union all
    select to_date('2022-12-01','yyyy-mm-dd'), 0, 1 from dual union all 
    select to_date('2022-12-02','yyyy-mm-dd'), 1, 1 from dual union all 
    select to_date('2022-12-03','yyyy-mm-dd'), 0, 0 from dual union all 
    select to_date('2022-12-04','yyyy-mm-dd'), 0, 0 from dual union all 
    select to_date('2022-12-05','yyyy-mm-dd'), 1, 1 from dual -- union all 
),
newcalendar(ref_date, civil_util, target_util, workday, workday_rnk, last_wd_month) as ( 
    select ref_date, civil_util, target_util, workday, workday_rnk, 
        last_value(last_wd_month) ignore nulls over(partition by trunc(ref_date,'MM')) as last_wd_month
    from (
        select ref_date, civil_util, target_util, workday, /*nvl2(workday,workday_rnk,null) as*/ workday_rnk,
            max(nvl2(workday,ref_date,null)) over(partition by trunc(ref_date,'MM'), workday) as last_wd_month
        from (
            select c.*, sum(workday) over(order by ref_date) as workday_rnk
            from (
                select c.*, case when civil_util target_util>0 then 1 end as workday 
                from calendar c
            ) c
        )
    )
)
select * from newcalendar
order by ref_date
;

For the point 1, you just add/substract from workday_rnk, and the ref_date of the corresponding row is your answer. Note that this version with the "nvl2(workday,workday_rnk,null)" commented out works also if the day is an off day, if you don't want that just remove the comment.

For the point 2, all rows have "last_wd_month" set, so it's just a matter of where clause on it (with trunc(last_wd_month,'MM') = target).

REF_DATE CIVIL_UTIL TARGET_UTIL    WORKDAY WORKDAY_RNK LAST_WD_
-------- ---------- ----------- ---------- ----------- --------
30/11/22          1           1          1           1 30/11/22
01/12/22          0           1          1           2 05/12/22
02/12/22          1           1          1           3 05/12/22
03/12/22          0           0                      3 05/12/22
04/12/22          0           0                      3 05/12/22
05/12/22          1           1          1           4 05/12/22