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?

Example:

CREATE TABLE tblExcludeDates ( 
   ExceptionDate DATE, 
   Type  INTEGER );

INSERT tblExcludeDates VALUES ('2019-04-24', 1);
INSERT tblExcludeDates VALUES ('2019-04-25', 1);

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.

asked 13 Nov '19, 12:36

Rolle's gravatar image

Rolle
516364354
accept rate: 0%


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.

Remark:
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.

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 
permanent link

answered 14 Nov '19, 03:26

Frank's gravatar image

Frank
314111323
accept rate: 30%

edited 25 Nov '19, 15:36

Breck%20Carter's gravatar image

Breck Carter
29.5k488656962

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?

(25 Nov '19, 15:27) Rolle
Replies hidden
Comment Text Removed

What version of SQL Anywhere are you using? SQL Anywhere has a table called rowgenerator that contains a column named row_num.

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

(25 Nov '19, 15:34) Frank
1

I added a pre tag and changed rownum to row_num.

(25 Nov '19, 15:38) Breck Carter

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?

(30 Dec '19, 16:29) Rolle
1

Then you could create a stored function to do the trick:

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

(If you like the answer please give me some likes)

(31 Dec '19, 02:49) Frank

Thanks! This worked. Do you think this can be achieved via SQL as well and not with a function?

(01 Jan, 13:25) Rolle
Replies hidden

. Do you think this can be achieved via SQL as well and not with a function?

What do you mean by that? This is a SQL user-defined function that you can used within any SQL query, such as

select CalculateWorkginDays('2019-04-01', '2019-04-30');
(01 Jan, 16:29) Volker Barth

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.

(02 Jan, 14:10) Rolle
2

It is possible but it doesn't make it any clearer

(03 Jan, 02:44) Frank
More comments hidden
showing 4 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×3

question asked: 13 Nov '19, 12:36

question was seen: 169 times

last updated: 03 Jan, 02:44