title: SQL query producing duplicate rows and I can't see why
body: <p>My query always produces duplicate results. How best do I go about troubleshooting this query with a database > 1 million rows.</p>
  
  <pre><code>Select segstart
      ,segment
      ,callid
      ,Interval
      ,dialed_num
      ,FiscalMonthYear
      ,SegStart_Date
      ,row_date
      ,Name
      ,Xferto
      ,TransferType
      ,Agent
      ,Sup
      ,Manager
      ,'MyCenter' = Case Center
  When 'Livermore Call Center' Then 'LCC'
  When 'Natomas Call Center' Then 'NCC'
  When 'Concord Call Center' Then 'CCC'
  When 'Virtual Call Center' Then 'VCC'
  When 'Morgan Hill Call Center' Then 'MHCC'
  Else Center
  End
      ,Xferfrom
      ,talktime
      ,ANDREWSTABLE.transferred
      ,ANDREWSTABLE.disposition
      ,dispsplit
      ,callid
      ,hsplit.starttime
      ,CASE
      WHEN hsplit.callsoffered &gt; 0 
      THEN (CAST(hsplit.acceptable as DECIMAL)/hsplit.callsoffered)*100
      ELSE '0'
      END AS 'Service Level'
      ,hsplit.callsoffered
      ,hsplit.acceptable
  FROM
  (
  Select segstart,
      100*DATEPART(HOUR, segstart) + 30*(DATEPART(MINUTE, segstart)/30) as Interval,
      FiscalMonthYear,
      SegStart_Date,
      dialed_num,
      callid,
      Name,
      t.Queue AS 'Xferto',
      TransferType,
      RepLName+', '+RepFName AS Agent,
      SupLName+', '+SupFName AS Sup,
      MgrLName+', '+MgrFName AS Manager,
      q.Center,
      q.Queue AS 'Xferfrom',
      e.anslogin,
      e.origlogin,
      t.Extension,
      transferred,
      disposition,
      talktime,
      dispsplit,
      segment
  From CMS_ECH.dbo.CaliforniaECH e
  
  INNER JOIN Cal_RemReporting.dbo.TransferVDNs t on e.dialed_num = t.Extension
  INNER JOIN InfoQuest.dbo.IQ_Employee_Profiles_v3_AvayaId q on e.origlogin = q.AvayaID
  INNER JOIN Cal_RemReporting.dbo.udFiscalMonthTable f on e.SegStart_Date = f.Tdate
  
  Where SegStart_Date between getdate()-90 and getdate()-1
      And q.Center not in ('Collections Center',
                           'Cable Store',
                           'Business Services Center',
                           'Escalations')
      And SegStart_Date between RepToSup_StartDate and RepToSup_EndDate
      And SegStart_Date between SupToMgr_StartDate and SupToMgr_EndDate
      And SegStart_Date between Avaya_StartDate and Avaya_EndDate
      And SegStart_Date between RepQueue_StartDate and RepQueue_EndDate
      AND (e.transferred like '1'
      OR e.disposition like '4') order by segstart
  ) AS ANDREWSTABLE
  
  --Left Join CMS_ECH.dbo.hsplit hsplit on hsplit.starttime = ANDREWSTABLE.Interval and hsplit.row_date = ANDREWSTABLE.SegStart_Date and ANDREWSTABLE.dispsplit = hsplit.split
  </code></pre>