如何在 MySQL 中排除周末和节假日日期并查找预期日期？

我正在执行一项有两张桌子的任务。 IE，tickets and holidays.

现在我也有完成票的天数了。现在我需要通过排除假期（在holidays表）和weekends.

现在我可以使用查找日期ticket created date and days to complete the ticket。但无法通过除去节假日和周末来计算预计预产期。

如果机票预计到期日恰逢节假日或周末，我们需要将预计到期日提前。

之后我们需要比较ticket_closed_date and expected_due_date.

If ticket_closed_date <= expected_due_date然后需要返回isSlaMet as YES。否则需要返回isSlaMet as NO.

门票表

假期表

Example:一般来说，如果工单创建于2nd October,2020完成的天数是3，那么预计的到期日是，5th October我们有一个假期，5th October。但工单创建日期和预计截止日期之间有 1 个假期和 2 个周末。 IE，3rd, 4th and 5th of October 。所以我们需要将预计到期日延长3 days（因为 2 个周末 + 1 个假期）。 IE，8th October。门票截止日期为9th October.

然后我们需要比较ticket closed date(9th october) and expected due date(8th October)并返回isSlaMet as YES.

预期输入

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.

预期输出

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
  
--------------------------------------------------------------------------------------------------------------------------

这是我到目前为止一直在使用的查询。

    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;

这可能看起来更复杂。最简单的方法可能是暴力破解：使用递归 CTE（仅在 MySQL 8.0 中可用）枚举票证创建日期和关闭日期之间的所有日期，然后过滤掉周末和节假日来计算 SLA 天数：

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