Rajiv is getting organization wide recognition for his newly acquired SQL skills which is helping him preparing ad-hoc reports much faster and in a convenient manner. 

Robert, the Customer Success Head has become his new fan and promoter of SQL skills at GlobalMart. He is looking to get few more ad-hoc requests addressed by Rajiv. So, he decided to drop him a mail. 

Let's read his mail below :

Hi Rajiv, 

Thanks for being such a valuable asset to GlobalMart. I can truly see the value SQL brings to the table. I believe it has amazing capabilities to analyze data faster and better. 

By the way, I've got some new reporting request for you. Hope you won't mind :)

Download the requested report format by client from here

Let me know by when can I have it. 

Regards,

Robert

Rajiv again had a familiar feeling that some of the date processing tasks is achievable via Excel. By now, he also knows that SQL must have something similar (at least in logic) to achieve. 

However, given the tight deadlines, he would seek some support from Sharon. So, he forwarded Robert's mail to her seeking her mentorship. 

Sharon, being a great support, accepted the invite and asked for a catch up next day. Let's walk through the conversation and discover how the requested report can be prepared using SQL. 

Rajiv : Hi Sharon, Thanks again for being such a great support. I wanted to have a conversation around the problem statement. If you can give quick directions, I'll try and figure out the code required to complete the task

Sharon : No worries Rajiv. I had a look at the mail. Shoot your queries :). 

Rajiv : All right. If you see the report, there seems to be a number of columns need to be derived. I've never written a big query requiring so many calculations/code. Feeling little nervous.

Sharon : Hold on. That's the first learning when preparing to write big SQL queries. You don't have to write them in one shot. Follow the rule of breaking down into simple problems, work them out first and then put the pieces together to complete your code. 

Rajiv : Right. This makes a lot of sense. So, let me explain my interpretation of different columns required : 

• month_year - This seems similar to using TEXT() function in Excel
• period - Looks like combining 1st and last dates of a month. Seems like some sort of adding/subtracting days
• is_weekend - Seems like IF..ELSE of Excel. Is there a WEEKDAY() function I can use?
• delivery_duration_1 - This looks like simple difference between dates
• delivery_duration_2 - No idea how to get this :(
• date_german_format - Excel has got some formatting features. How to do that in SQL?
• date_US_format - Same as above

Sharon : I'm glad you could relate so much with SQL. Don't worry, all of these are achievable by combining two things : Logical Thinking + Knowledge of Date manipulation functions in SQL. Check out the hint below to learn about popular date manipulation functions offered by SQL. I'm sure that'll give you lot of ideas
 

Rajiv : Thanks Sharon! This has tons of ideas for me to apply on the problem statement. Let me show you how I tried to derive the column month_year. Here's how it looks like : 

-- Step 1 : Extract month name from date and extract first three characters

select 
left(datename(month,order_purchase_date),3) as month_MMM
from orders

-- Step 2 : Extract year from date

select 
datename(year,order_purchase_date) as year_YYYY
from orders

-- Step 3 : Combine the two. I tried using + operator

select 
left(datename(month,order_purchase_date),3) as month_MMM,
datename(year,order_purchase_date) as year_YYYY,
left(datename(month,order_purchase_date),3) + '-' + datename(year,order_purchase_date) as month_year

Sharon : Your code looks perfect Rajiv. Congrats on cracking the code for column month_year. However, I'd encourage you to explore the FORMAT() function in SQL Server. It reduces your code a lot and also helps you the idea of TEXT() function in Excel straight away in SQL. 

Check out this article to understand more about usage of FORMAT() function for date formatting. 

Rajiv : Wow. Didn't know about that. Will surely explore it

Check the code below : 

select 
left(datename(month,order_purchase_date),3) + '-' + datepart(year,order_purchase_date) as month_year

The order_purchase_date passed in the above query is : 2018-08-12

Check the code below : 

select 
format(order_purchase_date,________) as result
from orders

The output is required in the MMM-YYYY format. Ex. if the date is 2018-08-12, the result required is Aug-2018

Check the code below : 

select order_purchase_date,
format(order_purchase_date,'dd-mm') as result
from orders

Sharon : All right. Let's move on the next column : period. What's your approach?

Rajiv : I now understand that somehow I have to use DATEADD() function. I am thinking hard how to get the 1st day of the month. 

Sharon : Psst! Try asking Google. Do your experiments. If hitting a roadblock, check the hint below

You need to extract the date of last Sunday from any given date. Ex. if the date supplied is '16-03-2022' the outcome should be '13-03-2022'. If the date supplied is '11-03-2022' then the output should be '06-03-2022'  Your colleague has come up with the below code :

select dateadd(DAY,datepart(weekday,'2022-03-15'),'2022-03-15')

However, this code is not giving the desired results. 

Rajiv : Interesting! However, I have a problem. I can only reach the 1st date one by one here. But, I have different datetime values in the order_purchase_date column. How do I get 1st date in one query straight?

Sharon : Logic my friend, Logic! Observe the pattern. Every time we are subtracting 1 less than the DAY value of the date. 

• Ex. If date is 03-06-2023, then we subtract 2 days which 3 - 1.
• If date is 05-06-2023, then we subtract 4 days which 5 - 1.
• If date is 10-06-2023, then we subtract 2 days which 10 - 1.

Rajiv : Hold on! I think I can do it by combining DATEPART, DATEADD and FORMAT. Let me give it a try. Check out my code below after some time. 

Rajiv : This was a great brain gym! Now, the next part, end of month. I could easily do that using EOMONTH() in Excel. 

Sharon : Ditto! Fortunately in SQL Server, we have the same function (Both Excel and SQL Server being Microsoft products). Go ahead, try and come back with your results. 

Rajiv : For sure! Let me work this out and show your the outcome. Check below for the full code for the column period after some time. 

Rajiv : Hi Sharon, Did you check my code?

Sharon : Yes I did. However, I have a challenge for you. Imagine EOMONTH() function wasn't invented yet. It actually didn't exist before SQL Server version 12. Think how would you fetch the end of month date in that case?

Rajiv : Very interesting. I'll give it a try

Rajiv : Hi Sharon, We have few more columns to calculate. Shall we quickly discuss them?

Sharon : Yes, let's do it. Now that you've done a good exercise using DATEPART, DATEADD, FORMAT functions, you should be able to come with an approach for a few

Rajiv : Yes, I have approaches for a few. Let me show you :

• is_weekend - I think we can use DATEPART() with weekday or dw parameter to fetch the numeric value of a given weekday between 1 and 7. After that it should be an easy CASE…WHEN

However, I have a question here. Which day does 1 signify here and what if I would like to set a different day (say Monday) as first day of the week? How to achieve that?

Sharon : Well, by default, the week is taken as Sunday to Saturday with Sunday labelled as 1. However read this post on Stackoverflow to understand how you can set 1st day of the week to a different day. 

Rajiv : Got it. Well, let me finish writing the code for the column is_weekend and get back. Do check out my code below after some time :)

Rajiv : Hi Sharon, Hope I got the code for is_weekend correctly?

Sharon : Indeed! It came out well. 

Rajiv : Let's try the next one. delivery_duration_1 seems to be a cakewalk. I think DATEDIFF will do the job. 

Sharon : Do you think so? Note that the result is not in exact days but days in fraction. 

Rajiv : Ohh! Ok, hold on. I was overthinking. This seems to be a simple case of subtracting order_purchase_date from order_delivered_customer_date, just the way we did in Excel :)

Sharon : Yup! Try it and let me know. 

Rajiv : Great. Let me come back. 

Rajiv : Hi Sharon, to my disbelief, such a simple calculation didn't give me the result I was looking for!

Check this Example : 

order_delivered_customer_date : ‘2018-05-11 20:07:00.000’

order_purchase_date : ‘2018-05-19 13:42:00.000’

Difference :  ‘1900-01-08 17:35:00.000’

Sharon : Hey, since both columns involved in your calculation has data type as datetime, the result too is returning in datetime. Think how will change the datatype from datetime to float (or decimal)?

Rajiv : CAST?

Sharon : You got it. But hold on. The calculation you did would only work for the orders which are already delivered. The orders which aren't delivered yet won't have the order_delivered_customer_date populated. How will you handle that?

Rajiv : I'll have to do a null check before taking the difference. If the order_delivered_customer_date is null then I'll return the outcome as null else will return the actual days difference.

Sharon : I'm sure you'll use CASE WHEN for null check. However, I'd also encourage you to explore ISNULL() function. Check the usage here. 

Finally, ensure that you keep the result with just 1 digit after decimal place. Try ROUND()

Rajiv : Hey, just had a query. Why should I even consider this? Shouldn't I filter out all orders which aren't delivered yet? It doesn't make sense to include them in the calculation of delivery duration.

Sharon : You are absolutely right. However, my intent here is to help you learn how to handle cases of null. I would rather encourage you finish your attempt by filtering out such records.

Rajiv : Great! Thanks for all the valuable inputs Sharon. Let me modify my code and come back with correct results

Rajiv : Hi Sharon. Did everything look all right in my code?

Sharon : Yes, Rajiv! A flawless one :) What next?

Rajiv : The column delivery_duration_2. Looks tricky where we need to represent something in float in the form of textual format. May be I am missing some function here. 

Sharon : You won't always have a ready made function for your rescue. Nothing beats your logical thinking muscle. Go to your basic math of time calculations. You'll crack it. 

Let's take the following case :

order_delivered_customer_date : ‘2018-05-11 20:07:00.000’

order_purchase_date : ‘2018-05-19 13:42:00.000’

delivery_duration (without any restriction of number of digits after decimal) : 7.73263888888889

Let's take till 2 decimal places (general standard) : 7.73 days. 

If you read this, you can say the result is 7 days + 0.73 days right? So, how will you extract 7 here?

Rajiv : I am not sure about this. But if the goal is to get days, I can easily do that using the DATEDIFF() function. 

Sharon : Hmm…. Good idea. Why not try it and see?

Rajiv : Let's try now only. Here's the code

select 
datediff(dd,order_purchase_date,order_delivered_customer_date) as days
from orders

Rajiv : Oops! It's giving 8 days not 7. 

Sharon : That's because it rounds off the results. If the actual difference is 7.12 days, it will round off to 7 and if it is 7.73 days, it will round off to 8. 

Rajiv : But, the right answer is 7. Should I subtract 1 from the result?

Sharon : You can do it. However, let me show you another approach. Look at the below code. See, if you can interpret it : 

Select
datediff(hh,order_purchase_date,order_delivered_customer_date)/24 as diff_in_days
from orders

Rajiv : So, here you are deriving difference in hours and dividing by 24 to get the result in days. But, why is the result not coming in fraction?

Sharon : That's because in this operation, the numerator is integer and the denominator is also integer. Hence, the result will also be integer. If you wish to have the result in float, at least one among the numerator or the denominator should be in float. Check the below change

-- Option 1
Select
datediff(hh,order_purchase_date,order_delivered_customer_date)/24.0 as diff_in_days
from orders

-- Option 2
Select
datediff(hh,order_purchase_date,order_delivered_customer_date)/(24 * 1.0) as diff_in_days
from orders

-- Option 3
Select
cast(datediff(hh,order_purchase_date,order_delivered_customer_date) as float)/24 as diff_in_days
from orders

-- Option 4
Select
datediff(hh,order_purchase_date,order_delivered_customer_date)/cast(24 as float) as diff_in_days
from orders

Rajiv : Understood.

Sharon : Good, so with this we get the 1st part of result : difference in days. How about the 2nd part - Hours?

Rajiv : May be we calculate 7*24 which we subtract from total difference in hours to get left over hours?

Sharon : Thinking is right. However, let me use this opportunity to teach you something interesting. Remember the days when you learnt division? There is quotient and there is a remainder. Now look at the below code :

Select
datediff(hh,order_purchase_date,order_delivered_customer_date)%24 as left_over_hours
from orders

Rajiv : This results in 17 which is the remainder when 185 (Difference in hours in the above example) is divided by 24. Is the % symbol doing this?

Sharon : Yes. That's popularly called the modulo operator in mathematics. 

Rajiv : This is so intuitive!! I couldn't imagine I'd use primary school math today in SQL. 

Sharon : Amazing isn't it? That's the beauty of logical thinking and SQL. Now, only thing left is getting the minutes part. 

Rajiv : Hold on. I am getting an idea. I am trying the below code :

Select
datediff(mi,order_purchase_date,order_delivered_customer_date)%60 as left_over_min
from orders

Sharon : You've nailed it perfectly. Can you explain what you did?

Rajiv : I calculated the difference in minutes. Then divided by 60 to get only the remainder minutes. 

Sharon : I am so happy! So, can you complete the code now?

Rajiv : Yes. Here you go : 

select
order_purchase_date,
order_delivered_customer_date,
-- Delivery duration in days (using dd).This may give wrong results in this specific -- case
datediff(dd,order_purchase_date,order_delivered_customer_date) as diff_in_days,
-- Delivery duration in fraction of days
datediff(hh,order_purchase_date,order_delivered_customer_date)/24.0 as diff_in_days_frac,
-- Delivery duration in fraction of days (correct result for this case)
datediff(hh,order_purchase_date,order_delivered_customer_date)/24 as diff_in_days_correct,
-- Calculating the hours part
datediff(hh,order_purchase_date,order_delivered_customer_date)%24 as left_over_hrs,
-- Calculating the minutes part
datediff(mi,order_purchase_date,order_delivered_customer_date)%60 as left_over_min,
-- Concatenating the results together
cast(datediff(hh,order_purchase_date,order_delivered_customer_date)/24 as varchar) + ' Days ' +
cast(datediff(hh,order_purchase_date,order_delivered_customer_date)%24 as varchar) + ' Hours '+
cast(datediff(mi,order_purchase_date,order_delivered_customer_date)%60 as varchar) + ' Minutes' as final
from orders
where order_delivered_customer_date is not null

Sharon : Dear Rajiv, You made my day! Enjoy SQLing

Rajiv : Thanks a ton Sharon. Without you this would be impossible. Thanks for being such a great mentor. I'll quickly wrap up the code for the last two columns. That should be easy. 

Sharon : Without doubt. Best of luck my friend :)

Check the following code : 

select
order_purchase_date,
order_delivered_customer_date,
datediff(hh,order_purchase_date,order_delivered_customer_date)/24 as x,
datediff(mi,order_purchase_date,order_delivered_customer_date)/(24*60) as y,
datediff(mm,order_purchase_date,order_delivered_customer_date)/(24*60) as z
from orders

Check the code below :

select
order_purchase_date,
order_delivered_customer_date,
datediff(mi,order_purchase_date,order_delivered_customer_date)%60 as w,
datediff(hh,order_purchase_date,order_delivered_customer_date)/24 as x,
datediff(hh,order_purchase_date,order_delivered_customer_date)%24 as y,
datediff(mi,order_purchase_date,order_delivered_customer_date)%(24*60) as z
from orders