How to exclude weekends and holidays dates and find an expected date in MySQL?

user_11387549 0
Viewed 568

I am working on a task where I have 2 tables. i.e, tickets and holidays.

Now I also have the number of days to complete the tickets. Now I need to find the expected date by excluding holidays(specified in the holidays table) and weekends.

Now I can able to find a date using ticket created date and days to complete the ticket. But unable to calculate the expected due date by removing holidays and weekends.

If ticket expected due date falls under holidays or weekends, we need to shift the expected due date ahead.

And after that we need to compare ticket_closed_date and expected_due_date.

If ticket_closed_date <= expected_due_date then need to return isSlaMet as YES. Else need to return isSlaMet as NO.

Tickets Table

enter image description here

Holidays Table

enter image description here

Example: Generally if a ticket is created on 2nd October,2020 and its days to complete is 3, then the expected due date would be, 5th October and we have a holiday, 5th October. But there are 1 holiday and 2 weekends in between ticket created date and expected due date. i.e, 3rd, 4th and 5th of October . So we need to extend the expected due date by 3 days(because 2 weekends + 1 Holiday). i.e, 8th October. Ticket is closed on 9th October.

Then we need to compare ticket closed date(9th october) and expected due date(8th October) and return isSlaMet as YES.

Expected Input

Tickets Table
--------------------------------------------------------------------------------
tid         createdAt         apply_sla    ticket_closed_date     days_to_complete
--------------------------------------------------------------------------------
100    2020-10-02 00:00:00        1       2020-10-09 00:00:00           3       
--------------------------------------------------------------------------------
       
Holidays Table
----------------------------------------------
id        holiday_date          end_date
----------------------------------------------
20         2020-10-05          2020-10-05
----------------------------------------------

Along with the above holiday, we need to exclude Weekends.

Expected Output

Tickets Table
--------------------------------------------------------------------------------------------------------------------------
tid       createdAt     apply_sla    ticket_closed_date   days_to_complete   expected_due_date    completedIn   isSlaMet
--------------------------------------------------------------------------------------------------------------------------
100  2020-10-02 00:00:00    1       2020-10-09 00:00:00             3        2020-10-08 00:00:00      4           NO
  
--------------------------------------------------------------------------------------------------------------------------

This is the query I have been using so far.

    SELECT 
        `t`.`tid`, `t`.`createdAt`, `t`.`days_to_complete`,
        `t`.`ticket_closed_date`,`holidays`.`holiday_date`,
        `holidays`.`end_date`, `t.apply_sla`,
        IF(ISNULL(`t`.`ticket_closed_date`),
            NULL,
            IF((`t`.`apply_sla` = 1),
                IF(((CAST(`t`.`createdAt` AS DATE) + INTERVAL (`t`.`days_to_complete` + 1) DAY) BETWEEN `holidays`.`holiday_date` AND `holidays`.`end_date`),
                    IF((CAST(`t`.`ticket_closed_date` AS DATE) <= (`holidays`.`end_date` + INTERVAL `t`.`days_to_complete` DAY)),
                        'YES',
                        'NO'),
                    IF((CAST(`t`.`ticket_closed_date` AS DATE) <= (`t`.`createdAt` + INTERVAL (`t`.`days_to_complete` + 1) DAY)),
                        'YES',
                        'NO')),
                IF(((TO_DAYS(`t`.`ticket_closed_date`) - TO_DAYS(`t`.`createdAt`)) > (`t`.`days_to_complete` + 1)),
                    'NO',
                    'YES')
            )
        ) AS `isSlaMet`
    FROM
        (`tickets` `t`
        LEFT JOIN `holidays` ON (((CAST(`t`.`createdAt` AS DATE) + INTERVAL (`t`.`days_to_complete` + 1) DAY) BETWEEN `holidays`.`holiday_date` AND `holidays`.`end_date`)))
    ORDER BY `t`.`tid` DESC;
recursive-query count datetime mysql sql
1 Answers

This is more complicated that it may seem. The simplest approach may be brute force: enumerate all days between the creation and closure date of the ticket with a recursive CTE (available in MySQL 8.0 only), then filter out the weekends and holidays to count the number of SLA days:

with recursive cte_tickets as (
    select tid, created_at as dt, ticket_closed_date
    from tickets 
    where apply_sla = 1
    union all
    select tid, dt + interval 1 day, ticket_closed_date
    from cte_tickets
    where dt < ticket_closed_date
)
select t.*, 
    t.created_at 
        + interval (t.days_to_complete + sum(weekday(dt) in (5, 6) or h.holiday_date is not null)) day
        as expected_due_date,    
    count(*) - sum(weekday(dt) in (5, 6) or h.holiday_date is not null) - 1 completed_in,
    t.ticket_closed_date <= t.created_at 
        + interval (t.days_to_complete + sum(weekday(dt) in (5, 6) or h.holiday_date is not null)) day
        as is_sla_met
from tickets t
inner join cte_tickets ct on ct.tid = t.tid
left join holidays h on ct.dt between h.holiday_date and h.end_date
group by t.tid

Demo on DB Fiddle:

tid | created_At          | apply_sla | ticket_closed_date  | days_to_complete | expected_due_date   | completed_in | is_sla_met
--: | :------------------ | :-------- | :------------------ | ---------------: | :------------------ | -----------: | ---------:
100 | 2020-10-02 00:00:00 | 1         | 2020-10-09 00:00:00 |                3 | 2020-10-08 00:00:00 |            4 |          0
user_106767160
Related