SQLTeam.com | Weblogs | Forums

Using Case When to Calculate a number of events


I am needing to calculate the AVERAGE number of times a customer has delivered an order against the amount of days an order has sat on the shelf . So with this table scheme, I am building a derived column to produce the calculation within this table structure

TABLE dbo.orders --o
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
Sourceid (pk uniqueidentifier NOT NULL),
Status varchar(50) null,
Stockarrived datetime NULL,
Ordershipped datetime NULL,
Deliveryconfirmed datetime NULL

I will need to evaluate only the past 7 days not including weekend days , and eliminate divide by zero instances :

SELECT coalesce (o.customerid,'GrandTotal') AS CUSTID

,[AVG SHELF DELIV] =COALESCE(convert(DECIMAL(5,1),sum(case when o.Status in ('ready','stock present')
and o.Status not in ('delivered') then 1 else 0 end))/
NULLIF(sum(convert(DECIMAL(5,1),case when o.Status = 'delivered and approved' and o.Deliveryconfirmed >= getdate()-7 and ((DATEPART(dw, o.Deliveryconfirmed) + @@DATEFIRST) % 7) NOT IN (0, 1) then 1 else 0 end),0) ,0) ,0) * .143


orders o

group by rollup (customerid)

is not bringing up accurate result
Thanks in advance