Help with complex SQL query

[hullabaloo]

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:

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