Solving a REAL Business Use Case Using SQL | Business Days Excluding Weekends and Public Holidays
Вставка
- Опубліковано 31 лип 2024
- 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:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
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 - Наука та технологія
![Amazon SQL Interview Question for Data Analyst Position [2-3 Year Of Experience ] | Data Analytics](http://i.ytimg.com/vi/oGYinDMDfnA/mqdefault.jpg)
![Amazon SQL Interview Question for Data Analyst Position [2-3 Year Of Experience ] | Data Analytics](/img/tr.png)
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) - 2*(week(resolved_date)-week(create_date)) - count(holiday_date) actual_biz_days
from cte
group by ticket_id,create_date,resolved_date;
Perfect. Thanks for posting 👏
This code is not working in MS SQL server... Plz solve it
@@nishushroff9656 Yh , it was written in Mysql. For MS server try this
DATENAME(WEEKDAY, holiday_date) in place of dayname(holiday_date);
@@abb_raj1107 yess it worked... Thank you so much 🌸❤
I think this condtion should be an OR not AND. Please correct if i am wrong..
(dayname(holiday_date) 'Saturday' or dayname(holiday_date) 'Sunday')
Quite Insightful problem !! Thanks for this :)
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
Very inspiring and teaching video. These videos are great.
Count(holiday_days) part was bit tricky and you explained it very well bhai. Thankyou 🙏
Thank you 😊
Very intersting one. Thanks for the logical thinking
Good question nd content 🙌🙌
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.
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
YOU ARE AMAZING DUDE! SAVED ME FROM HOURS AND HOURS OF WORK!!!!!!!!!!!!! AL THE BEST
You are welcome!
Great video, feeling fortunate to visit this channel. Thank you so much for helping all in desperate need to learn SQL SERVER development
You are welcome!
This is 🔥 And YOU are 🔥
Thank You Ankit Sir Ji 🙏🏻 Dil se !!
Thank you 😊
Very well
Explained ..I used the same logic in one of my use and it fits perfectly. thanks Ankit
Great 👍
Thank you so much.
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 ?
Alternate Saturday holiday situation could you please make a video on this as well
Thanks a lot Ankit for the Videos ...
For assignments, added " and datename(weekday,holiday_date) not in ('Saturday','Sunday') " in the subquery of your final query.
I have been trying to solve this problem in real world project! thank you.
Glad I could help!
can you solve this, how do we eliminate duplicate records using analytical function? we should use row number right? or it can be done using rank and dense_rank as well?
Does this logic of calculating actual days and business days work if create_date is the last day of a week and resolve_date is the first day of the next week?
Because, in this case the actual days will be equal to 1 and business days will be (1 - (2 * 1)) = -1.
Hey I tried your given problem
Will this work
With cte as
(Select *, (CASE WHEN DATEPART(dw,holiday_date) = 1 OR DATEPART(dw,holiday_date) = 7 THEN 1 ELSE 0 END) AS Public_boolean
from holidays)
Select *,
datediff(day,create_date,resolved_date) as actual_days,
datediff(day,create_date,resolved_date) - 2*datediff(week,create_date,resolved_date) - (SELECT COUNT( IF (holiday_date BETWEEN tickets.created_date AND tickets.resolved_date) AND (Public_boolean = 0) ) FROM cte) as business_days
What to do if we want to calculate time difference in hours between two timestamps excluding the weekends?
It's very useful.. Thanks a lot!!
You are welcome!
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
Superb
Awesome bro..
Day 2 of 47
Thanks for the video series
Great
Thanks for your help. Amazing
Happy to help!
does someone know from where to get all these questions by anki bansal in pdf?
This solution doesn't work if the create date and resolution date are in two different calendar years. For example, if the create date is December 29, 2020(week 53 for 2020) and resolution date is January 20, 2021 (week 4 of 2021), the week difference calculation would return a negative number (-49). As a result, the Business day calculation returns incorrect results. Is there a way to get around this, please?
Datediff function will return the right result irrespective of year
how to solve this problem in oracle as datediff is not working in oracle sql developer? post solution as soon as possible
awesome
It seems in postgresql , we will need to do lot of extra boiler plate just to get the datediff
Hi sir may be output is not correct just because when holiday and Sunday on same day then it should be count 1 as holiday but in your output it is showing 2 days
Problem with query if I would have Saturday in between or only Sunday then that query will not consider it
what will be the query for mysql...
The syntax is so much more complex in Postgres and MySQL
Awesome vdo ❤❤😇😇
Thank you 😊
what if the holidays falls in weekends as well ?
My approach for the assignment:-
select *,
datediff(day, create_date, resolved_date) - (datediff(week, create_date, resolved_date) * 2) - no_of_holidays as business_days
from
(select ticket_id, create_date, resolved_date, count(holiday_date) as no_of_holidays
from tickets
left join holidays
on holiday_date between create_date and resolved_date and datepart(WEEKDAY, holiday_date) not in (1, 7)
group by ticket_id, create_date, resolved_date) A
Just a note:
The syntax used by different flavors of SQL may differ with others....I use SQL Server...some i see have used MySQL...
I think this will work for both situations.
with recursive dates as (
select min(create_date) as start_date from tickets
union all
select start_date+ interval 1 day from dates where start_date= t.create_date and start_date
Why have you put holiday date is null
@@ankitbansal6 I am trying to exclude holidays from business days
is this sql server syntax, I am getting error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'dates'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '1'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Msg 195, Level 15, State 10, Line 9
'weekday' is not a recognized built-in function name.
Can we do like this that In that Holiday List first we compute Week days for it ,So in mysql If week day for weekends or(sat and sun) is 5 or 6 ,if weekday for holidays is either 5 or 6 can we eliminate by tht method ..? And take only holidays which are not in weekends
exactly :)
Please make secnarios about Joins 🤯🤯
Very useful. Thanks for the video Sir Ankit.
A quick question: Solution was created assuming that the date_created and date_resolved is in the same year. What if date_created and date_resolved in different year (i.e. date_created="21/12/2022" & date_resolved = "14/1/2023" ) ?
This will work across years as well
Hi Ankit
I tried solving the assignment .
with cte as (
select t.ticket_id,t.create_date,t.resolved_date,count(h.holiday_date) as noofholidays
--,datediff(day,create_date,resolved_date) as daydiff,
---datediff(WEEK,create_date,resolved_date) as daydiff,
--
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 (7,1)
group by t.ticket_id,t.create_date,t.resolved_date)
select * , datediff(day,create_date,resolved_date) -2*datediff(WEEK,create_date,resolved_date)- noofholidays as actualdays
from cte
Thanks very much. V. Helpful when doing student attendances. What if we wanted it from two dates rather than 1 date, e.g. half term break for spring term is 13-02-23 to 17-02-23? I need to exclude attendance absences from these dates…
While joining with the holiday table join based on range using between operator.
please consider situation that some holidays lies in weekend as well
In one of interview Interviewer asked me to solve same question using pyspark
What if incident got created on weekend or holiday
Greetings of the day Sir,
I've solved the second question by the following way given below. Please let me know whether my solution is correct or not..
with cte as(
select *
from tickets
left join holidays
on (holiday_date BETWEEN create_date And resolved_date)
AND (holiday_date create_date AND holiday_date resolved_date)
)
select ticket_id , create_date , resolved_date
, datediff(resolved_date , create_date) AS actual_days
, datediff(resolved_date , create_date) - 2*(weekofyear(resolved_date) - weekofyear(create_date)) - count(holiday_date) AS business_days
from cte
group by ticket_id , create_date , resolved_date ;
My approach for additional assignment sir:
SELECT
ticket_id ,
create_date,
resolved_date,
datediff(day,create_date,resolved_date) as act_days,
datediff(day,create_date,resolved_date) - 2*datediff(week,create_date,resolved_date) - holiday as business_days
from (select
ticket_id ,
create_date,
resolved_date,
count(holiday_date) as holiday
from ticket tk
left join ( select *
from holiday
WHERE DAYNAME(TO_DATE(holiday_date)) NOT IN ('Sat', 'Sun') )hl
on hl.holiday_date between tk.create_date and tk.resolved_date
group by 1,2,3
order by tk.ticket_id);
If the holiday is weekday then the query will not work, To get the correct answer need to check whether the holidays are on sunday or saturday using DATENAME(weekday ,date)
edge case scenario what if holiday falls on saturday?
Imagine if ticket raised on Friday and closed Monday. Total actual days is 4 and working days is two only. Will this work for this scenario also.
Please explain.
Yes
Hi Ankit,
I have a query that suppose any of the public holiday falls on weekend then we will be deducting the same day twice as per the logic. One solution can be that we maintain our holiday table only for weekdays and it will work fine. But if we don’t maintain that then can we have another condition in left join that public holiday should be considered if it is on weekday?
Exactly. I have given that assignment at the end of the video 🙂
@@ankitbansal6 so will the solution will work of adding another condition in left join with ‘and’ operator…using weekday function to filter only those which are not weekends
@@SatyamKumar-bd9qh right
@ankitbansalSir why have you added A at the end of sub-query?
In SQL server it is mandatory to give alias to a sub query.
@@ankitbansal6 Thank you so much for clearing it up and for the quick reply.
Why did you put 'A' in the end?
with cte as(select *,
datediff(day,t.create_date,t.resolved_date)-2*datediff(week,t.create_date,t.resolved_date) as business_days
from tickets t
left join holidays h on h.holiday_date between t.create_date and t.resolved_date)
select ticket_id,create_date,resolved_date,max(business_days)-count(holiday_date) as actual_business_days
from cte
group by ticket_id,create_date,resolved_date
@
Ankit Bansal
can you tell me me what wrong in my solution
Ankit Bansal
select ticket_id,create_date,resolved_date,DATEDIFF(day,create_date,resolved_date) as actual_day,
DATEDIFF(day,create_date,resolved_date)-2*(DATEDIFF(week,create_date,resolved_date)) as buisness_day,
count(holiday_date) as no_of_publicholidays
from tickets left join holidays on holiday_date between create_date and resolved_date
group by ticket_id,create_date,resolved_date;
Need to group the business and holiday days
Hi Ankit
this logic will not work if the resolved_date is falling on weekend
eg from 1 may 2024 to 18 may 2024
it should be 13 not 12
We assumed that the create date and resolve date will be on weekends for this problem. We can handle weekend use cases using case when
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
Thanks for the findings and adding solution 👍👏
Hi Ankit sir can you please explain above solution I am not able to understand it.
great 👍
Hey ayush, I just want to add, we are finding actual business days taken to resolve the ticket i.e. if ticket raised on saturday still support team will start working on it from monday, so it does not matter whenever it is raised, if its raised on weekend we will still skip weekend as its not working day.
please correct me if i am wrong.
also I have posted my query using pgsql and output as well to justify above explanation ,please take a look at it.
This solution seems to be subtracting those weekend(s) twice if a holiday(s) falls on a particular weekend(s). Since we removed all the weekends using 'BusinessHoliday' already once. Can you/anyone check and confirm?
holiday as (
select holiday_date from holidays where DATENAME(dw,holiday_date) not in ('Saturday','Sunday'))
Good one
SELECT * FROM holidays
WHERE DATENAME(WEEKDAY,holiday_date) NOT IN ('Saturday','Sunday')
Adding this in joining condition
answer of the assignment: Please note I use Postgres SQL a lot, so my query is updated on datediff function of SQL server to Postgres based function.
SELECT
t.ticket_id,
t.create_date,
t.resolved_date,
date_part('day', t.resolved_date::timestamp - t.create_date::timestamp) AS actual_days,
date_part('day', t.resolved_date::timestamp - t.create_date::timestamp) -
(2 * TRUNC(DATE_PART('day', t.resolved_date::timestamp - t.create_date::timestamp) / 7)) -
COUNT(CASE
WHEN extract(dow FROM h.holiday_date) IN (0, 6) THEN NULL
ELSE h.holiday_date
END) AS business_days
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
ORDER BY
t.ticket_id;
It took me 40 minutes to resolved this question , how bad i am in sql can anyone reply?
But what when the year changes and you take difference between 1st week and 52th?
Datediff function takes care of it.
👍
hello @Ankit Bansal Sir , thanks for providing such useful videos.
sir , I am using Extract function for getting week out of current and resolved but it is returning one week less as compared with your result . can you please tell why?
I read the manual but didn't found any information regarding this
Hi Ashutosh, are u sing mysql? and if using week() function for calculating than week takes the count form (0 to 53).
Hi Bro, I've recently started learning python and sql and want to switch to data analysis role, I've done basic bootcamp course from udemy, how should i get more grasp on sql in particular, please guide. TIA
practice more. Learn Window Functions, Analytical functions, CTE, Aggregation. Find question online(Leetcode, Hackerrank etc.). Try to solve them yourself. Watch Ankit's videos and practice them yourself. Hope it answers your question.😃
@@NamanSeth1 This helps a lot, thanks bro!
Welcome 😃
You have got the answer 🙂
You got the job now?
Did it using Oracle. Here is the solution-
select ticket_id
,((resolve_date-create_date)- 2*round((resolve_date-create_date)/7))-(sum (case when TRIM(TO_CHAR(holiday_date, 'DAY')) IN ('SATURDAY','SUNDAY') then 0 when HOLIDAY_DATE is null then 0 else 1 end )) as business_days
from tickets
left join holidays on holiday_date between create_date and resolve_date
group by ticket_id, resolve_date, create_date
order by ticket_id
Assuming- Tickets are not created on weekends and the day ticket is created is not counted as a business day. Else just add 1
hi ankit
below is the sollution for case 2
select ticket_id,create_date,resolved_date,(DATEDIFF(day,create_date,resolved_date))-2*(DATEDIFF(WEEK,create_date,resolved_date))- holiday_count
as actual_business_days
from(
select ticket_id, create_date,resolved_date,sum(holiday_day) as holiday_count
from (
select *,datename(weekday,holiday_date) as weekday_name,
case when datename(weekday,holiday_date) in ('saturday','sunday') then 0
when datename(weekday,holiday_date) in('monday','tuesday', 'wednesday','thursday','friday') then 1 else 0 end as holiday_day
from tickets
left join holidays on holiday_date between create_date and resolved_date
)a group by ticket_id,create_date, resolved_date
) b
Next business day after 11 business days?
I have tried below excluding weekend holidays
Select t.*,datediff(day,create_date,resolved_date)-(2*datediff(week,create_date,resolved_date))
-count(holiday_date) as Business_days
--,datename(weekday,holiday_date)
from
tickets t
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
🙏
with cte as (
select *
from tickets a left join
(select * from holidays where datepart(weekday,holiday_date)7 and datepart(weekday,holiday_date)1 ) b
on (b.holiday_date between a.create_date and a.resolved_date)
--and (dayname(holiday_date) 'Saturday' and dayname(holiday_date) 'Sunday')
)
select ticket_id,create_date,resolved_date,
abs(datediff(day,resolved_date,create_date) - 2*datediff(week,resolved_date,create_date) - count(holiday_date))
as Days_to_resolve from cte
group by ticket_id,create_date,resolved_date;
-- when a holiday falls on a weekend
-- check the dayofweek, if on weekend, flag it as false, else true
with cte as (
select *,dayofweek(holiday_date) as day_week,
case
when dayofweek(holiday_date) not in (6,7) then 'true' else 'false' end as is_considered
from holidays
),
cte1 as (
select * from tickets t
left join
cte h
on
t.create_date < h.holiday_date
and
t.resolved_date > h.holiday_date
),
-- when counting the holidays, count only rows where is is_considered is true or is null
cte2 as (
select ticket_id, create_date, resolved_date, count(holiday_date) as holidays
from cte1
where is_considered = 'true' or is_considered is null
group by 1,2,3
)
select ticket_id, create_date, resolved_date,
(datediff('day', create_date, resolved_date) -
datediff('week', create_date, resolved_date)*2) - holidays as diff
from cte2
order by ticket_id asc;
Thanks for posting. An easy way is to put a filter on the holiday table to get rid of public holidays on weekends .
Assignment solution:
with cte as
(select * from holiday
where datename(weekday,holiday_date)'sunday'or
datename(weekday,holiday_date)'saturday' )
SELECT ticket_id,create_date,resolved_date,count(holiday_date) as ct,
datediff(day, create_date,resolved_date)-
2*datediff(week, create_date,resolved_date)- count(holiday_date) as businessdays
FROM tickets left join cte on holiday_date between create_date and resolved_date
group by ticket_id,create_date,resolved_date
The results will be incorrect if the resolved_date falls on a Sunday in the 2nd row of tickets, as eventhough the week diff will be 1, the no. of weekend days in between will be 3 , not 2.
yes but , nothing will get resolved on the weekend
If holyday is in weekend:
with a as(
select ticket_id,
datediff(resolved_date,create_date) - 2*(WEEK(resolved_date)-WEEK(create_date)) as ActualDays
from tickets)
, b as (
select ticket_id, create_date,resolved_date,
count(Case when DAYNAME(holiday_date) not in ('Saturday','Sunday') then 1
when reason is null then null
else 0 end) as hd
from tickets
left join holidays on holiday_date between create_date and resolved_date
group by ticket_id,create_date,resolved_date)
select a.ticket_id, a.ActualDays-b.hd as workdays from a inner join b on a.ticket_id=b.ticket_id;
#This query will handle the holidays in weekend
select ticket_id,create_date,resolved_date,(datediff(resolved_date,create_date)-2*(week(resolved_date)-week(create_date))-no_of_holidays) as business_days from
(select ticket_id,create_date,resolved_date,
count(case when weekday(holiday_date) not in(5,6) then holiday_date else null end) as no_of_holidays
from tickets left join holidays on holiday_date between create_date and resolved_date group by ticket_id,create_date,resolved_date)A;
Thanks for posting 👏
For PostgreSQL - the query will be
select *,(day_diff - 2* week_diff- holiday) as business_day from(
SELECT ticket_id,create_date,resolved_date,count(holiday_date)as holiday,EXTRACT('week' FROM resolved_date)-EXTRACT('week' FROM create_date) AS week_diff,
(resolved_date- create_date ) AS DAY_DIFF from tickets
left join holidays on holiday_date between create_date and resolved_date
group by ticket_id,create_date,resolved_date
order by ticket_id)A
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)
Was this question asked in any interview? Which company?
@@ankitbansal6 , it was asked by Amazon for Data analyst job. 2-3 years of experienced required.
Ok. Answer should be 2 and 3 and not 1 and 2 that you mentioned
@@ankitbansal6 , its typo. Answer should be 2 and 3. Now, problem got solved, here is the queries
select count(*)
from (
select * from hospital
where (emp_id,time) in
(
Select emp_id,max(time) as time
from hospital
group by emp_id
)
)as X
where X.action = 'in';
with cte as
(
select t.*, t.create_date + (level-1) as consecutive_date
from tickets t
connect by t.create_date + (level-1)
Got a hint from your video that need to use datediff(week,...) in order to calculate weekends. After that, wrote this:
with cte1 as
(select ticket_id,create_date,resolved_date,
case when holiday_date is null then 0 else
count(*) over(partition by ticket_id,create_date,resolved_date) end as public_holidays
from
(select ticket_id,create_date,b.holiday_date,resolved_date
from tickets a left join holidays b on a.create_dateb.holiday_date
) A
),
cte2 as
(select *,
datediff(day,create_date,resolved_date) - 2*datediff(week,create_date,resolved_date) as business_day
from tickets)
select distinct a.ticket_id,a.create_date,a.resolved_date,
(a.business_day - b.public_holidays) as days
from cte2 a left join cte1 b on a.ticket_id=b.ticket_id
Good effort 😊 but can be simplified a bit. Check out my solution
Shouldn't the answer be "" datepart(weekday,holiday_date) NOT IN (5,6) "" and not actually *(6,7) ?
As I guess weekday function works like : 0 = Monday , 1= Tuesday and so on till 6 = Sunday.
Can you please confirm?
I think Sunday is 0 or 7 varies database to database . Monday is always 1.
@@ankitbansal6 Thanks a lot for the quick reply. Also I tried checking this through various resources documentation and it says the same about Monday being 0. And then i tried with 2-3 diff online editors and with this syntax every time I am getting 0 for Monday and that was the reason I was wondering about the same.
My solution in MYSQL (Considering the case week end days in holidays)
select ticket_id,create_date,resolved_date,datediff(resolved_date,create_date) as actual_days, cnt_holidays as holidays,cnt_week_holidays,
datediff(resolved_date,create_date) - 2*floor(datediff(resolved_date,create_date)/7) - cnt_holidays - cnt_week_holidays as business_days
from(
select ticket_id,create_date,resolved_date,count(holiday_date) as cnt_holidays,
sum(case when weekday(holiday_date) in (5,6) then 1 else 0 end) as cnt_week_holidays
from tickets left join holidays on holiday_date between create_date and resolved_date
group by ticket_id,create_date,resolved_date) temp;
My solution for assignment in Mysql
with cte as
(select t.ticket_id, t.create_date, t.resolved_date, h.holiday_date, count(h.holiday_date) as public_holidays
from tickets t
left join holidays h
on h.holiday_date between t.create_date and t.resolved_date
group by 1, 2, 3)
select *,
datediff(resolved_date, create_date) as actual_days,
datediff(resolved_date, create_date) - 2*(week(resolved_date) - week(create_date)) as total_weekdays,
case when weekday(holiday_date) in (5,6) then datediff(resolved_date, create_date) - 2*(week(resolved_date) - week(create_date))
else datediff(resolved_date, create_date) - 2*(week(resolved_date) - week(create_date)) - public_holidays end as total_working_days
from cte;
Thanks for the videos
Her is my approach-->
select c.*,count(holiday_date) as hd,
case when wek>0 then days-wek*2-count(holiday_date) else days end as actua_days
from (select ticket_id,create_date,resolved_date, DATEDIFF(day,create_date,resolved_date) as days,
DATEDIFF(WEEK,create_date,resolved_date) as wek
from tickets) c
left join holidays h on holiday_date between create_date and resolved_date
group by ticket_id,days,wek,create_date,resolved_date,holiday_date
having datename(dw,holiday_date) not in('Sunday','Saturday') or count(holiday_date)=0;
How we get resolve_date - let say 5 business day- holiday_date
how to solve this in oracle sql?
Same will work
@@ankitbansal6 I tried itz not working
with cte as (Select t.ticket_id,H.reason, datediff(t.resolved_date,t.create_date) as actual_day,
datediff(t.resolved_date,t.create_date)-2*Floor(datediff(t.resolved_date,t.create_date)/7) as business_day
from tickets as t left join holidays as H on holiday_date between create_date and resolved_date),
cte2 as (select ticket_id,actual_day,count(reason) as holiday,business_day from cte group by ticket_id,business_day,actual_day)
select ticket_id,(business_day-holiday) from cte2;
below query without public holiday on weekends
with cta_table as
(
select * , resolved_date - create_date as days,
(select count(*) from holidays h1 where h1.holiday_date > t1.create_date and h1.holiday_date < t1.resolved_date) as holidays,
(
select count(*) from (SELECT t.day::date , extract(dow from t.day::date)
FROM generate_series(t1.create_date::timestamp
, t1.resolved_date::timestamp
, interval '1 day') AS t(day)) as f where f.date_part in(0,6)
) as weekends
from tickets t1
)
select *, days-holidays-weekends as business_days from cta_table
solution of question , if holiday falls on weekend------->
select *,
datediff(resolved_date,created_date) as actual_days,
datediff(resolved_date,created_date)-2*(week(resolved_date)-week(created_date))-A.actual_no_of_hoilday as business_days from (
select id,created_date,resolved_date,count(holiday_date) as no_of_holiday,
ifnull((case when dayofweek(holiday_date) in (1,7) then count(holiday_date)-1 end),0) as actual_no_of_hoilday
from ticket t left join holidays h on h.holiday_date between t.created_date and t.resolved_date
group by id,created_date,resolved_date) A;
My approach for additional assignment:
select *,datediff(day,create_date,resolved_date)-(2*datediff(week,create_date,resolved_date)+cn) busness_day from
(with hd as(select holiday_date,reason from holidays where dayname(holiday_date) not in ('Sat','Sun'))
select t.*,count(h.holiday_date)cn from tickets t left join hd h on h.holiday_date between t.create_date and t.resolved_date
group by t.ticket_id,t.create_date,t.resolved_date);--Snowflake
THE ASSIGNMENT SOLUTION:- select *,
datediff(day, create_date, resolved_date) as total_days,
(datediff(day, create_date, resolved_date) -
2 * (datediff(week, create_date, resolved_date))) -
no_of_holidays as business_days
from (
select ticket_id,
create_date,
resolved_date,
count(holiday_date) as no_of_holidays
from tickets
left join holidays on holiday_date between create_date and resolved_date
and datepart(weekday, holiday_date) not in (1, 7) -- Exclude Sunday (1) and Saturday (7)
group by ticket_id, create_date, resolved_date
) A;
with cte as (select *,case when dayofweek(holiday_date)=1 then null
when dayofweek(holiday_date)=7 then null
else holiday_date
end as holiday_date1
from holidays)
select datediff(day,create_date,resolved_date ) as actualdays,
datediff(day,create_date,resolved_date)-2*datediff(week,create_date,resolved_date)-no_of_holidays as businessdays
from(select ticket_id,create_date,resolved_date,count(holiday_date1) as no_of_holidays from tickets left join cte on holiday_Date1 between create_Date and resolved_Date group by ticket_id,create_date,resolved_date)
My Sol:
with recursive cte as(
select ticket_id,create_date,resolved_date from tickets
union all
select ticket_id,DATE_ADD(create_date, INTERVAL 1 DAY),resolved_date
from cte where DATE_ADD(create_date, INTERVAL 1 DAY)
My solution :
with cte as (
select ticket_id,create_date,resolved_date , count(holiday_date) as holiday_count from dummy_tickets t left join holidays h on h.holiday_date between t.create_date and t.resolved_date
where h.holiday_date is not null
group by ticket_id,create_date,resolved_date)
select t.ticket_id, t.create_date, t.resolved_date , c.holiday_count , datediff(day, t.create_date,t.resolved_date),datediff(day, t.create_date,t.resolved_date) - 2*datediff(week, t.create_date,t.resolved_date) - case when c.holiday_count is null then 0 else c.holiday_count end as actual_days_between
from dummy_tickets t left join cte as c
on t.ticket_id=c.ticket_id;
MYSQL:
with cte as (select *, datediff(resolved_date,create_date) as diff,
weekofyear(resolved_date) - weekofyear(create_date) as week_diff,
datediff(resolved_date,create_date) - 2 * (weekofyear(resolved_date)-weekofyear(create_date)) as weekend_removed
from tickets)
select ticket_id, create_date,resolved_date,resolved_date - create_date as actual_days,
sum(holidays) as no_of_holidays ,weekend_removed - sum(holidays) as business_days
from (
select ticket_id, create_date,resolved_date,weekend_removed,
case when holiday_date between create_date and resolved_date then 1 else 0 end as holidays
from cte, holidays) a
group by ticket_id, create_date,resolved_date,weekend_removed