February 24, 2017 at 3:45 pm
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
February 25, 2017 at 5:13 am
We have the table DDL, which is great, but we don't have any sample data. Simply telling us that the result you get isn't accurate doesn't give us much information, as we don't know what value that calculation provided, or what it should.
Perhaps you could provide some sample data (with INSERT statements), and then what you want your output to be from that data. It'll be much easier for one of us to help you then.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 25, 2017 at 6:48 am
I see your query failed a syntax check; when i reformatted it, i see you got three trailing ,0), as if they were part of NULLIF/COALESCE, but you need only two,.
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
)
)--sum
,0)--nullif
,0) --HIGHEST coalesce
* .143
from
orders o
group by rollup (customerid)
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply