Help with complex SQL query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 1 of 1

Thread: Help with complex SQL query

  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Help with complex SQL query

    Hi,

    I've got a complex-ish query which I need a bit of help with optimising. I've actually got it got it working already, but I'm sure there must be a better way of doing it than this.

    What we need is a report that tells us how many orders we've had placed for each delivery type over a given period of days. There are 4 types of delivery - Standard, Premium, Next Day and Saturday. And each day has three shifts that we need to break the report up into : 8:00am-5:00pm,5:00pm-2:00am, and 2:00am-8:00am.

    So our report should look something like this :

    http://thegrapevine.brinkster.net/delivery.jpg

    So given any two start and end dates, we need a stored procedure that will return a resultset like the one above. The results are then formatted and output using a JSP page.

    The way I've got it working at the moment is, my stored procedure will return the results for a single day only, and this procedure then gets called 'x' number of times for each of the days between 'start' and 'end' date. But I don't think this is very efficient...?

    Here is roughly what my stored procedure does:
    Code:
    select @startdate + ' 08:00:00.00' Placed, -- for 8:00-5:00
    
    (select count(*) from OrderHeader 
    where Placed between @startdate + ' 08:00:00.00' and @startdate + ' 16:59:59.00' and CarriageMethod = 's') Standard, 
    
    (select count(*) from OrderHeader 
    where Placed between @startdate + ' 08:00:00.00' and @startdate + ' 16:59:59.00' and CarriageMethod = 'p') Premium, 
    
    (select count(*) from OrderHeader 
    where Placed between @startdate + ' 08:00:00.00' and @startdate + ' 16:59:59.00' and CarriageMethod = 'n') NextDay, 
    
    (select count(*) from OrderHeader 
    where Placed between @startdate + ' 08:00:00.00' and @startdate + ' 16:59:59.00' and CarriageMethod = 't') Saturday, 
    
    union 
    
    select @startdate + ' 17:00:00.00' Placed, -- for 5:00-2:00
    
    (select count(*) from OrderHeader 
    where Placed between @startdate + ' 17:00:00.00' and dateadd(dd, 1, (@startdate + ' 01:59:59.00')) and CarriageMethod = 's') Standard, 
    
    (select count(*) from OrderHeader 
    where Placed between @startdate + ' 17:00:00.00' and dateadd(dd, 1, (@startdate + ' 01:59:59.00')) and CarriageMethod = 'p') Premium, 
    
    (select count(*) from OrderHeader 
    where Placed between @startdate + ' 17:00:00.00' and dateadd(dd, 1, (@startdate + ' 01:59:59.00')) and CarriageMethod = 'n') NextDay, 
    
    (select count(*) from OrderHeader 
    where Placed between @startdate + ' 17:00:00.00' and dateadd(dd, 1, (@startdate + ' 01:59:59.00')) and CarriageMethod = 't') Saturday, 
    
    union 
    
    select dateadd(dd, 1, (@startdate + ' 02:00:0.00')) Placed, -- for 2:00-8:00
    
    (select count(*) from OrderHeader 
    where Placed between dateadd(dd, 1, (@startdate + ' 02:00:0.00')) and dateadd(dd, 1, (@startdate + ' 07:59:59.00')) 
    and CarriageMethod = 's') Standard, 
    
    (select count(*) from OrderHeader 
    where Placed between dateadd(dd, 1, (@startdate + ' 02:00:0.00')) and dateadd(dd, 1, (@startdate + ' 07:59:59.00')) 
    and CarriageMethod = 'p') Premium, 
    
    (select count(*) from OrderHeader 
    where Placed between dateadd(dd, 1, (@startdate + ' 02:00:0.00')) and dateadd(dd, 1, (@startdate + ' 07:59:59.00')) 
    and CarriageMethod = 'n') NextDay, 
    
    (select count(*) from OrderHeader 
    where Placed between dateadd(dd, 1, (@startdate + ' 02:00:0.00')) and dateadd(dd, 1, (@startdate + ' 07:59:59.00')) 
    and CarriageMethod = 't') Saturday
    Is there a better way of writing this where I can just pass the start and end dates to the stored procedure and it returns all the results in one go, rather than having to recursively call it for each day in turn? I am using Sybase, BTW.

    Sorry for the length of the post, I know it's a bit fiddly.

    Many thanks in advance for any help

    Nick
    Last edited by Hack; 06-10-2009 at 07:54 AM. Reason: Added Code Tags

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 04:14 PM
  2. Complex class won't compile
    By Mcody2 in forum Java
    Replies: 7
    Last Post: 12-01-2005, 11:29 PM
  3. Replies: 2
    Last Post: 05-24-2005, 04:50 PM
  4. SQL Query Fails on NT System
    By Ken D. in forum VB Classic
    Replies: 2
    Last Post: 09-03-2002, 11:25 PM
  5. how to write this SQL query and SQL assertion
    By Diana Perkins in forum Database
    Replies: 2
    Last Post: 11-22-2000, 09:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center