Calulating date after specific number of workdays
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)
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?
Create Table "TEST_TBL" AS (
(SELECT ts::date AS the_date
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
-------------- | ------------------------------ |