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
Holidays Table
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;
Advertisement
Answer
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
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