tid INTEGER NOT NULL PRIMARY KEY

,created_At VARCHAR(23) NOT NULL

,apply_sla BIT NOT NULL

,ticket_closed_date VARCHAR(25) NOT NULL

,days_to_complete INTEGER NOT NULL

);

INSERT INTO tickets(tid,created_At,apply_sla,ticket_closed_date,days_to_complete) VALUES (100,'2020-10-02 00:00:00',1,'2020-10-09 00:00:00',3);

id INTEGER NOT NULL PRIMARY KEY

,holiday_date DATE NOT NULL

,end_date DATE NOT NULL

);

INSERT INTO holidays(id,holiday_date,end_date) VALUES (20,'2020-10-05','2020-10-05');

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