October 18, 2011 at 8:23 am
I have a join on our work orders database between Work Orders and Service orders. Very rarely there can be multiple service orders attached to a Work Order, but I only need and want one if there are. Anybody have thoughts on an easy way to go about doing this? Here is the SQL.
SELECT DISTINCT WO.WorkOrder, WO.Location, WO.MainServicePlanner, WO.MainDivision,
WO.MainType, WO.LastEditTime, WO.InsertTime, WO.Status, WO.LateStartDate,
WO.ReleasedDate, WO.Poles, SO.ServiceOrder FROM WorkOrders WO
LEFT OUTER JOIN IntWOSO I ON WO.WorkOrder = I.WorkOrder
LEFT OUTER JOIN ServiceOrders SO ON I.ServiceOrder = SO.ServiceOrder
WHERE WO.MainServicePlanner = @Planner
AND WO.MainDivision = @Division
AND UPPER(SO.MemberName) Like @MemberName
AND SO.Assigned >= @AssignedFrom
AND SO.Assigned < @AssignedTo
GROUP BY WO.WorkOrder, WO.Location, WO.MainServicePlanner, WO.MainDivision,
WO.MainType, WO.LastEditTime, WO.InsertTime, WO.Status, WO.LateStartDate,
WO.ReleasedDate, WO.Poles, SO.ServiceOrder ORDER BY WO.InsertTime DESC
October 18, 2011 at 8:31 am
If you only want one Work Order, how will you choose which one will be returned? Also, your WHERE clause includes conditions on your outer tables. This will have the effect of turning your outer join into an inner join.
John
October 18, 2011 at 8:37 am
John Mitchell-245523 (10/18/2011)
If you only want one Work Order, how will you choose which one will be returned? Also, your WHERE clause includes conditions on your outer tables. This will have the effect of turning your outer join into an inner join.John
In the rare case there are multiple Service Orders attached to one Work Order, they don't really care which one is returned. Just the first found is fine, they just want only one. The problemis there are some calculated fields in the report this drives attached to Work Order, so if it has five service orders attached to it, and five rows are returned, the values are multipeld by five times was the values actually are. Yeah I guess an inner join would work as well, you can't have a Work order without a Service order.
October 18, 2011 at 8:43 am
One way of doing it is put a DISTINCT in, thus:
...
LEFT OUTER JOIN (SELECT DISTINCT ServiceOrder FROM ServiceOrders) SO ON I.ServiceOrder = SO.ServiceOrder
...
John
October 18, 2011 at 8:51 am
John Mitchell-245523 (10/18/2011)
One way of doing it is put a DISTINCT in, thus:...
LEFT OUTER JOIN (SELECT DISTINCT ServiceOrder FROM ServiceOrders) SO ON I.ServiceOrder = SO.ServiceOrder
...
John
Huh that may be my ticket, except distinct on the WorkOrder. Thanks, let me play around with that idea.
October 18, 2011 at 9:35 am
You have two options. Which one works best will depend on a number of factors such as what indexes you have/create and how selective those indexes are.
Option 1: CTE with Row_Number()
; WITH RankedServiceOrders AS (
SELECT i.WorkOrder, so.ServiceOrder
, Row_Number() OVER( PARTITION BY i.WorkOrder ORDER BY so.ServiceOrder ) AS rn
FROM IntWOSO AS i
INNER JOIN ServiceOrders AS so
ON i.ServiceOrder = so.ServiceOrder
WHERE SO.MemberName Like @MemberName COLLATE Latin1_General_CI_AS
AND so.Assigned >= @AssignedFrom
AND so.Assigned < @AssignedTo
)
SELECT WO.WorkOrder, WO.Location, WO.MainServicePlanner, WO.MainDivision,
WO.MainType, WO.LastEditTime, WO.InsertTime, WO.Status, WO.LateStartDate,
WO.ReleasedDate, WO.Poles, SO.ServiceOrder
FROM WorkOrders AS wo
LEFT OUTER JOIN RankedServiceOrders AS so
ON wo.WorkOrder = so.WorkOrder
AND so.rn = 1
WHERE WO.MainServicePlanner = @Planner
AND WO.MainDivision = @Division
ORDER BY WO.InsertTime DESC
I moved all of the criteria specific to the ServiceOrder to the CTE. You probably don't need the UPPER in your query, but just in case, I changed it to use a specific collation rather than upper to allow that field to be SARGable.
OPTION 2: OUTER APPLY
SELECT WO.WorkOrder, WO.Location, WO.MainServicePlanner, WO.MainDivision,
WO.MainType, WO.LastEditTime, WO.InsertTime, WO.Status, WO.LateStartDate,
WO.ReleasedDate, WO.Poles, SO.ServiceOrder
FROM WorkOrders AS wo
OUTER APPLY (
SELECT TOP (1) so.ServiceOrder
FROM IntWOSO AS i
INNER JOIN ServiceOrders AS so
ON i.ServiceOrder = so.ServiceOrder
WHERE wo.WorkOrder = i.WorkOrder
AND SO.MemberName Like @MemberName COLLATE Latin1_General_CI_AS
AND so.Assigned >= @AssignedFrom
AND so.Assigned < @AssignedTo
) AS so
WHERE WO.MainServicePlanner = @Planner
AND WO.MainDivision = @Division
ORDER BY WO.InsertTime DESC
Here the TOP (1) serves the same function as the Row_Number in the CTE. The CTE requires some extra fields be returned in order to specify the JOIN condition, whereas the OUTER APPLY specifies those same conditions internally. The OUTER APPLY parallels an OUTER JOIN. A CROSS APPLY would parallel an INNER JOIN.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 19, 2011 at 8:16 am
Crap, apparently this is a legacy database, and even though I'm working with it with the 2008 interface, it's a 2000, so the OUTER APPLY won't work. Thanks for the suggestion though.
October 20, 2011 at 8:13 am
I am assuming the IntWOSO table joins both WorkOrders and ServiceOrders tables.
The MAX in the GROUP BY subquery ensures you have only one row per WorkOrder and HAVING clause eliminates the WorkOrders that don't have ServiceOrders.
In the GROUP BY subquery, you may also get the specific ServiceOrder when there are multiple ServiceOrders per WorkOrder.
Try this and let me know.
[Code]
SELECT WO.WorkOrder, WO.Location, WO.MainServicePlanner, WO.MainDivision,
WO.MainType, WO.LastEditTime, WO.InsertTime, WO.Status, WO.LateStartDate,
WO.ReleasedDate, WO.Poles, SO.ServiceOrder
FROM WorkOrders WO
INNER JOIN (SELECT WorkOrder, MAX(ServiceOrder) as ServiceOrder
FROM IntWOSO
GROUP BY WorkOrder
HAVING count(*) > 0
) I ON WO.WorkOrder = I.WorkOrder
LEFT OUTER JOIN ServiceOrders SO ON I.ServiceOrder = SO.ServiceOrder
WHERE WO.MainServicePlanner = @Planner
AND WO.MainDivision = @Division
AND UPPER(SO.MemberName) Like @MemberName
AND SO.Assigned >= @AssignedFrom
AND SO.Assigned < @AssignedTo
ORDER BY WO.InsertTime DESC
[/Code]
October 20, 2011 at 8:24 am
Perfect man, thank you.
October 20, 2011 at 8:54 am
I am glad I could help
October 20, 2011 at 3:45 pm
"HAVING count(*) > 0" is totally useless expression.
And "LEFT OUTER JOIN" can safely be replaced with "INNER JOIN" because of the conditions in WHERE clause.
October 21, 2011 at 8:23 am
That's true, I kind of overlooked the conditions in the where clause. Thanks for your comments
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply