Help with SQL for Report Looping/Grouping ???

[BFG 9000]

Hi All,

I have a query that shows what percentage of orders we've shipped in less than 2 working days (i.e. ignoring weekends) - which is working perfectly, but I have to specify the year & month.

I'd like some help in creating one query which outputs this data by month for the past 2 years.


Here's the query - you'll see that this is specifying last month only...

SELECT CAST(100*(

SELECT CAST(COUNT(OrderNumber) AS decimal(8,2))
FROM Orders WHERE YEAR(OrderDate) = 2011 AND Month(OrderDate) = 10 AND (TransactionState= 'AUTHORIZED' OR TransactionState= 'CAPTURED') AND CAST(DATEDIFF(d,OrderDate,ShippedOn)+0
- (DATEDIFF(wk,OrderDate,ShippedOn) + CASE WHEN DATEPART(dw,OrderDate)=1 then 1 else 0 End )
- (DATEDIFF(wk,OrderDate,ShippedOn) + CASE WHEN DATEPART(dw,ShippedOn)=7 then 1 else 0 End ) AS decimal) < 2

) / (

SELECT CAST(COUNT(OrderNumber) AS decimal(8,2))
FROM Orders WHERE YEAR(OrderDate) = 2011 AND Month(OrderDate) = 10 AND (TransactionState= 'AUTHORIZED' OR TransactionState= 'CAPTURED')

)
 AS decimal(8,2)) 

Can anyone help?



TTFN

BFG