 Hey guys, welcome to SSU Tech. So, we'll decide and this is continuation of SQL Server interview questions and answers. So, today we are going to see about one more SQL query related interview question. So, let's move to next slide to understand about that question. So, basically here in a library, if any person will take the book from the library, it will take the charges like if anyone take the book for between 1 to 30 days, it will be going to charge $0.5. If it will be more than that between 30 first to 60 days, then it will be $0.75 for each day. If the day that will be more than or equals to 61, then it will be going to charge $1.5 on each day. So, here basically you need to write a SQL store procedure that will take three parameters. First will be the from date, second will be the to date and third will be the book price. If your charges will be more than the book price, then it will show only book price and output will have only two columns. First will be the total days and second will be the charges. So, let's assume here we are having an example like if your from date is 2020-01-01 and to date that will be 2020-0-15 and your book price is $15. So, here output should be 15 because charges will be 26.25. How we can calculate that? So, here as you can see inside the note like the total days that is 45 days. So, the charges will be going to applicable up to 30 days that will be $0.5 and between 30 first to 60 days that will be $0.75. So, here in the 45 days first 30 days it will be going to charge on the $0.5 and next 15 days will be going to charge on the $0.75. So, it will be going to calculate like this. So, go to other SQL Server and let's try to write the store procedure and where we can achieve this output. So, here I am using SQL 2017. You may have another version of SQL Server but this query will help you on the all the versions of SQL Server. So, here first of all for creating any store procedure you have to write the create statement. So, the create then proc after that you need to specify your procedure name. So, that will be part 64 of this video series. So, I am going to call this as part 64. Then as your question is saying like we should have three parameters. So, first will be the from date. So, this is the from date and data type of this will be the date. Then we will have next one that is the true date and data type that is date. Then last will be the book price. So, we can call this as book price and this should be integer. And here we can see as then we could have begin and at the last it will be end. So, now the first thing that we need to take care we need to calculate the difference between the true date and from it. So, how we can calculate that? So, first of all let me declare a variable. So, declare at the rate total days and it should be integer. So, here we need to use the date difference and start the bracket. Here we need to get the difference on days. So, we can call this as dd then put comma. Here we need to specify from date. So, this is your from date then put comma last that will be true date. Now, we can close the bracket. So, here we have calculated the total days. Now, the logic will come on the next. So, here first of all let me declare one more variable and that will be the total charges. So, how we can calculate the total charges. So, this is very important and you need to understand. So, first of all we need to use the case statement. So, case when? Now, we are going to check this total date difference. If it is more than or equals to 61 then we need to have the calculation that will be the first one. Let me check the value of this total days. If that value is greater or equals to 61 then the first 30 days will be going to calculate on the point five dollars plus next 30 days will be going to calculate on the point seven five dollars then plus here we need to get how many days are remaining. So, how we can calculate that. So, for that we need to get the total days minus 60 because here we have this 60 for the first 30 days then next 30 days. So, we need to subtract the 60. So, it will get the how many days are remaining then multiply this with the 1.5. So, we have done if your total days is more than or equals to 60. So, not 60 it should be 61. Now, go to the next condition and in the next condition we need to check when the total day greater or equals to 61 then here is will be going to lie between 31 to 60. So, for that first will be same like the first calculation of 30 days will be multiplying with the point five plus here we need to get how many days are remaining. So, for that we can copy this start the bracket subtract with the 30. So, now we will get the how many days are remaining we can multiply with 0.75. Now, the last either you can check with the when condition or you could use the else condition and in the else condition it should be going to lie less than 30 days. So, we can multiply with the 0.5 directly and we could say like and. So, this is the calculation. So, by using this calculation we can directly achieve the output of the total charges. Now, it looks good. Now, here we need to select total days call this as total days.com. Now, here again we need to check the charges value if this charges value is more than the book price then we need to display the book price. So, here we need to use again case when your book price value is less or equals to add the total charges then we need to display the book price. So, here it is the book price else we can display the total charges and we can end with this case statement and let me call this column as charges. So, this is the charges. Now, we can execute this. So, the store procedure is created successfully. Now, let me copy this and try to execute this store procedure and check the output. So, for that we can write the execute then we have three parameters the last one will have the 15 in the from date we need to specify 2020 0 1 0 1 in the second parameter 2020 0 to 15 and in the last parameter that is 15. Now, we need to execute this store procedure. So, in the output you could see we are seeing like the total days that is 45 and the charges that is 15. Now, let me change this with the 50 of this book price. Now, let me try to execute. So, this time we should be able to see this calculated value that will be 26.25. So, let me try to execute and here you could see 26.25. So, it is working fine as per our expectation. So, guys, I hope you have understand how you can achieve this output by writing the store procedure with the three parameters and if you need this script it will be available on the comment box of this video. So, you can go and check the comment. 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. See you in the next video.