 Hey guys welcome to SSUnitex. See this side and this is continuation of SQL Server Real-Time scenarios. So this is part 3 and today we are going to see how we can get the last working day in a month and here we can notice one thing Saturday and Sunday will be the weekends and also we are having a holiday table. So inside the holiday table holidays are listed so we need to take care if the last working day is coming on the holiday then the previous day of that should be the last working day and if last day of the month will be coming inside the Saturday or Sunday then previous that Friday will be the last working day. So these scenarios we need to take care. So go to on the SQL Server management studio and where we will write the query for getting this last working day in a month. So basically here as you can see I have already written this query for creating the holiday list table and inserting the data on this table. So first let me try to execute this query to insert the data. So as you can see six rows vector. So in the holiday table we are having six rows. Let me try to select that. So select from this holiday list table and in this table as you can see for year 2021 month is first 29th of January was the holiday. So go to on the calendar and inside the calendar inside the calendar go to on the January. So as you can see 29th was holiday. So 29th was the Friday 31st is Sunday 30th is Saturday and 29th is the holiday. So the last working day for the month of Jan that should be 28. Now go to on the Feb. So in the month of Feb as you can see 27 and 28 is the Saturday and Sunday. So 26th will be the last working day. But as per the holiday 26th and 25th. So these two days are having the holidays. So here 26th and 25th will not be the last working day. The last working day that should be 24. So how we can write the query for getting this output. So for that first of all we need to declare few variables. The first variable that will be our input date variable and in this date variable we are going to pass the date of the month by which we want to get the last working day. So here let me try to put this as the month of Jan. Now the next variable that should be going to get the last day of the month. So that should be the end date and data type that should be date. And here as you can see end of month. So end of month function will be going to return the last working day for that particular month. So now we can select this at the rate n month end date. And here we can see the 31st that is okay. Now we need to declare one more variable and that variable will be going to have the last working day. So let me try to declare one more variable with the name of last working day and this should be date. Now it looks good. Here the first thing that we need to take care. We are going to calculate the last working day if we are having Saturday or Sunday. On that case we are going to set the Friday's date on this last working day. Now let me try to set at the last working day should be equals to. Here we need to use the case and inside the case when we need to use date part function. So date part function is going to return part of the interval that we are going to specify. In this case we are going to specify the date of the week. Now put common from this end date. So this time it is going to return the day of week and it is going to start from Sunday. So if at the rate end date is Sunday then it is going to return 1 and in case of Saturday that should be 7. So first of all let me try to check for 7. It means we are going to check for the Saturday. If we are going to get that then we need to subtract 1 day from this end date. So dd put comma minus 1 so Saturday previous day should be Friday. Now from this end date. So that looks good. The second thing that we need to check like when this date part this value of day of week end date is equals to 1 it means Sunday then we need to subtract 2 days on that. So date at here dd put comma minus 2 again put comma at the rate end date. In the else condition we are going to return the end date. Now here let me check this last working day. So select last working day. Now let me try to execute this query. So this time here it is showing the problem not use the end after this case statement. Now let me try to execute this. So this time it is going to return 29. Now go to on the calendar and go to on the Jan. So here because 31st was the Sunday so we have subtracted 2 so it directly jump to the 29. So that is why it is returning 29. But 29 is the holiday as we have already seen in the holiday list table. So for that we need to use a loop. So while exist we can use and here we need to check select one from this holiday list table and where date is equals to at the rate last working day. If this condition is going to satisfy it means the last working day that we have calculated from above query if it is having the holiday on that then we need to search at the rate last working day that should be previous day. So date add then dd comma minus one comma at the rate last working day. If this condition is going to satisfy it means if that day is available on the holiday list table then we are going to subtract minus one day over there. And again if that previous day is already exist in this table then again it is going to subtract minus one. So it is going to check previous to previous to previous where the date is not available in this holiday list table. So after that we can directly select at the rate last working day. Now let me remove this select and try to execute this one. So it is going to return 28 as we have already seen. Now let me try to change this to the second month. Now let me try to execute this query. So it is going to return 24. Now go to on the calendar and go to on the prep. So prep as we can see 27th and 28th is the weekend. So it will jump to the 26th from the above calculation and 26th and 25th. So these two are available in the holiday list table. So that's why it will move to the 24th. So 24th is the last working day. So as you can see right here it is working fine. But again we need to do one more thing. Let's assume if for the whole week we are having the holiday on this holiday list table then it will be going to jump on Sunday. But here after this calculation we are not going to check about the Sunday. So let me try to copy the above query as it is and try to paste it over here. And here instead of the end date here we are going to check on the last working day. And then we need to subtract from the last working day again. And here at the rate last working day after that at the rate last working day. Else at the rate last working day. Now let me try to execute this. So this will be our complete query. So as you can see it is going to return 24. So here like we have also captured that condition if we are having complete week as of it means if you go on this Feb. So here 20 from 26 to 22nd. So all these days are having the holidays then it will be going to return the 21st. And again we are going to check for 21st and 20th. So after that it is going to return 19. So 19 should be the last working day. But this condition is not very important because in any organization I did not see 5 day holidays. So that's why this might not be used. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. If you need this script I am going to provide this script on the description of this video. So you can directly go and copy this and try to do the practice on your own. Thank you so much. See you in the next video.