asked on

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.

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

I_J_Evans

ASKER

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_Evans

ASKER

Thanks for the idea of the fucntion , we have succefully build one now that meets what we need to do.