I'm facing a challenging task here, spent a day on it and I was only able to solve it through a procedure but it is taking too long to run for all projects.
I would like to solve it in a single query if possible (no functions or procedures).
There is already some questions here doing it in programming languages OR SQL functions/procedures (Which I also solved min). So I'm asking if it is possible to solve it with just SQL
The background info is:
project tablephase tableholiday tabledayexception table which cancel a holiday or a weekend day (make that date as a working day) and it is associated with a projectstart date, a duration and a draworder (needed by the system)dayexception table)Consider this following scenario:
project | phase(s) | Dayexception | Holiday
id | id pid start duration draworder | pid date | date
1 | 1 1 2014-01-20 10 0 | 1 2014-01-25 | 2014-01-25
| 2 1 2014-02-17 14 2 | |
The ENDDATE for the project id 1 and phase id 1 is actually 2014-01-31 see the generated data below:
The date on the below data (and now on) is formatted as dd/mm/yyyy (Brazil format) and the value N is null
proj pha start day weekday dayexcp holiday workday
1 1 20/01/2014 20/01/2014 2 N N 1
1 1 20/01/2014 21/01/2014 3 N N 1
1 1 20/01/2014 22/01/2014 4 N N 1
1 1 20/01/2014 23/01/2014 5 N N 1
1 1 20/01/2014 24/01/2014 6 N N 1
1 1 20/01/2014 25/01/2014 7 25/01/2014 25/01/2014 1
1 1 20/01/2014 26/01/2014 1 N N 0
1 1 20/01/2014 27/01/2014 2 N 27/01/2014 0
1 1 20/01/2014 28/01/2014 3 N N 1
1 1 20/01/2014 29/01/2014 4 N N 1
To generate the above data I created a view daysOfYear with all days from 2014 and 2015 (it can be bigger or smaller, created it with two years for the year turn cases) with a CTE query if you guys want to see it let me know and I will add it here. And the following select statement:
select ph.project_id proj,
ph.id phase_id pha,
ph.start,
dy.curday day,
dy.weekday, /*weekday here is a calling to the weekday function of db2*/
doe.exceptiondate dayexcp,
h.date holiday,
case when exceptiondate is not null or (weekday not in (1,7) and h.date is null)
then 1 else 0 end as workday
from phase ph
inner join daysofyear dy
on (year(ph.start) = dy.year)
left join dayexception doe
on (ph.project_id = doe.project_id
and dy.curday = truncate(doe.exceptiondate))
left join holiday h
on (dy.curday = truncate(h.date))
where ph.project_id = 1
and ph.id = 1
and dy.year in (year(ph.start),year(ph.start)+1)
and dy.curday>=ph.start
and dy.curday<=ph.start + ((duration - 1) days)
order by ph.project_id, start, dy.curday, draworder
To solve this scenario I created the following query:
select project_id,
min(start),
max(day) + sum(case when workday=0 then 1 else 0 end) days as enddate
from project_phase_days /*(view to the above select)*/
This will return correctly:
proj start enddate
1 20/01/2014 31/01/2014
The problem I couldn't solve is if the days I'm adding (non workdays sum(case when workday=0 then 1 else 0 end) days ) to the last enddate (max(day)) is weekend days or holidays or exceptions.
See the following scenario (The duration for the below phase is 7):
proj pha start day weekday dayexcp holiday workday
81 578 14/04/2014 14/04/2014 2 N N 1
81 578 14/04/2014 15/04/2014 3 N N 1
81 578 14/04/2014 16/04/2014 4 N N 1
81 578 14/04/2014 17/04/2014 5 N N 1
81 578 14/04/2014 18/04/2014 6 N 18/04/2014 0
81 578 14/04/2014 19/04/2014 7 N 0
81 578 14/04/2014 20/04/2014 1 N 20/04/2014 0
/*the below data I added to show the problem*/
81 578 14/04/2014 21/04/2014 2 N 21/04/2014 0
81 578 14/04/2014 22/04/2014 3 N 1
81 578 14/04/2014 23/04/2014 4 N 1
81 578 14/04/2014 24/04/2014 5 N 1
With the above data my query will return
proj start enddate
81 14/04/2014 23/04/2014
But the correct result would be the enddate as 24/04/2014 that's because my query doesn't take into account if the days after the last day is weekend days or holidays (or exceptions for that matter) as you can see in the dataset above the day 21/04/2014 which is outside my duration is also a Holiday.
I also tried to create a CTE on phase table to add a day for each iteration until the duration is over but I couldn't add the exceptions nor the holidays because the DB2 won't let me add a left join on the CTE recursion. Like this:
with CTE (projectid, start, enddate, duration, level) as (
select projectid, start, start as enddate, duration, 1
from phase
where project_id=1
and phase_id=1
UNION ALL
select projectid, start, enddate + (level days), duration,
case when isWorkDay(enddate + (level days)) then level+1 else level end as level
from CTE left join dayexception on ...
left join holiday on ...
where level < duration
) select * from CTE
PS: the above query doesn't work because of the DB2 limitations and isWorkDay is just as example (it would be a case on the dayexception and holiday table values).
If you have any doubts, please just ask in the comments. Any help would be greatly appreciated. Thanks.
How to count business days forward and backwards.
Background last Century I worked at this company that used this technique. So this is a pseudo code answer. It worked great for their purposes.
What you need is a table that contains a date column and and id column that increments by one. Fill the table with only business dates... That's the tricky part because of the observing date on another date. Like 2017-01-02 was a holiday where I work but its not really a recognized holiday AFAIK.
How to get 200 business days in the future.
How to get 200 business days in the past.
Business days between.
select count(*) from myBusinessDays where "date" between startdate and enddate
Good Luck as this is pseudo code.