Solving a REAL Business Use Case Using SQL | Business Days Excluding Weekends and Public Holidays

In this video we are going to solve a very important business use case where we need to find difference between 2 dates excluding weekends and public holidays . Basically we need to find business days between 2 given dates using SQL.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:
script:
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');

#sql #business #days #analytics

[letsdomistakes]

Assignment Question: No of business Days if holidays are in weekends
Ans:
By just excluding holidays from weekends, we could calculate Business Day in better way.

SELECT ticket_id,
create_date,
resolved_date,
DATEDIFF(day, create_date, resolved_date)
- 2*DATEDIFF(week, create_date, resolved_date)
- COUNT(holiday_date) as Business_days
FROM tickets
LEFT JOIN holidays
ON holiday_date between create_date AND resolved_date
AND DATENAME(WEEKDAY, holiday_date) NOT IN ('Saturday', 'Sunday')
GROUP BY ticket_id, create_date, resolved_date;


Love you and Thank you sir, you're contribution.

[abb_raj]

My approach for additional assignment⬇:
with cte as (
select *
from tickets left join holidays on (holiday_date between create_date and resolved_date) and
(dayname(holiday_date) <> 'Saturday' and dayname(holiday_date) <> 'Sunday')
)
select ticket_id, create_date, resolved_date,
datediff(resolved_date, create_date) - - count(holiday_date) actual_biz_days
from cte
group by ticket_id, create_date, resolved_date;

[TheVaibhavdang]

I tried this approach for your assignment question
select *, case when datepart(WEEKDAY, holiday_date) in (6, 7) then 1 else 0 end as weekdayandpublicholiday
from holidays

[avi]

Count(holiday_days) part was bit tricky and you explained it very well bhai. Thankyou 🙏

[salmeelezovski]

YOU ARE AMAZING DUDE! SAVED ME FROM HOURS AND HOURS OF AL THE BEST

[gagansingh]

In power bi we can easily exclude weekends and by using networkdays we can we can exclude weekends & holidays between the business days ....
Using the slicer we can navigate between the dates too ... Cons of this we have to fix the date ...In power bi using slicer we can change the dates too

[ShubhamRajputDataTalks]

Hi Ankit, PFB solution if holidays fall into weekend : -
select *,
DATEDIFF(day, create_date, resolved_date) - 2*DATEDIFF(week, create_date, resolved_date) - holiday as bdays
from
(select t.*, count(holiday_date) as holiday
from tickets t
left join holidays h on h.holiday_date between t.create_date and t.resolved_date and
DATEPART(weekday, holiday_date) not in (1, 7)
group by ticket_id, create_date, resolved_date) A

[aayushkaul]

Great question, solution, and explanation. Thanks for sharing! :)
Don't mean to be a critique but have two suggestions:

1. When counting no. of days between 2 dates we should always add ONE(+1) otherwise we miss a day DATEDIFF(DAY, start_date, end_date) +1.
Also, there might be cases where we don't want to add(+1). So, based on our business needs :)

For example: in the above scenario ('2022-08-01', '2022-08-03') --> it should be 3 days and not 2

2. There is a corner case with the above code - this solution would work only when tickets are created on weekdays and not weekends.
Ideally, as per business case, we should not have a ticket created on weekend but (we all know the kind of data we get is never 100% clean :P)

Example: Below 2 scenarios will give incorrect results

, (4,'2022-08-19','2022-08-27')
, (5,'2022-08-21','2022-08-27');

I solved this with another approach where the above scenarios are covered.

;with _cte as
(
select ticket_id, create_date, resolved_date from #tickets
UNION ALL
SELECT t.ticket_id, DATEADD(DAY, 1, b.create_date) create_date, T.resolved_date FROM #tickets T
inner join _cte b on b.ticket_id = t.ticket_id
where b.create_date < T.resolved_date
)
,
_CTE2 AS(
select C.*
, CASE WHEN DATEPART(WEEKDAY, create_date) IN (1, 7) then 1 ELSE 0 END AS BusinessHoliday
, CASE WHEN H.holiday_date IS NOT NULL THEN 1 ELSE 0 END AS FirmHoliday
from _cte C LEFT JOIN #holidays H
ON C.create_date = H.holiday_date
)
SELECT ticket_id, MIN(CREATE_DATE), MAX(CREATE_DATE), COUNT(1) AS TotalDays,
sum(BusinessHoliday) BusinessHolidays, sum(FirmHoliday) FirmHolidays
, COUNT(1) - sum(BusinessHoliday) - sum(FirmHoliday) as TotalBusinessDays

FROM _CTE2
group by ticket_id
order by 1

[nachiketpalsodkar]

Another Simpler Way!!!

;
WITH cte as (
select *
from tickets as t LEFT JOIN holidays as h
on h.holiday_date BETWEEN t.create_date AND t.resolved_date )

select *
, DATEDIFF(DAY, create_date, resolved_date) - 2*DATEDIFF(WEEK, create_date, resolved_date) - COUNT(reason) over(partition by ticket_id order by ticket_id) as NOOFBUSINESSDAYS
from cte

[rohitmishra]

For last part instead of sub query i used CTE
select ticket_id, create_date, resolved_date, (business_days - no_of_holidays) as actual_business_days
from CTE
and got the answer.

[vinil]

The syntax is so much more complex in Postgres and MySQL

[santoshsokalyanrao]

I have also a little logic to solve the assignment of yours:

with cte as (
select *
from tickets left join holidays on (holiday_date between create_date and resolved_date) and
(dayname(holiday_date) <> 'Saturday' and dayname(holiday_date) <> 'Sunday')
)
select ticket_id, create_date, resolved_date,
datediff(resolved_date, create_date) - - count(holiday_date) actual_bussiness_days
from cte
group by ticket_id, create_date, resolved_date;


output is like this below:
ticket_id, | create_date, | resolved_date, | actual_bussiness_days
1 | 2022-08-01 | 2022-08-03 | 2
2 | 2022-08-01 | 2022-08-12 | 8
3 | 2022-08-01 | 2022-08-16 | 9

[fantasytalker]

I have been trying to solve this problem in real world project! thank you.

[lakshmig]

Great video, feeling fortunate to visit this channel. Thank you so much for helping all in desperate need to learn SQL SERVER development

[rabink.]

Can any solve this problem;
-- find the total number of people present inside the hospital--
create table hospital ( emp_id int
, action varchar(10)
, time timestamp);

insert into hospital values ('1', 'in', '2019-12-22 09:00:00');
insert into hospital values ('1', 'out', '2019-12-22 09:15:00');
insert into hospital values ('2', 'in', '2019-12-22 09:00:00');
insert into hospital values ('2', 'out', '2019-12-22 09:15:00');
insert into hospital values ('2', 'in', '2019-12-22 09:30:00');
insert into hospital values ('3', 'out', '2019-12-22 09:00:00');
insert into hospital values ('3', 'in', '2019-12-22 09:15:00');
insert into hospital values ('3', 'out', '2019-12-22 09:30:00');
insert into hospital values ('3', 'in', '2019-12-22 09:45:00');

Base on the data, emp_2 should be in the building, emp_3 already inside building but exit, again enter, exit and finally enter. So there should be 2 people (emp_2 and emp_3) currently present inside the building(hospital)

[dfkgjdflkg]

Very inspiring and teaching video. These videos are great.

[Datapassenger_prashant]

I love coming back to your videos to check, if I have improved or not and how my approach has changed so far..
so sharing the final answer including if holidays on weekends.. please note, I still haven't watched the video before writing this code.


with cte as (
Select *, DATEDIFF(Day, create_date, resolved_date) as Days_Difference,
DATEDIFF(Week, create_date, resolved_date) as Weeks_Count_between_Dates
from tickets
), exclude_Holidays as (
Select C.ticket_id,
max(c.create_date) as create_date,
max(c.resolved_date) as resolved_Date,
as Business_Days,
count(case when h.holiday_date between c.create_date and c.resolved_date
and datename(Weekday, h.holiday_date) not in ( 'Saturday', 'Sunday')
then 1 end) as holidays
from cte c
left join holidays h
on h.holiday_date between c.create_date and c.resolved_date
group by c.ticket_id
)
Select *, Business_Days-holidays as
from exclude_Holidays;

[shreyagupta]

Quite Insightful problem !! Thanks for this :)

[lovishbabar]

As a Fresher, If we have prepared Sql and excel both .
Sir, Can you tell me on which platform, companies are hiring ? I am applying not getting any call ?

[fahadmahmood]

Very well
Explained ..I used the same logic in one of my use and it fits perfectly. thanks Ankit