identifying duplicates by person

• kelly-408607

  Old Hand

  Points: 337

  More actions

  February 19, 2007 at 9:30 am

  #176079

   I have a query:

   

  SELECT     orig.*, X.DisplayName AS Expr1, X.BirthDayNum AS Expr2,

  X.BirthMonthNum AS Expr3,

                        X.BirthYearNum AS Expr4

  FROM         X INNER JOIN

                        Y ON X.PRIMARYKEY =

  Y.Client.PRIMARYKEY INNER JOIN

                            (SELECT     ReasonText, CreatedWhen, OrderStatusCode

                              FROM          Y

                              WHERE      (OrderStatusCode = 'perf')) orig INNER JOIN

                            (SELECT     reasontext

                              FROM          Y

                              WHERE      orderstatuscode = 'perf'

                              GROUP BY reasontext

                              HAVING      COUNT(*) > 1) dup ON orig.ReasonText =

  dup.reasontext AND orig.ReasonText = dup.reasontext ON

                        Y.ReasonText = orig.ReasonText COLLATE

  SQL_Latin1_General_CP1_CI_AS

  WHERE     (Y.CreatedWhen >= '20061101') AND

  (Y.CreatedWhen < '20070215') AND

                        (Y.ReasonText LIKE 'Filler Order ID:%') OR

                        (Y.ReasonText LIKE 'Placer Order ID:%')

   

   

   

   

   

   

   

   

   

   

   

   

  Filler Order ID: <A023403771020> Filler Facility ID: <RAD>	PERF	Good, KELLY
  Filler Order ID: <A023403771020> Filler Facility ID: <RAD>	PERF	Good, KELLY
  Filler Order ID: <1018863276856> Filler Facility ID: <RAD>	PERF	XTEST, TEST
  Filler Order ID: <1018863276856> Filler Facility ID: <RAD>	PERF	XTEST, TEST
  Filler Order ID: <A023403771020> Filler Facility ID: <RAD>	PERF	BAD, KELLY
  Filler Order ID: <A023403771020> Filler Facility ID: <RAD>	PERF	BAD, KELLY
  Filler Order ID: <1018863276856> Filler Facility ID: <RAD>	PERF	XTEST, TWO
  Filler Order ID: <1018863276856> Filler Facility ID: <RAD>	PERF	XTEST, TWO

   

   

  What I want is to identify when the filler order id is repeated for more than one person., ie  Good, Kelly and Bad, Kelly

   

  I don’t care if its repeated on the same person.

   

  How do I do this? And thanks to SQLBILL for all previous help.

   

  Any help would be appreciated.

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  February 19, 2007 at 5:05 pm

  #690564

  Maybe I'm just missing it... what does the output you listed have to do with the query you listed?

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