Calulating date after specific number of workdays

MateuszDudek

Hello,

I'm trying to calculate date after N working days (for now we ignore public holidays - it's only about excluding weekends). Unfortunately we're doing that in ETL part of Celonis Process Mining which uses Vertica SQL (10.1.x version) but without some specific analytic functions like LEAD/LAD which could solve my issue, I also cannot used any king of custom functions, so only simple SQL is viable here.

I've seen these links, however they cannot solve my problem:
A) https://forum.vertica.com/discussion/239801/counting-days-week-days-and-weekend-days-across-the-years (I want to calculate for every row in the table, not single value)
https://forum.vertica.com/discussion/239605/add-days-to-a-date-excluding-sat-and-sun

I've also tried generate list of days between min and max dates in the table, however I cannot find it useful without LEAD/LAG functions. Do you have some ideas?

CODE:
Create Table "TEST_TBL" AS (
SELECT the_date
FROM
(SELECT ts::date AS the_date
FROM
(
SELECT MIN(CPUDT)::TIMESTAMP AS tm FROM TABLE UNION --Get Min date from TABLE
SELECT MAX(CPUDT)::TIMESTAMP AS tm FROM TABLE --Get Max date from TABLE
) AS t TIMESERIES ts AS '1 DAY' OVER (ORDER BY tm)) foo --Fill the gapes by generating dates between MIN & MAX
WHERE DAYOFWEEK(the_date) NOT IN (1,7) --Do that only for working days

INPUT:

Some_Date

2022-11-28
2022-11-27

DESIRED OUTPUT
-------------- | ------------------------------ |

Some_Date	Some_Date+3 workDays
2022-11-28	2022-12-01
2022-11-25	2022-12-30

Best Regards,
Mateusz Dudek

Bryan_H

How about something like this (if you can't create function, you only need the SQL from the RETURN line):
CREATE OR REPLACE FUNCTION AddBusinessDays(ts TIMESTAMP, days INT) RETURN TIMESTAMP
AS BEGIN
RETURN TIMESTAMPADD('day', (days % 5)::int + CASE ((1 + DAYOFWEEK_ISO(ts) + (days % 5)) % 7) WHEN 0 THEN 2 WHEN 1 THEN 1 ELSE 0 END, TIMESTAMPADD('week', (days / 5)::int, ts));
END;
Adapted from T-SQL example at https://stackoverflow.com/questions/5471524/add-business-days-to-date-in-sql-without-loops

MateuszDudek

Hello Bryan_H,

Thanks a lot for that snippet - I've seen that thread, but as I just seen that's a function, I ignored that - probably a mistake

I've adjusted the code and added code for dummy data creation.
It works however outcome is a bit strange - I wanted to add 3 working days so I should receive:
2022-11-30
2022-11-30
2022-12-01

CODE:
DROP TABLE IF EXISTS "TEST_DATE_TBL_MD";

Create table "TEST_DATE_TBL_MD" AS (
SELECT '2022-11-28'::TIMESTAMP AS "SomeDate", 1 AS "DUMMY_KEY"
UNION
SELECT '2022-11-27'::TIMESTAMP AS "SomeDate", 1 AS "DUMMY_KEY"
UNION
SELECT '2022-11-26'::TIMESTAMP AS "SomeDate", 1 AS "DUMMY_KEY"
);

SELECT "SomeDate",
TIMESTAMPADD('day', (3 % 5)::int + CASE ((1 + DAYOFWEEK_ISO("SomeDate") + (3 % 5)) % 7)
WHEN 0 THEN 2
WHEN 1 THEN 1
ELSE 0 END, TIMESTAMPADD('week', (3 / 5)::int, "SomeDate")) AS "SomeDate+3WDs"
FROM "TEST_DATE_TBL_MD";

Output:

Best Regards,
Mateusz Dudek

marcothesane

To cater for holidays, too - and, by adding some sort of a geography/region key, for local holidays, create a calendar table, with a counter set to 1 in case the row is a workday, 0 otherwise.
For this example, I just create a calendar where Saturday and Sunday are non-working days, and limit to 3 months.

DROP TABLE IF EXISTS cal;

CREATE TABLE cal AS
WITH
cal AS (
  SELECT
    tm::DATE as dt
  , CASE DAYOFWEEK(dt)
      WHEN 1 THEN 0
      WHEN 7 THEN 0
      ELSE 1
    END AS wkdaycounter
  FROM (
               SELECT TIMESTAMP '2022-10-01'
     UNION ALL SELECT TIMESTAMP '2022-12-31'
  ) limits(ts)
  TIMESERIES tm AS '1 DAY' OVER(ORDER BY ts) 
);

Then, you can range-join your input data over the from-date, so that the calendar date is greater than the from-date - and limit to, say the double number of workdays to add. On that range-join, apply a running sum on the week day counter coming from the calendar. Pack this last query again as a subquery (a Common Table Expression in my case), and finally filter by the result of the running sum equal to the number of workdays you want. I left all intermediate columns in the report so you can see what happened.,

WITH
indata(dt, wkdays) AS (
             SELECT DATE '2022-11-26', 3
   UNION ALL SELECT DATE '2022-11-27', 3
   UNION ALL SELECT DATE '2022-11-28', 3
)  
,
wrkdayrunsum AS (
  SELECT
    indata.dt
  , indata.wkdays
  , cal.dt as after_n_wkdays
  , SUM(wkdaycounter) OVER(PARTITION BY indata.dt ORDER BY cal.dt) AS wdaycount
  FROM indata
  JOIN cal ON cal.dt > indata.dt AND cal.dt <= indata.dt + wkdays * 2
) 
SELECT
  *
FROM wrkdayrunsum
WHERE wdaycount = wkdays
;
-- out      dt     | wkdays | after_n_wkdays | wdaycount 
-- out ------------+--------+----------------+-----------
-- out  2022-11-27 |      3 | 2022-11-30     |         3
-- out  2022-11-26 |      3 | 2022-11-30     |         3
-- out  2022-11-28 |      3 | 2022-12-01     |         3

VValdar

I think you can modify this for this:
TIMESTAMPADD('week', (days / 5)::int, ts));
TIMESTAMPADD('week', days // 5, ts));

It's better but there's still a day that don't output like expected.

Kedis1999

1. Calculating Date After Specific Number of Workdays
   The formula for calculating the date after a specific number of workdays is:
   Date (Workday + 1) / 2
   For example: If you want to calculate the date after 5 days, then you would use the following formula:
   (5 + 1) / 2 4/2 2
   So the date after two weeks is February 6th.
   2. Calculating Date Before Specific Number of Workdays
      The formula for calculating date before a specific number of work days is:
      Date Workday - (Workday + 1)/2
      For example: If we wanted to calculate the date before five days, we would use the following formula.
      (5 - (5+1)) / 2 4/ 2 2
      So the day before two weeks is January 25th.