Calulating date after specific number of workdays
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
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Answers
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
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
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.
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.,
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.
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.
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.