September 17, 2007 at 5:16 pm
Goal: To determine how many (COUNT) work orders have a single endpoint? How many orders have multiple endpoints?
Background: A WorkOrder has a single endpoint if it only has one unique JobNumber. (Like WorkOrders 1, 2, & 5)
[RealWorld example: A single drive (WorkOrder) from A to B(JobNumber) may involve many gas stops (StopNumber),
but B is the true single destination (endpoint).]
A WorkOrder has multiple endpoints if it has more than one JobNumber. (Like WorkOrders 3 & 4)
[RealWorld Example: A pizza delivery driver that delivers only one pizza at a time.
There is one bill (WorkOrder) for two pizzas (JobNumber)and the pizzas are to be delivered on different days (StopNumber)]
A WorkOrder may appear more than once.
The JobNumber is unique to the table but may appear more than once for a specific WorkOrder.
The StopNumber is sequential.
CREATE TABLE #TestTable (
ID int IDENTITY (1,1),
WorkOrder int,
JobNumber int,
StopNumber int)
INSERT #TestTable
--Examples of Single endpoints
SELECT 1, 2, 1
UNION
SELECT 1, 2, 2
UNION
SELECT 1, 2, 3
UNION
SELECT 2, 5, 1
UNION
SELECT 2, 5, 2
UNION
SELECT 5, 1, 1
UNION
--Examples of Multiple endpoints
SELECT 3, 6, 1
UNION
SELECT 3, 7, 1
UNION
SELECT 3, 8, 1
UNION
SELECT 4, 9, 1
UNION
SELECT 4, 10, 1
Answer should be 3 workorders with a single endpoint, and 2 workorders with multiple endpoints
September 17, 2007 at 5:32 pm
In your simple table this should provide the results you need. Maybe somebody has a more elegant solution.
SELECT
'One EndPoint', COUNT(WorkOrder)
FROM (
SELECT WorkOrder, COUNT(DISTINCT JobNumber) AS JobNumberCount
FROM #TestTable
GROUP BY WorkOrder
) as tbl
WHERE JobNumberCount = 1
UNION ALL
SELECT '2+ EndPoint', COUNT(WorkOrder)
FROM (
SELECT WorkOrder, COUNT(DISTINCT JobNumber) AS JobNumberCount
FROM #TestTable
GROUP BY WorkOrder
) as tbl
WHERE JobNumberCount > 1
September 17, 2007 at 5:39 pm
Similar to Mike's:
select count(*) from (
select WorkOrder from #TestTable group by WorkOrder having count(distinct JobNumber)=1
) as [single_ep]
union all...
September 17, 2007 at 5:47 pm
Thank you both!!!
September 19, 2007 at 6:02 pm
Little late, but...
here's another approach comparing min and max values...
SELECT CASE WHEN MIN_JobNumber = MAX_JobNumber THEN 'One EndPoint' ELSE '2+ EndPoint' END, SUM(x)
FROM (SELECT 1 x, MIN(JobNumber) MIN_JobNumber, MAX(JobNumber) MAX_JobNumber FROM #TestTable GROUP BY WorkOrder) D
GROUP BY CASE WHEN MIN_JobNumber = MAX_JobNumber THEN 'One EndPoint' ELSE '2+ EndPoint' END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply