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>enWed, 18 Mar 2020 04:05:25 -0400Comment by Volker Barth on Frank's questionhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35072<p>Frank, that's code sharing at its best:)</p>Volker BarthWed, 18 Mar 2020 04:05:25 -0400https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35072Comment by Frank on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35071<pre>Change
if in_DateFrom > in_DateUntil then
into
if in_DateFrom >= in_DateUntil then
</pre>FrankWed, 18 Mar 2020 03:32:50 -0400https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35071Comment by Rolle on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35064<p>One question, I would like to get the difference up to the date.
For example, this will be 1, but I want it to be 0</p>
<p>CalculateWorkginDays('2019-04-02', '2019-04-02');</p>RolleTue, 17 Mar 2020 09:15:24 -0400https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35064Comment by Rolle on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35063<p>Ok, thanks for the answer. This works perfectly.
sa_rowgenerator(1, 1000)</p>RolleMon, 16 Mar 2020 15:52:34 -0400https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35063Comment by Frank on Rolle's questionhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35060<p>I think the rowgenerator is causing your problem because it only contains 255 records.
I think it is a SQL Anywhere table that you can extend with new values by adding the values you're missing</p>FrankMon, 16 Mar 2020 08:58:53 -0400https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35060Comment by Rolle on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35059<p>Something strange in this function that I don't know what it is.
If I specify an interval that extends over a year, I get too few days in the result.
In tblExcludeDates I only have one date (one row) for the year 2022. If I run the function for the year 2020 I get a diff of 182 days. It should be much more? If we count off weekends in 2022, it should be about 102 days. What could be wrong?</p>RolleMon, 16 Mar 2020 08:00:24 -0400https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#35059Comment by Frank on Rolle's questionhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34622<p>It is possible but it doesn't make it any clearer</p>FrankFri, 03 Jan 2020 02:44:13 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34622Comment by Rolle on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34620<p>Yes, I know it's a function. But instead of creating a function and calling it the one in my SQL, I wondered if this can be done directly in my sql.</p>RolleThu, 02 Jan 2020 14:10:07 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34620Comment by Volker Barth on Rolle's questionhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34617<blockquote>
<p>. Do you think this can be achieved via SQL as well and not with a function?</p>
</blockquote>
<p>What do you mean by that? This is a SQL user-defined function that you can used within any SQL query, such as</p>
<pre>select CalculateWorkginDays('2019-04-01', '2019-04-30');</pre>Volker BarthWed, 01 Jan 2020 16:29:36 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34617Comment by Rolle on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34616<p>Thanks! This worked.
Do you think this can be achieved via SQL as well and not with a function?</p>RolleWed, 01 Jan 2020 13:25:00 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34616Comment by Frank on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34615<p>Then you could create a stored function to do the trick:</p>
<pre>create or replace function CalculateWorkginDays(in in_DateFrom date,in in_DateUntil date)
returns integer
begin
declare l_AdjustDays integer=1;
declare l_DateFrom date;
declare l_DateUntil date;
declare l_Result integer;
//
if not exists(select 1 from systable where table_name = 'tblExcludeDates') then
create table tblExcludeDates (
ExceptionDate date,
Type integer);
end if;
//
if in_DateFrom > in_DateUntil then
set l_AdjustDays = -1;
set l_DateFrom = in_DateUntil;
set l_DateUntil = in_DateFrom;
else
set l_DateFrom = in_DateFrom;
set l_DateUntil = in_DateUntil;
end if;
//
select count()
into l_Result
from rowgenerator
where
// dates in the given period
(l_DateFrom + row_num - 1) between l_DateFrom and l_DateUntil
// only weekdays
and DatePart(Caldayofweek,l_DateFrom + row_num - 1) <= 5
// skip holidays
and not exists(select 1 from tblExcludeDates where ExceptionDate = (l_DateFrom + row_num - 1));
//
return l_Result * l_AdjustDays;
end
</pre>
<p>(If you like the answer please give me some likes)</p>FrankTue, 31 Dec 2019 02:49:16 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34615Comment by Rolle on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34614<p>An additional question. Count () in this case returns the number of rows. I need to know the difference even if it is negative, which does not work with this solution. Is there any smart solution to do that?</p>RolleMon, 30 Dec 2019 16:29:50 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34614Comment by Breck Carter on Frank's questionhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34462<p>I added a pre tag and changed rownum to row_num.</p>Breck CarterMon, 25 Nov 2019 15:38:20 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34462Comment by Frank on Rolle's questionhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34461<p>What version of SQL Anywhere are you using?
SQL Anywhere has a table called rowgenerator that contains a column named row_num. </p>
<p>You should replace rownum with an underscore between row and num. HTML removes the 8underscore in my example.
With every second underscore used the text is made cursive between the two underscores</p>FrankMon, 25 Nov 2019 15:34:07 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34461Comment by Rolle on Frank's answerhttps://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34459<p>Thanks for your reply.
I don't get it to work with your example. Don't quite understand how you mean with rownum and why it should be used?</p>RolleMon, 25 Nov 2019 15:27:28 -0500https://sqlanywhere-forum.sap.com/questions/34404/calculating-the-number-of-work-days-between-two-dates-excluding-weekends-and-holidays#34459Answer 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