create table tickets
(
ticket_id varchar(10),
create_date date,
resolved_date date
);
delete from tickets;
insert into tickets values
(1,'2022-08-01','2022-08-03')
,(2,'2022-08-01','2022-08-12')
,(3,'2022-08-01','2022-08-16');
create table holidays
(
holiday_date date
,reason varchar(100)
);
delete from holidays;
insert into holidays values
('2022-08-11','Rakhi'),('2022-08-15','Independence day');

select t.*,count(h.holiday_date) as hldys,
datediff(resolved_date,create_date) as days
,week(resolved_date)
    from tickets t
Left join holidays h on h.holiday_date between t.create_date and t.resolved_date
    group by t.ticket_id,t.create_date,t.resolved_date, datediff(create_date,resolved_date);