Answers to: Calculating the Number of Work Days between two dates excluding weekends and holidayshttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays<p>How do I do the smartest way to calculate the number of days between two dates and exclude weekends and holidays. With datepart I can exclude weekends, but holidays are stored in a table. How do I best exclude these dates? Does it work with dateddiff? </p>
<p>Example:</p>
<div><pre><span></span>CREATE TABLE tblExcludeDates (
ExceptionDate DATE,
Type INTEGER );
INSERT tblExcludeDates VALUES ('2019-04-24', 1);
INSERT tblExcludeDates VALUES ('2019-04-25', 1);
</pre></div>
<p>Between the dates 2019-04-01 and 2019-04-30 the result should be 20 days if it excludes weekends and holidays from above table.</p>enThu, 14 Nov 2019 03:26:59 -0500Answer by Frankhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays/34407<p>I created an example that can be run in I-Sql. Maybe you need to create a stored procedure/function based on the example where the inDateFrom and inDateUntil will be the parameters. </p>
<p><strike>Remark:<br>
rownum is a column generated by the rowgenerator and should be with a underscore between row and num. You need to change that to make it work.</strike></p>
<pre>CREATE TABLE tblExcludeDates (
ExceptionDate DATE,
Type INTEGER );
INSERT tblExcludeDates VALUES ('2019-04-24', 1);
INSERT tblExcludeDates VALUES ('2019-04-25', 1);
create variable inDateFrom date;
create variable inDateUntil date;
set inDateFrom = '2019-04-01';
set inDateUntil = '2019-04-30';
select count()
from rowgenerator
where
// dates in the given period
(inDateFrom + row_num - 1) between inDateFrom and inDateUntil
// only weekdays
and DatePart(Caldayofweek,inDateFrom + row_num - 1) <= 5
// skip holidays
and not exists(select 1 from tblExcludeDates where ExceptionDate = (inDateFrom + row_num - 1))
count()
-----------
20
</pre>FrankThu, 14 Nov 2019 03:26:59 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays/34407