T-SQL help

• Attopeu

  Ten Centuries

  Points: 1308

  More actions

  May 27, 2011 at 7:13 am

  #236951

  Hi,

  How can I write a query to return only records when one ManagerID(1430857) is tied to multiple Record# (375964,375965)?

  Record# ManagerID

  ------------------

  3708681427894

  2746581428292

  2746581428293

  3498831430095

  735551430129

  735551430535

  3759641430857

  375965 1430857

  Thanks much,

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 27, 2011 at 7:20 am

  #1330968

  you can do it with a subselect of something featuring row_number(), or simply with a GROUP BY:

  With MySampleData ([Record#],ManagerID)

  AS

  (

  SELECT 370868,1427894 UNION ALL

  SELECT 274658,1428292 UNION ALL

  SELECT 274658,1428293 UNION ALL

  SELECT 349883,1430095 UNION ALL

  SELECT 73555,1430129 UNION ALL

  SELECT 73555,1430535 UNION ALL

  SELECT 375964,1430857 UNION ALL

  SELECT 375965,1430857

  )

  SELECT

  ManagerID,

  COUNT([Record#]) AS NumRecords

  FROM MySampleData

  GROUP BY ManagerID

  HAVING COUNT([Record#]) > 1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Attopeu

  Ten Centuries

  Points: 1308

  More actions

  May 27, 2011 at 7:39 am

  #1330991

  Hi Lowell,

  Actually I gave a bad sample data. I added a few more duplicate records but I really need to return ManagerID of 1430857. Also, I have more than a 1000 records that I need to go through.

  Record# ManagerID

  ------------------

  370868 1427894

  274658 1428292

  274658 1428292

  274658 1428293

  349883 1430095

  73555 1430129

  73555 1430535

  73555 1430535

  375964 1430857

  375965 1430857

  Hope you can help.

  Thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 27, 2011 at 7:53 am

  #1330998

  what i posted works...whether there's more than one manager or not.

  change the query to work on YourTable instead of mySampleData... then tell us what you expected that is different fromt eh results:

  /*--results

  ManagerIDNumRecords

  14282922

  14305352

  14308572

  */

  With MySampleData ([Record#],ManagerID)

  AS

  (

  SELECT 370868,1427894 UNION ALL

  SELECT 274658,1428292 UNION ALL

  SELECT 274658,1428292 UNION ALL

  SELECT 274658,1428293 UNION ALL

  SELECT 349883,1430095 UNION ALL

  SELECT 73555,1430129 UNION ALL

  SELECT 73555,1430535 UNION ALL

  SELECT 73555,1430535 UNION ALL

  SELECT 375964,1430857 UNION ALL

  SELECT 375965,1430857

  )

  SELECT

  ManagerID,

  COUNT([Record#]) AS NumRecords

  FROM MySampleData

  GROUP BY ManagerID

  HAVING COUNT([Record#]) > 1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 27, 2011 at 8:32 am

  #1331029

  Lowell, actually, if u look at the new dataset the OP provided, there are dups.. so, a GROUP BY of managerID with COUNT > 1 wont work on dups..

  You can do something like ( shamelessly using Lowell's sample data preparation, thanks Lowell :-))

  With MySampleData ([Record#],ManagerID)

  AS

  (

  SELECT 370868,1427894 UNION ALL

  SELECT 274658,1428292 UNION ALL

  SELECT 274658,1428292 UNION ALL

  SELECT 274658,1428293 UNION ALL

  SELECT 349883,1430095 UNION ALL

  SELECT 73555,1430129 UNION ALL

  SELECT 73555,1430535 UNION ALL

  SELECT 73555,1430535 UNION ALL

  SELECT 375964,1430857 UNION ALL

  SELECT 375965,1430857

  )

  SELECT

  ManagerID,

  COUNT([Record#]) AS NumRecords

  FROM MySampleData

  GROUP BY ManagerID

  HAVING COUNT(DISTINCT [Record#]) > 1

  OR

  With MySampleData ([Record#],ManagerID)

  AS

  (

  SELECT 370868,1427894 UNION ALL

  SELECT 274658,1428292 UNION ALL

  SELECT 274658,1428292 UNION ALL

  SELECT 274658,1428293 UNION ALL

  SELECT 349883,1430095 UNION ALL

  SELECT 73555,1430129 UNION ALL

  SELECT 73555,1430535 UNION ALL

  SELECT 73555,1430535 UNION ALL

  SELECT 375964,1430857 UNION ALL

  SELECT 375965,1430857

  )

  SELECT

  ManagerID,

  COUNT([Record#]) AS NumRecords

  FROM MySampleData

  GROUP BY ManagerID

  HAVING MAX(Record#]) <> MIN(Record#])

  {Edit: Removed "results" section from Lowell's sample }

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 27, 2011 at 8:47 am

  #1331042

  ColdCoffee (5/27/2011)

  ---

  Lowell, actually, if u look at the new dataset the OP provided, there are dups.. so, a GROUP BY of managerID with COUNT > 1 wont work on dups..

  I missed that part, ColdCoffee, thanks! i see the issue a lot better now.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 27, 2011 at 9:42 am

  #1331087

  Lowell (5/27/2011)

  ---

  ColdCoffee (5/27/2011)

  ---

  Lowell, actually, if u look at the new dataset the OP provided, there are dups.. so, a GROUP BY of managerID with COUNT > 1 wont work on dups..

  I missed that part, ColdCoffee, thanks! i see the issue a lot better now.

  You're welcome, Lowell..