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 ) :

Customer
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.
I_J_EvansAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

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
select
  t.customer
  , count( t.orderNo )
  , sum( dbo.isShippedOnTime( t.OrderNo )) as OrdersShippedOnTime
  , sum( 1 - dbo.isShippedOnTime( t.OrderNo )) as OrdersNotShippedOnTime
from myTable t
where
  t.OrderCreatedDateTime between @StartDate and @EndDate
group by
  t.customer

simple function
create function dbo.isShippedOnTime( @OrderNo integer )
returns bit
as
@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

HTH
  David




if (select t.createDateTime from myTable t
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
I_J_EvansAuthor Commented:
Thanks for the idea of the fucntion , we have succefully build one now that meets what we need to do.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.