Trying to find the percentage of on time shipments

Question

Post reply

Trying to find the percentage of on time shipments

mjakachira

SSC Veteran

Points: 215
More actions
January 22, 2016 at 7:07 am

#307911

I have two metrics Promise ship date and Ship date and I am trying to find out the percentage of on time shipments.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

untested, but this might give you an idea.

percentage is # ontime / Total,typically limited by a period of time (a month?)

due to integer division in SQL, you need to convert values to decimals to get a decent percentage;

i also took into consideration null values, and multiplying by 100 to get a value end users might expect to see, instead of 0.80111 or something.

SELECT CONVERT(decimal(5,2),(ShippedOnTime * 1.0) / (NULLIF(TotalOrdersShipped * 1.0,0)*100)) As PercentageOnTime,*

FROM (

SELECT

SUM(CASE WHEN PromiseShipDate > ShipDate THEN 1 ELSE 0 END) As ShippedOnTime,

COUNT(*) As TotalOrdersShipped

FROM Orders WHERE PromiseShipDate BETWEEN '2015-01-01' AND '2015-01-31'

) MyAlias

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Y.B. SSChampion Points: 11649 More actions · Answer 2

Providing sample data would have gone a long way for your cause. That being said I threw this together but since you have so little information I'm not sure if it's what you are looking for.

DECLARE @myTable TABLE (PromiseDate DATETIME, ShipDate DATETIME)

INSERT INTO @myTable

VALUES ('2016/01/05','2016/01/05'), ('2016/01/06','2016/01/08'), ('2016/01/08','2016/01/08'), ('2016/01/10','2016/01/13')

SELECT

CONVERT(NUMERIC(4,2),((SELECT COUNT(ShipDate) FROM @myTable WHERE ShipDate > PromiseDate) * 1.0 / (SELECT COUNT(ShipDate) FROM @myTable)) * 100) AS OnTimePercentage

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 3

yb751 (1/22/2016)
Providing sample data would have gone a long way for your cause.

Remember that most people don't know that the best way to do that is to create a test table and populate it. I recommend citing an article on the subject of "readily consumable data". For example, see the two links in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

A couple quick cautionary points:

1) Lowell's idea works nicely for this, just be careful with implementation since the scope of the *100 is incorrect. As written it multiplies the denominator by 100, and only then does the division. In other words, if you have 3 orders that shipped on time and 5 total orders, instead of doing 3/5=0.6, then multiplying by 100 to get 60 for percent shipped on time, it does 5*100=500, and then 3/500, which gives an incorrect result. Fixing that is simple, though, and he did warn that it was untested. 🙂

2) yb's solution is also an idea that could work, but as set up it's actually calculating the opposite of what you want, since the numerator for the division is calculated based on ShipDate>PromiseShipDate, meaning the order shipped after the promise date. If that is reversed then it will give correct results. Be careful, though, since that solution will scan the Orders table twice, once for each subquery, while Lowell's should be a single scan.

3) For any of these solutions, be mindful of how you want to treat orders with a ShipDate=PromiseShipDate. If that is considered on-time, then the PromiseShipDate>ShipDate comparison should be PromiseShipDate>=ShipDate in each example.

Here's quick example illustrating the first two points:

CREATE TABLE #Orders (PromiseShipDate DATE, ShipDate DATE);

INSERT INTO #Orders (PromiseShipDate,ShipDate)

VALUES ('20151230','20151229'),

('20151230','20151231'),

('20151115','20151110');

--Scope of the *100 is incorrect

SELECT CONVERT(decimal(5,2),(ShippedOnTime * 1.0) / (NULLIF(TotalOrdersShipped * 1.0,0)*100)) As PercentageOnTime,*

FROM (

SELECT

SUM(CASE WHEN PromiseShipDate > ShipDate THEN 1 ELSE 0 END) As ShippedOnTime,

COUNT(*) As TotalOrdersShipped

FROM #Orders

) MyAlias;

--Same idea, but with the scope of *100 fixed.

SELECT PercentageOnTime=(CONVERT(decimal(5,2),(ShippedOnTime * 1.0)) / NULLIF(TotalOrdersShipped * 1.0,0))*100, TotalOrdersShipped,ShippedOnTime

FROM (

SELECT

SUM(CASE WHEN PromiseShipDate > ShipDate THEN 1 ELSE 0 END) As ShippedOnTime,

COUNT(*) As TotalOrdersShipped

FROM #Orders

) MyAlias;

--Mixed up, calculates percentage of late shipments.

SELECT

CONVERT(NUMERIC(5,2),((SELECT COUNT(ShipDate) FROM #Orders WHERE ShipDate > PromiseShipDate) * 1.0 / (SELECT COUNT(ShipDate) FROM #Orders)) * 100) AS OnTimePercentage;

--Fixed, but less efficient than the first method, requiring two scans.

SELECT

CONVERT(NUMERIC(5,2),((SELECT COUNT(ShipDate) FROM #Orders WHERE ShipDate < PromiseShipDate) * 1.0 / (SELECT COUNT(ShipDate) FROM #Orders)) * 100) AS OnTimePercentage;

DROP TABLE #Orders;

Cheers!

EDIT: Fixed a typo