|
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. |
|
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.
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
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 Vestjens
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 Vestjens
Thanks! This worked. Do you think this can be achieved via SQL as well and not with a function?
(01 Jan '20, 13:25)
Rolle
Replies hidden
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 '20, 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 '20, 14:10)
Rolle
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 '20, 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 '20, 08:58)
Frank Vestjens
Ok, thanks for the answer. This works perfectly. sa_rowgenerator(1, 1000)
(16 Mar '20, 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 '20, 09:15)
Rolle
Change
if in_DateFrom > in_DateUntil then
into
if in_DateFrom >= in_DateUntil then
(18 Mar '20, 03:32)
Frank Vestjens
Frank, that's code sharing at its best:)
(18 Mar '20, 04:05)
Volker Barth
More comments hidden
|
