• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

SQL query - Shipped on time report

I am required to build a query that allows me to provide details relating to Order shipment information :
Here is the table :
Order No ,  Customer , order created( date time )  , order shipped ( date time )

From this simple table is it possible to get the following information  ( grouped by customer ) between to dates ( Order created date > blah and order created date < blah ) :

Number of Orders
Orders shipped on time
Order not shipped ontime
Percentage shipped on time ( number shipped on time / number of orders )
Percentage not shipped on time ( number not shipped on time / number of orders )

To classify as shipped on time : if the order is created before 4.00 pm it must be shipped same day before 4.00 pm , if how ever  the order is created after 4.00 pm the order is required to be shipped before 4.00 next day. When calculating - Saturday and Sundays are not to be considered and the following dates ( holidays ) must also not be considered ( 01/01/2008 , 03/02/2008, 04/01/2008  US date format )

Any other order that does not meet this criteria is deemed to be NOT shipped on time.
  • 2
1 Solution
David ToddSenior DBACommented:

The shipped on time thing could be complex to calculate. I think that you might need to build a calendar table, and create a function to return 1 -- yes, or 0 -- no called isShippedOnTime

But the core code is something like this
  , count( t.orderNo )
  , sum( dbo.isShippedOnTime( t.OrderNo )) as OrdersShippedOnTime
  , sum( 1 - dbo.isShippedOnTime( t.OrderNo )) as OrdersNotShippedOnTime
from myTable t
  t.OrderCreatedDateTime between @StartDate and @EndDate
group by

simple function
create function dbo.isShippedOnTime( @OrderNo integer )
returns bit
@declare @Create datetime
@declare @Shipped datetime

select @Create = t.CreateDateTme, @Shipped = t.ShippedDateTime
from dbo.myTable t
where t.OrderNo = @OrderNo

if datediff( day, 0, @Create ) = datediff( day, 0, @Shipped ) -- shipped same day
  return 1

if @Create > dateadd( hour, 16, dateadd( day, datediff( day, 0, @Create ), 0 )) -- order after 4pm
  if datediff( day, 0, @Create ) = datediff( day, 0, @Shipped ) - 1 -- shipped next day
    return 1

if datepart( weekeday, @Create ) = 6 -- friday
  if datediff( day, 0, @Create ) = datediff( day, 0, @Shipped ) - 3 -- shipped monday
    return 1

-- holiday calculation to do here ...
return 0


if (select t.createDateTime from myTable t
I_J_EvansAuthor Commented:
There will be some order sthat will not be picked up in this - I wil have to think about it and get back to you. Thanks for the direction in creating a specifc fucntion for this type of calculation
I_J_EvansAuthor Commented:
Thanks for the idea of the fucntion , we have succefully build one now that meets what we need to do.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now