How to count days between 2 dates except holiday and weekend?

Question

I started a HR management project and I want to count days between 2 dates without counting the holidays and weekends. So the HR can count employee's day off

Here's the case, I want to count between 2018-02-14 and 2018-02-20 where there is an office holiday on 2018-02-16. The result should be 3 days.

I have already created a table called tbl_holiday where I put all weekends and holidays in one year there

I found this post, and I tried it on my MariaDB

Here's my query:

SELECT 5 * (DATEDIFF('2018-02-20', '2018-02-14') DIV 7) + 
MID('0123444401233334012222340111123400012345001234550', 7 * 
WEEKDAY('2018-02-14') + WEEKDAY('2018-02-20') + 1, 1) - 
(SELECT COUNT(dates) FROM tbl_holiday WHERE dates NOT IN (SELECT dates FROM tbl_holiday)) as Days

The query works but the result is 4 days, not 3 days. It means the query only exclude the weekends but not the holiday

What is wrong with my query? Am I missing something? Thank you for helping me

The logic for this sort of query is far better done with a date table, that has a pre-prepared sequential number (a workday number) that progresses only on working days. Then, to get the number of working days between, you simply select the two dates of interest, and subtract the workday number of one from the other. — Steve, Feb 08 '18 at 10:01
Or a table with 365 dates and a `type` field where you can mark some days as holidays and some as weekends. Maybe it would be more convenient if the logic changes in the future. — akarilimano, Feb 08 '18 at 10:15
@Steve so what you meant is I create a new table, then I'll just input ALL working days? — Richard Doe, Feb 08 '18 at 11:50

score 0 · Answer 1 · answered Feb 08 '18 at 14:09

@RichardDoe, from the question comments.

In a reasonable implementation of a date table, you create a list of all days (covering a sufficient range to cope with any query you may run against it - 15 years each way from today is probably a useful minimum), and alongside each day you store a variety of derived attributes.

I wrote a Q&A recently with basic tools that would get you started in SQL Server: https://stackoverflow.com/a/48611348/9129668

Unfortunately I don't have a MySQL environment or intimate familiarity with it to allow me to write or adapt queries off the top of my head (as I'm doing here), but I hope this will illustrate the structure of a solution for you in SQL Server syntax.

In terms of the answer I link to (which generates a date table on the fly) and extending it by adding in your holiday table (and making some inferences about how you've defined your holiday table), and noting that a working day is any day Mon-Fri that isn't a holiday, you'd write a query like so to get the number of working days between any two dates:

WITH 
dynamic_date_table AS
(
    SELECT *
    FROM generate_series_datetime2('2000-01-01','2030-12-31',1)
    CROSS APPLY datetime2_params_fxn(datetime2_value)
)
,date_table_ext1 AS
(
    SELECT
        ddt.*
        ,IIF(hol.dates IS NOT NULL, 1, 0) AS is_company_holiday

    FROM 
        dynamic_date_table AS ddt

    LEFT JOIN 
        tbl_holiday AS hol
        ON (hol.dates = ddt.datetime2_value)
)
,date_table_ext2 AS
(
    SELECT
        *
        ,IIF(is_weekend = 1 OR is_company_holiday = 1, 0, 1) AS is_company_work_day
    FROM date_table_ext1
)

SELECT 
    COUNT(datetime2_value)
FROM 
    date_table_ext2
WHERE 
    (datetime2_value BETWEEN '2018-02-14' AND '2018-02-20')
    AND
    (is_company_work_day = 1)

Obviously, the idea for a well-factored solution is that these intermediate calculations (being general in nature to the entire company) get rolled into the date_params_fxn, so that any query run against the database gains access to the pre-defined list of company workdays. Queries that are run against it then start to resemble plain English (rather than the approach you linked to and adapted in your question, which is ingenious but far from clear).

If you want top performance (which will be relevant if you are hitting these calculations heavily) then you define appropriate parameters, save the lot into a stored date table, and index that table appropriately. This way, your query would become as simple as the final part of the query here, but referencing the stored date table instead of the with-block.

The sequentially-numbered workdays I referred to in my comment on your question, are another step again for the efficiency and indexability of certain types of queries against a date table, but I won't complicate this answer any further for now. If any further clarification is required, please feel free to ask.

score 0 · Accepted Answer · answered Feb 08 '18 at 22:59

0

I found the answer for this problem

It turns out, I just need to use a simple arithmetic operator for this problem

SELECT (SELECT DATEDIFF('2018-02-20', '2018-02-14')) - (SELECT COUNT(id) FROM tbl_holiday WHERE dates BETWEEN '2018-02-14' AND '2018-02-20');

answered Feb 08 '18 at 22:59

Richard Doe

79
3
14

If you've gone as far as defining a table containing all weekends, you might as well go the whole hog as I suggested haha! That is, define a table in which all dates are either flagged as working days or non-working days. That will then eliminate the need for the `DATEDIFF` and the subquery. – Steve Feb 10 '18 at 16:06
Your suggestion is very good, @Steve and my co-worker also said the same thing like you. However, I don't want to burden my HR staffs, they are understaffed and inserting weekends and holidays are much easier for them rather than putting workdays for 5 to 15 years upfront. – Richard Doe Feb 11 '18 at 12:01
My thinking is that staff wouldn't maintain the 'weekend' logic - since that can be determined algorithmically, unless you're saying that the definition of the weekend may vary (and is not necessarily cyclic). If it varies between staff (e.g. some take a "weekend" on Tuesday and Wednesday), you wouldn't define all those days individually (unless they are completely ad-hoc and irrational), you'd define a lookup against the main date table (e.g. if their weekend is days 2 and 3, then you'd store 2 and 3 for that staff, and join that back onto the main date table in order to eliminate those days) – Steve Feb 11 '18 at 13:25

How to count days between 2 dates except holiday and weekend?

2 Answers2