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
548424459
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
389111428
accept rate: 23%

edited 25 Nov '19, 15:36

Breck%20Carter's gravatar image

Breck Carter
30.0k490670975

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
2

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

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?

(16 Mar, 08:00) Rolle

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

(16 Mar, 08:58) Frank

Ok, thanks for the answer. This works perfectly. sa_rowgenerator(1, 1000)

(16 Mar, 15:52) Rolle

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

CalculateWorkginDays('2019-04-02', '2019-04-02');

(17 Mar, 09:15) Rolle
Change 
    if in_DateFrom > in_DateUntil then 
into 
    if in_DateFrom >= in_DateUntil then
(18 Mar, 03:32) Frank

Frank, that's code sharing at its best:)

(18 Mar, 04:05) Volker Barth
More comments hidden
showing 4 of 15 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: 331 times

last updated: 18 Mar, 04:05