It’s Monday morning and the Operations director has sent another email asking for some more critical data. His request is a bit odd but the email reads:

“Jim,

I need to see Sales Orders from the start of time. I want a column to indicate if  the order was shipped by the due date. Maybe a Yes or No, just use your imagination. We have heard several complaints from customers on orders being shipped past the due date and the executives are on my back. I want to be able to create a pivot table with this data and compare overtime. Send it to me as soon as you get in!

Scott”

As soon as we get some coffee, we start tackling Scott’s request. To begin, let’s break it out into two steps. We know the table we will be using is Sales.SalesOrderHeader in the AdventureWorks2012 database. You can download the AdventureWorks2012 database by clicking here.

Given the email,  we will be pulling the SalesOrderIDShipDate and Duedate. Now if this was something more than an ad-hoc request we could create a VIEW to pull it in the future. However, we will operate on the assumption that Scott only needs the data this one time.

 

The above query will give us the basic data we need, except we are missing the most important part. Scott requested an indicator of whether the order shipped after or before the due date. For this critical piece of data lets use the SQL CASE function.

 

The query results are below.

SQL CASE

The data above should be what Scott is looking for. Please keep in mind the <= operator could be up for debate; we would definitely want to communicate with Scott the logic we used in the query.  Now we can run the results to a spreadsheet, send to Scott and await his next request.

Like this post? Share it!