mercredi 29 juillet 2015

SQL Query based on occurrence of records

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

enter image description here

Expected Output

enter image description here

Aucun commentaire:

Enregistrer un commentaire