After a long time, I am getting a chance to post a SQL Server question here.
I have a table variable as shown below, in SQL Server 2005. This table is populated by a stored procedure written by some other team.
This is a order processing system. Each order can be accomplished by multiple processes by various departments, based on the OPRouteCode.
Taking example for OrderNo = 2, it has two OPRouteCode - but both these OPRouteCodes are using the same processes by same departments. They are considered equivalent OPRouteCodes.
On the other hand, for example OrderNo = 1, the processes and departments vary; hence they are not equivalent.
What is the best way to select only orders that has non-equivalent OPRouteCodes.
Note: If there is only one OPRouteCode, it is considered as equivalent only. Non-equivalence come only if there are more than one OPRouteCode.
What is the best SQL Server query to get this result? I couldn't write anything working after hours of effort.
DECLARE @OrderProcess TABLE (OrderNo Int,
OPRouteCode VARCHAR(5),
Department VARCHAR(10),
Process VARCHAR(20) )
--Order = 1 OPRouteCode = '0023'
INSERT INTO @OrderProcess
SELECT 1,'0023' ,'103','Receive'
UNION ALL
SELECT 1,'0023' ,'104','Produce'
UNION ALL
SELECT 1,'0023' ,'104','Pack'
UNION ALL
SELECT 1,'0023' ,'105','Ship'
--Order = 1 OPRouteCode = '0077'
INSERT INTO @OrderProcess
SELECT 1,'0077' ,'103','Receive'
UNION ALL
SELECT 1,'0077' ,'104','Produce'
UNION ALL
SELECT 1,'0077' ,'105','Ship'
--Order = 2 OPRouteCode = '0044'
INSERT INTO @OrderProcess
SELECT 2,'0044' ,'105','Receive'
UNION ALL
SELECT 2,'0044' ,'106','Ship'
--Order = 2 OPRouteCode = '0055'
INSERT INTO @OrderProcess
SELECT 2,'0055' ,'105','Receive'
UNION ALL
SELECT 2,'0055' ,'106','Ship'
Table Variable
Expected Output
Aucun commentaire:
Enregistrer un commentaire