How do I remove duplicate results from my sql query

Question

Thank you so much for helping!

Ok, the portion of my query producing duplicate results is:

Left Join CMS_ECH.dbo.hsplit hsplit on hsplit.row_date = ANDREWSTABLE.SegStart_Date 
                                   and hsplit.split = ANDREWSTABLE.dispsplit 
                                   and hsplit.starttime = ANDREWSTABLE.Interval

I believe it is producing duplicate results due to the hsplit table not having any primary key. While I am not the admin I cannot set a primary key. I have asked, but I would like a workaround.

I've seen this post as a solution: SQL - How can I remove duplicate rows?

But, how do I apply that to my query below:

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 > 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')
) AS ANDREWSTABLE

Left Join CMS_ECH.dbo.hsplit hsplit on hsplit.row_date = ANDREWSTABLE.SegStart_Date and hsplit.split=ANDREWSTABLE.dispsplit and hsplit.starttime = ANDREWSTABLE.Interval

Answer 1

Use GROUP BY statement

Answer 2

Maybe you can be helped by SELECT DISTINCT... (just add DISTINCT after your select). I don't know if it's sufficient for your application, but why don't you try it.

Answer 3

I believe it is producing duplicate results due to the hsplit table not having any primary key. While I am not the admin I cannot set a primary key. I have asked, but I would like a workaround.

The issue is that you have more than one row in the CMS_ECH.dbo.hsplit table that matches what you are joining to, based on the JOIN criteria (row_date, split, and starttime). It doesn't require an admin - you need to review the data from the CMS_ECH.dbo.hsplit table and see how you can get a one to one match (assuming it's possible).

I recommend looking at that before considering a bandaid solution like GROUP BY or DISTINCT...