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
#sql interview question How to achieve Organisational hierarchy using self join from one table | DE
#sql Count occurence of character in a string / Count occurrence of word in a sentence #data analyst
#sql - difference between WHERE & AND clause with LEFT/RIGHT join | INNER join+WHERE=LEFT join+WHERE
#sql How to handle NULL while aggregation | SUM() | AVG() | Data Analyst | Data Engineer |Math calc
#sql Calculate start & end time and average time spent in successful transaction | startup interview
#sql to achieve last NOT NULL value from the record | Analytical Function | Data Engg | Data Analyst
#sql to identify Top Performing Product every Quarter of each year | Highest sale every quarter
#sql Interview Question - Calculate Running Total | Cumulative sum | UNBOUNDED PRECEDING FOLLOWING
#sql Interview question - Schedule cricket match between teams and generate points table using SQL
#sql Interview Questions - All JOINS (INNER, LEFT, RIGHT, FULL OUTER) | JOIN only NULL values
#sql Interview Question - What is the difference between COUNT(*), COUNT(1) and COUNT(-1)
#SQL Interview Question - How to delete duplicate record from table
#SQL Interview Question -Second highest salary using dense rank analytical function
#SQL to pull unique record after combination of column
#SQL Join on duplicate values all four joins (INNER, LEFT, RIGHT, FULL OUTER)
#dataengineering #etl #dataanalysts #dataanalysis #bigdata #business
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.
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.
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.
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.
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.
- The total number of days in August is 31.
- The number of weekend days between 1st August 2022 and 31st August 2022 is 8.
- The number of working days in August 2022 is 31 - 8 = 23.
You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection.How to calculate days between two dates in Excel excluding weekends and holidays? ›
If you'd like to calculate the difference between two dates while excluding weekends and holidays, use the NETWORKDAYS function instead. This also looks for 3 arguments: the start date, the end date, and optional holidays. Unlike the WORKDAY function, the NETWORKDAYS function does include or count the start day.How do you calculate business hours in Excel excluding weekends and holidays? ›
Use NETWORKDAYS. INTL just like NETWORKDAYS, except after the second date argument, place a comma and then enter a seven-digit string consisting of ones and zeros. The first digit represents Monday, and the last Sunday. Use a "1" to signify a weekend, and a "0" to signify a workday.Is there a formula to calculate business days in Excel? ›
Then, to calculate the number of business days in the specified time period, type the formula =NETWORKDAYS(B3,B4), where B3 is the starting date and B4 is the ending date.How do I calculate the number of days between two dates in Excel? ›
To find the number of days between these two dates, you can enter “=B2-B1” (without the quotes into cell B3). Once you hit enter, Excel will automatically calculate the number of days between the two dates entered. Note that Excel recognizes leap years.What is the WORKDAY formula in Excel? ›
The method to use this function in Excel is as follows =WORKDAY ( Start_date, Days, Holidays). The first two arguments are mandatory; however, the argument for holidays is optional. For example, =WORKDAY(A1,B1). A1-1/01/2020 (starting_date), B1- 45 days(days for completion)which gives 4-03-2020 in cell C1.How to add business days excluding weekends and holidays with formula? ›
Add business days excluding weekends with formula
To add days excluding weekends, you can do as below: Select a blank cell and type this formula =WORKDAY(A2,B2), and press Enter key to get result. Tip: In the formula, A2 is the start date, B2 is the days you want to add.
- @StartDate datetime = '2014-05-15 12:00:00.000',
- @EndDate datetime = '2014-05-19 13:03:00.000';
- @diff int = datediff(MINUTE, @StartDate, @EndDate);
- SELECT[days] = (@diff / 1440),
- [hour] = (@diff % 1440) / 60,
- [min] = (@diff % 1440) % 60;
Count days excluding Sundays with formula
Here I can introduce a formula for counting days excluding Sundays during a date range. Select a blank cell, here is C2, and type this formula =B2-A2-INT((B2-A2-WEEKDAY(B2)+1)/7) into it, and then press Enter key, a date displayed.
The optional parameter of holidays is entered as a range of cells, i.e., F2:F5. We apply the NETWORKDAYS formula “=NETWORKDAYS(A2, B2, F2:F5).” The output is 3, as shown in the succeeding image. Note: To enter holidays as text dates, follow the regional date and time settings of Excel.Do calendar days include weekends and holidays? ›
Calendar Day means the twenty-four hour period from midnight to midnight. Saturdays, Sundays and all holidays are considered calendar days. Calendar Day means any day, including Saturday, Sunday, and legal holidays.Does working days exclude weekends? ›
Working days exclude weekends and any dates identified as holidays.What is the total number of working days? ›
There are a total of 260 working days in the 2023 calendar year.