 Hey guys, welcome to SSUnitex. So, see this side and this is continuation of SQL Server real-time scenarios. So, today we are going to discuss about a function. So, this function is very important while we are going to use this function in the real-time scenario inside the store procedures or separately. So, what is the purpose of this function and how we can create that will be going to see in this video. So, let's get started. So, here basically as you can see you need to create a function with the following details. The first is like it would have three parameters. First parameter will be going to have the start date, then end date and third parameter will be date type. So, date type indicates either we want to see the data on the daily basis, second on the weekly basis and third on the monthly basis. So, zero for the daily, one weekly and two four monthly and second one like what we want in the output. So, in the output we want to have only two columns. First will be the start date and after that end date. So, for example, if we are going to pass zero, so zero indicates for the daily. So, if the start date is going to start with the 20, 21, 0801 and end date would be 20, 21, 0805. So, in the output you could see we are having five rows. In the start date that will be 20, 21, 0801, then in the end date that will be the same. So, it is going to repeat on the daily basis like 1, 2, 3, 4 and 5. So, while we are going to pass date type as zero, then we want to see this output. Go to the next one. So, if we are going to pass date type as one, so that indicates for the weekly. So, it will always start with the last Monday to the next Sunday. So, what it means? So, as you could see inside the example, so start date is the 20, 21, 0801 and in the end date that would be 20, 21, 08, 16. So, here basically the start date in the output you could see it is 26, 7, 2021. So, go to on the calendar and inside the calendar if it jump to the August. So, inside the August you could see it is having the Sunday. So, once we are having Sunday at the start date then it will always pick the last Monday. So, last Monday was 26. So, here you could see 26. So, first entry should be 26 to 1 and after that it is going to start with 2 to 8 then 9 to 15 and here end date is 16. So, 16 is Monday. So, that is why next would be 16 to 22. So, this is the output that we are expecting in case of weekly data. Now, go to on the next one. So, here the date type is 2. So, 2 indicates for the monthly. So, it will always start with the first date of the month and end with the last date. So, here basically as you could see inside the example. So, start date is 2021, 0801 and end date would be 2021, 11, 16. So, here in the output you could see it is going to start with the 2021, 0801 and end date would be 2021, 08, 31. So, it is going to repeat until your end date is not going to meet. So, for example, here I have written 11, it should be 01. So, the start date that should be 2021, 11, 01 and end date would be 2021, 11, 30. So, this is the requirement and how we can achieve this. So, for that we need to write a function. So, whenever it is required inside the store procedures or inside the separate queries then we could directly use that function and we can get the output. So, this is very important function and I have used this function many times in the real time. So, go to on the sequence over and we will see how we can create that function. So, here first of all we need to write the create function and after that the function name. So, in my practice I would always start with the fn underscore. So, fn underscore get complete date. So, this is the function name and it would have three parameters. So, first will be the start date, end date and date type. So, we need to specify. So, here it is having the start date and it should be date type and then we have the end date, this is again the date type and third will be have date type. So, this will be the integer type. So, this is our input parameter. Here returns and the variable name. So, variable names could be dates and the type. So, type will be table because it is having two columns with the start date and end date. So, here we will have the start date with the date type and end date with the date type. So, we are good with this. Now we can use the begin and end. Now we need to start our logic. So, first I need to declare a variable and this variable could be going to indicate with the end date and this will be the temporary variable and the data type that should be date. Now the first thing that we need to check if our input parameter that is date type, if the value of date type is equals to 2. So, it means we are checking for the monthly. Now we need to use the begin and end and in between we need to write the logic to insert the data on this dates table. So, here we need to use a loop. So, that loop is going to check if your start date value is less than or equals to your end date value. Then this loop will be going to start and in between we need to insert. So, we can write the insert statement on this dates table and of this start date and end date. For the values we need to write the select and after that here we need to focus one thing. So, if we are going to pass any start date which is in between of the month like not the start date of that particular month. So, first we need to calculate the first day of that particular date. So, how we can do that? So, for that first we need to check the end of month of this start date. So, this will be the end of month of that start date. Then we need to add one day on this then it will be going directly jump to the next month first date and then we can subtract one month. So, that will be the current month first date. So, that logic we need to do. So, here we can write the date add function and after that we could add one day on this particular date. So, this will be the next day first month. Now, we need to subtract one month on this. So, we can use the date add month minus one put comma. So, this will be the first day of current month and this will be have the start date put comma. Now, for the end date we can use the end of month and here we can have the start date. We can call this as end date. Here we need to close the bracket and it should not be here. Now, it looks good. So, we are going to insert the data of this. Second, here we need to unset your start date value. So, how we can do that? So, we can set add the start date is equals to date add and we want to add one month on this start date. So, that looks good. So, what it is doing? It is basically inserting the data and after inserting the data on particular month it is directly jump to the next month and if the next month is less than or equals to your end date then the data will be inserted for that month as well and going forward it is repeated that. So, now we have done for the monthly. Second, we need to check for the weekly. So, for the weekly need to more focus. So, let me try to start. So, if add the date type is equals to one it means we are checking for the weekly. Here we can have the begin then add. In between we need to write the logic. So, again first we need to check while your start date is less than or equals to your end date. So, this loop will be executed and in between we are going to insert a data on the dates table. So, here basically we need to check what will be the start date and what will be the end date. So, first I am going to check for the start date. So, first instead of inserting the value we need to set this start date value is equals to we need to check what is the current date we are having. So, case when we can have the date part function and inside the date part we are going to get day of week. So, for that we could have the dw then start date of the function. If this value is going to return as one. So, it means it is Sunday because your day of week is going to start with the Sunday. On that particular scenario we need to subtract six days on this start date. So, we can use the date add then dd comma minus six comma start date. So, it looks good if this is not the case we are not in the Sunday. So, on that particular scenario what we need to do we need to subtract whatever the value we are getting from the weekday and adding two on that. So, what it means let me write the query so you can understand easily. So, we can use the date add and after that date add we can add the dd put comma I am saying minus whatever the value of this date part we are getting from this weekday of this start date. We need to subtract that value of this start date and we need to add two right here. So, once you will be going to execute this so you will understand why we have added two. So, this we have done for the start date similar kind of thing we need to do for the end date. So, we have declared a variable that is end date temp. Here again we need to check case when your date part of this dw is equals to start date if that value is equals to 1. So, it means this is your Sunday. So, in the end date we could directly have the Sunday. So, we can directly return the start date in the end date. If this is not the case so on that particular scenario we need to subtract whatever the value we will be getting on this date part dw start date then subtract that value and add eight days on that. So, what it means so for that particular again we need to use the date add and after that we can have the dd put comma minus date part and here we have the weekday then start date close the bracket and the last parameter so that is start date. So, we have this start date now it looks good everything is okay but here like we are subtracting this but we need to add eight right here. So, that looks good and this will be your end date now we can directly insert the value on the dates table and we can select your start date comma your end date tmp. So, after setting all these here what we need to do it is showing error because we did not use the and a keyword right here. Now we need to set your start date of the next week. So, set add the rate start date because we have inserted the value of that particular week. So, we could directly use the date add keyword with the week and one. So, from this start date and this would be written after this. Second, we need to check for the end date and set the value on that particular end date. So, here we need to check case when we need to check the date difference. So, if the date difference value of this dd from the start date to the end date between one to six then we could return start date otherwise we could return end date and that is it for this. So, this is the first case and one end should be here not there and this is for the second one. So, it should be here the last case is remaining for the daily one. So, if add the rate date type value is equals to zero, it means we are checking for the daily case. Here we could use the begin end and in between we can directly insert the value on this dates table and we can select add the rate start date comma add the rate start date. So, this is for the end date and the first one which is for the start date. So, next we need to set add the rate start date value is equals to add one day on this. So, dd comma one comma add the rate start date. So, now everything is okay and here we need to use the written keyword. So, this function is ready. So, you need to more concentrate on this weekly case like on the daily case and monthly case seems to be very straightforward but this weekly case seems to be difficult. So, let me try to execute this and here let me copy this go to on the new query window and select a stick from this function it is expecting three parameters. So, let me put all those 0801 2021 08 16. So, let me try to execute for zero. Here we missed one thing we did not use the loop. So, loop should be required. So, while add the rate start date is less or equals to add the rate end date then begin and here and let me alter this and try to execute it again. Go here and execute this. So, now it is going to reflect the value from one to 16 for the daily one. So, that looks good. Now go to on the weekly basis. So, weekly it is going to start with the 26 to 1 then 2 to 8 then 9 to 15 and 16 to 22. So, it seems good and let me check for the monthly basis. So, here let me try to execute. So, it is like 1 to 31 then 1 to 30 1 to 31 1 to 30. So, it is working as per our expectation. If you guys have any doubt then you can drop your comments on the comment box. So, if you still have the doubt in case of weekly. So, I will try to record a separate video where we will see about the weekly. So, thank you so much for watching this video. If you really 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. See you in the next video with interesting fact.