Find different values for related orders?

Question

Find different values for related orders?

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
January 19, 2010 at 12:22 pm

#215812

Hi from the temp table below I would like to see related orders that were actioned by different employees. So for RelatedID 1 employee B & C both actioned that related order. How would I be able to pick instances of this out...
Thanks in advance for your help.
IF OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;
Create table #A(RelatedID int, EmpID varchar(10))
INSERT INTO #A VALUES (0, 'A')
INSERT INTO #A VALUES (1, 'B')
INSERT INTO #A VALUES (1, 'C')
INSERT INTO #A VALUES (2, 'D')
INSERT INTO #A VALUES (2, 'D')
INSERT INTO #A VALUES (4, 'E')
Select * from #A

Viewing 6 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You have verbalized the desired output, could you show us what the final result of the query would look like? A picture is like a thousand words. Seeing what you want to accomplish helps a lot.

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 2

Lynn Pettis (1/19/2010)
You have verbalized the desired output, could you show us what the final result of the query would look like? A picture is like a thousand words. Seeing what you want to accomplish helps a lot.

Sure Lynn, from the temp table the only values I would like to see are relatedID: 1 because they have the same id but different employees: 'B' & 'C'

RElatedID EmpID

1 B

1 C

Hope this makes more sense...Thanks again.

LutzM SSC Guru Points: 107049 More actions · Answer 3

First of all: thank you for providing ready to use test data!! Well done! 😀

Would something like the following help?

;WITH cte AS

(

SELECT RelatedID

FROM #A

GROUP BY RelatedID

HAVING count(DISTINCT EmpID) >1

)

SELECT #A.* FROM #A INNER JOIN cte ON #A.RelatedID = cte.RelatedID

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

Marcus Farrugia (1/19/2010)
Hi from the temp table below I would like to see related orders that were actioned by different employees. So for RelatedID 1 employee B & C both actioned that related order. How would I be able to pick instances of this out...
Thanks in advance for your help.
IF OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;
Create table #A(RelatedID int, EmpID varchar(10))
INSERT INTO #A VALUES (0, 'A')
INSERT INTO #A VALUES (1, 'B')
INSERT INTO #A VALUES (1, 'C')
INSERT INTO #A VALUES (2, 'D')
INSERT INTO #A VALUES (2, 'D')
INSERT INTO #A VALUES (4, 'E')
Select * from #A

Lutz has the right idea with his code but let me ask you... what do you want done with the 2 instances of 2 D? Supress or return?

--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)

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 5

Hi Jeff, Lutz and Lynn...thanks for taking the time to reply...What I'd like to do is create a report for our process team that takes related orders identified by a related order id that were actioned by different employees, so that they can minimze touches on related product orders by multiple employees. So I'm actually joining a couple of tables from a related orders table and an employee workload table to get all related orders and which employees completed the order. So I would like to return the all jobs with the same related order id that have different employee numbers. Hope this makes more sense.