Introduction
Hi All,
My name is Ankit Shrivastava and I am a Data Engineer.
Today this #vlog is regarding #sql
Calculate total number of business days between two given dates considering following factors :
1. Consider weekends
2. Consider holidays
3. Consider what if your holiday is coming on weekends.
Please find the DDL for practice:-
create table order_details (id integer, start_date date,end_date date);
insert into order_details values (1,'2022-Dec-30','2023-Jan-05');
insert into order_details values (2,'2023-Jan-03','2023-Jan-05');
insert into order_details values (3,'2023-Jan-03','2023-Jan-13');
insert into order_details values (4,'2023-Jan-03','2023-Jan-31');
create table holiday_details (holidaye_name varchar(20), holiday_date date);
insert into holiday_details values ('New year','2023-Jan-01');
insert into holiday_details values ('Republic Day','2023-Jan-26');
insert into holiday_details values ('Holiday_0','2023-Jan-06');
insert into holiday_details values ('Holiday_1','2023-Jan-18');
Please find the link of my all previous videos:-
#aws S3-IAM-Redshift | Copy data from S3 to Redshift and vice versa using IAM role & policy #amazon
youtu.be/YUXpQtIuid0
#sql interview question How to achieve Organisational hierarchy using self join from one table | DE
youtu.be/CNT1svWQCpg
#sql Count occurence of character in a string / Count occurrence of word in a sentence #data analyst
youtu.be/DGfqrjWNhE4
#sql - difference between WHERE & AND clause with LEFT/RIGHT join | INNER join+WHERE=LEFT join+WHERE
Link www.youtube.com/watch
#sql How to handle NULL while aggregation | SUM() | AVG() | Data Analyst | Data Engineer |Math calc
youtu.be/MhhBjNitslM
#sql Calculate start & end time and average time spent in successful transaction | startup interview
Link youtu.be/7uo450TmTak
#sql to achieve last NOT NULL value from the record | Analytical Function | Data Engg | Data Analyst
Link www.youtube.com/watch
#sql to identify Top Performing Product every Quarter of each year | Highest sale every quarter
Link www.youtube.com/watch
#sql Interview Question - Calculate Running Total | Cumulative sum | UNBOUNDED PRECEDING FOLLOWING
Link www.youtube.com/watch
#sql Interview question - Schedule cricket match between teams and generate points table using SQL
Link lnkd.in/dsM8Pe8B
#sql Interview Questions - All JOINS (INNER, LEFT, RIGHT, FULL OUTER) | JOIN only NULL values
Link lnkd.in/dJttWQBB
#sql Interview Question - What is the difference between COUNT(*), COUNT(1) and COUNT(-1)
Link lnkd.in/dVHMWxKj
#SQL Interview Question - How to delete duplicate record from table
Link lnkd.in/dcVyhC7X
#SQL Interview Question -Second highest salary using dense rank analytical function
Link lnkd.in/daBumQfB
#SQL to pull unique record after combination of column
#SELFJOIN
Link lnkd.in/d6tXYXCv
#SQL Join on duplicate values all four joins (INNER, LEFT, RIGHT, FULL OUTER)
Link lnkd.in/d9HcwkZs
#dataengineering #etl #dataanalysts #dataanalysis #bigdata #business
Content
Hello, everyone, my name, is uncle srivastava and today I am discussing a very important SQL problem.
So the problem statement is, we have to calculate the business days between the two dates.
Okay, and what are the factors we are going to include? Is we have to consider the weekends okay? So, if weekends are coming between the two dates, we have to exclude two days out of total days between the start and end date.
Okay, also, we have to consider the holidays as well, so in case any holiday in case any holiday is coming in between the two dates start and end it.
We have to consider that as well and what, if your holiday is coming on weekends right, so how you are going to handle that as well? Okay, so I hope.
The problem statement is clear: now, let's quickly jump on SQL and see how we can handle this okay.
So if you see the data here, we have two different tables: okay, the one is the order, details and the second one is the holiday details.
Okay, so let's quickly jump on SQL and see so I have already inserted the data in order, details and holiday details Okay.
So in order details you can see we have a start date and end date, okay and and in in in the holiday details table.
We have already inserted the holiday date like our new year.
Okay, the Republic day, which is on 26th of Jan and just for the uh handling different scenarios.
I have inserted two more holidays, which is on 18th of Jan, and one is on 6th of Jan okay.
So we will consider these two.
Does these two days as holiday? Okay, just for our understanding purpose, okay.
So to avoid any syntax mistake and also uh to keep the length of the video.
Pretty short I have already written the code and we will go through each line of the code.
Okay, so here in this case, I will explain first, what are the different functions? We are going to use in this SQL statement to achieve our goal? Okay, so let me execute it first and then we will go through each and every line.
Okay.
Okay.
Now here you can see we have a start date, end date, holiday, name holiday date.
The number of days number of weeks, then day of holiday and number of days to be deducted okay.
So if you consider the very first column number of days so here we are calculating the days between the start date and end date.
Okay, so what is the total number of date between the start date and end? It and I am using here, plus one just to uh in some cases, what happened that we we miss the first day or last day? Okay, so we don't want to miss uh that day, okay, so that uh, we have used date, div function with started and ended with plus one okay, so number of days for this particular duration start date and end date is actually is seven days.
Okay, now, my next column is number of weeks.
So here we are calculating.
What is the total number of week coming in between these two dates? So when we say number of week that means your your date range should have Saturdays and Sundays.
Both then only it will calculate one uh one week right.
So if you see here here, you can see the total number of week is one in this case.
It's 0, because the start date is third of Jan and the end date is 5th of Jan right.
So total number of working days is three only and here we can see the number of week as 0.
right and similarly for this particular Row.
The weakest start date is of third of Jan and ending uh.
Sorry end that is 31st of Jan, so total V coming between the these two dates is four correct now, the day of the holiday, so this is very important actually, so our aim is to calculate the total business days between the two dates right.
So business date is like we have to exclude the total number of weekends coming in between the two days and we have to exclude the number of holidays coming in between two dates: right and also we have to handle what if my holiday is coming on weekends right, so we cannot calculate both weekends as well as holiday is coming on weekends right either.
We have to consider one of them correct so to handle that part.
I have calculated the day of the holiday right.
So if you see the New, Year is uh for this particular start and end date.
30Th of Jan 2022 to 5th of Jan 2023, the New Year holiday.
The holiday was coming as a new year and it's it's day of day was Sunday right.
So if you see here the day was Sunday correct, so you have to.
We have to handle that as well right, so one number of week is one, so that means we have to subtract uh two days from the total number of days right for this particular case.
Now, if we consider this particular line, okay, the start date is third of Jan and the end date is 13th of Jan.
So in this a date range, the total number of working days is a total number of days is 11 right and the number of week is 4, correct and the day of holiday is Friday.
So in this case we have to consider this uh day of holiday and we will subtract number of weeks.
That means one which, which means that two days plus one day three day so total number of days to be subtracted from this number of days, is three correct, so actual working day is eight here right.
No in this last case, you can see that the total number of week is 4 correct.
So we have to consider the eight holiday eight weekends, plus these three different holidays coming in between start date and ended.
Right hope.
This is clear.
Now we have done a join, we have used a left join between the order tables and the holiday tables right, and the join condition we have used is like holiday date between the start date and end date right so in case once the holiday date is falling in between the start date and ended you will, we will get a holiday date as a result, right and in case if, if any debt is not falling under these two dates, we will get a null and in case, if multiple dates are falling like for these cases, we will get multiple results right, because each holiday date will go for a join with with start date and end date.
Correct hope this is clear now, the most important part so I already inserted that I have already written the code for that.
So we will execute each block of code and understand okay.
So here we will execute the very first block here.
Okay, so here I have used, select a star and then date name function, so date name function.
I have used to pull the day on day of day on which the holiday is coming, Okay, so holiday as a holiday name.
Okay, now I have used a case statement.
Okay, so why I have used case statement here so to identify if my holiday holiday is coming on weekends or it is coming on weekdays.
So in case, if it is coming on weekends, we will not count that day uh.
We will not count that day, okay and if it is sorry if my holiday is coming on weekends, we will not count that day and if it is coming on weekdays, we will count it as a one.
Okay, and we will use this count uh further to subtract from the complete count Okay.
So here, if I execute this, you can see the we have the start date ended.
Then holiday name is coming as a new year.
Then the holiday date is 1, 1, 2023 and the day is Sunday, so my holiday day actually is zero for this one correct.
So, for this particular range, my holiday is I mean the whole account of the holiday is zero because the holiday was coming on Sunday, so we will only focus on uh, calculating the weekends and the total number of days between the start date and end date.
Now, if you consider this part, you can see that you have three different holidays falling between 3rd of January 31st of Jan right, so what we have to do, we will have to consider uh total number of working days, as date, uh total number of working days as total number of days between the start date and date, including excluding the weekends, as well as excluding these three days right, and these three days are coming on weekdays like Thursday, Wednesday and Friday.
Okay, hope this is clear now in this second sub part, I am calculating the start date end date and the sum I'm doing the sum on the holiday day right.
So if we execute this, I will get so for this particular start and end it I am getting the holiday count as zero between this I am also getting the holiday count as 0 correct, because there was one holiday of 1st of Jan, but that was on Sunday.
So we are not counting this, and in this case it's one and now the final one.
We have three different holidays right, because all the holidays were coming on different date.
Uh were coming on weekdays right and finally, what we are doing here, we are calculating the actual number of working days.
So let me explain you how we are calculating the actual number of days so between fifth of sorry, third thirtieth of December to fifth of Jan, the total actual working days was five and we were having one holiday which was coming on uh Sunday, so we we did haven't included that right.
So five.
Now in this case, we have only three working days because we have we were having no uh holidays and no weekends in between this okay.
Now, let's understand this part what we have did.
We have calculated that.
Sorry first is like we have calculated the date diff.
That is the days between the start date and end date and plus one right, and we have calculated the week and multiplied it with two so that every week will have two weekends, Saturday and Sunday so that the count will be 2 every time and now.
Finally we are subtracting it from the holiday count right so what is my holiday account my holiday count is this one right this is my holiday count so here if my if it is 0 that means nothing will be subtracted here and here in this case the three will be subtracted from this.
Particular date.
Range correct! So, let's execute this and see what is the total output? We are getting so actually, the total working days is 18 for this? Particular date.
Range right because, if you remember so, total day was 29 right.
Now, number of weeks was 4.
So 29 minus 8, is 21 and three holidays were coming? Which it was on weekday, so 21 minus 3, is 18 right, so that's? Why we are getting here, 18, correct, sorry, let me execute it again, perfect, so hope this makes sense.
You please like share and subscribe.
Thank you so much for watching the video.
