# Using Case When to Calculate a number of eventsRSS

Last post Mar 03, 2017 05:45 AM by Chris Zhao

• qwester

None

0 Points

29 Posts

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

• Chris Zhao

All-Star

17612 Points

3510 Posts

### Re: Using Case When to Calculate a number of events

Mar 03, 2017 05:45 AM|Chris Zhao|LINK

Hi Qwester,

Please provide sample data in the table and your desired output, then it's likely that you will get a quick pointer or solution to your problem.

Best Regards,

Chris