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
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
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
from
orders o
group by rollup (customerid)
is not bringing up accurate result
Thanks in advance