Thread: Help with SQL for Report Looping/Grouping ???

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...

C#/VB.NET Code:

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

Something like this:

Code:

select t1.theyear, t1.themonth, t1.thecount, t2.theorders
from (
select year(orderdate) as theyear, month(orderdate) as themonth, count(*) as thecount
from orders (nolock)
where (transactionstate IN ('AUTHORIZED','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 int) < 2
group by year(orderdate), month(orderdate) ) as t1
join (
select year(orderdate) as theyear, month(orderdate) as themonth, count(*) as theorders
from orders (nolock)
where (transactionstate IN ('AUTHORIZED','CAPTURED'))
group by year(orderdate), month(orderdate)) as t2
on t1.theyear = t2.theyear and t1.themonth = t2.themonth
order by 1 desc, 2 desc

Thanks Mate - that is awesome !!!



TTFN

BFG