SQL how to remove duplicate rows from CTE with multiple joins

Tamayo, Ashley 121 Reputation points
2020-11-20T20:47:25.577+00:00

WITH CTE AS ( SELECT DISTINCT MV_Incident.CommonPlace as [Common Place] ,MV_Incident.Location as [Location] ,MV_Incident.CityName as [City] ,MV_Incident.ReportNumberAgencyId ,MV_Incident.IncidentTypeDescription as [Incident Type] ,dbo.DF_IncUnitStatusTime(MV_IncidentUnits.IncidentKey, ,MV_IncidentUnits.AgencyId, Callsign, 'TOC') as 'TransferCareTime' ,MV_Incident.IncidentTypeCode [Code] ,MV_Incident.IncidentKey ,MV_Incident.IncidentNumber [Incident Number] ,MV_Incident.ReportNumber as [Report Number] ,MV_Incident.IncidentStartedDateTime [Answer Time] ,MV_Incident.IncidentTypeDescription [Incident Description] ,CASE WHEN MV_IncidentUnits.DispositionDescription1 Is Null THEN '' WHEN MV_IncidentUnits.DispositionDescription2 Is Null THEN MV_IncidentUnits.DispositionDescription1 WHEN MV_IncidentUnits.DispositionDescription3 Is Null THEN MV_IncidentUnits.DispositionDescription1 + ', ' + MV_IncidentUnits.DispositionDescription2 WHEN MV_IncidentUnits.DispositionDescription4 Is Null THEN MV_IncidentUnits. DispositionDescription1 + ', ' + MV_IncidentUnits.DispositionDescription2 + ', ' + MV_IncidentUnits.DispositionDescription3 WHEN MV_IncidentUnits.DispositionDescription5 Is Null THEN MV_IncidentUnits.DispositionDescription1 + ', ' + MV_IncidentUnits.DispositionDescription2 + ', ' +MV_IncidentUnits. DispositionDescription3 + ', ' + MV_IncidentUnits.DispositionDescription4 WHEN MV_IncidentUnits.DispositionDescription5 Is Not Null THEN MV_IncidentUnits.DispositionDescription1 + ', ' + MV_IncidentUnits.DispositionDescription2 + ', ' + MV_IncidentUnits.DispositionDescription3 + ', ' + MV_IncidentUnits.DispositionDescription4 + ', ' + MV_IncidentUnits.DispositionDescription5 END AS [Disposition] ,MV_IncidentUnits.AgencyId ,MV_IncidentUnits.CallSign [Unit ID] ,MV_IncidentUnits.OnSceneTime [On Scene Time] ,MV_IncidentUnits.EnrouteHospitalTime [Transporting Time] ,MV_IncidentUnits.ArriveHospitalTime [At Hospital Time] ,MV_UnitHistory.TransactionDetail as Destination ,MV_IncidentUnits.ClearTime [Available] FROM MV_Incident LEFT JOIN MV_IncidentUnits ON MV_Incident.IncidentKey = MV_IncidentUnits.IncidentKey LEFT JOIN MV_UnitHistory ON MV_IncidentUnits.UnitKey = MV_UnitHistory.UnitKey AND (MV_UnitHistory.Status = 'tra' AND TransactionDetail Like 'destination%') AND MV_UnitHistory.ActiveIncidentKey = MV_IncidentUnits.IncidentKey WHERE ReportNumberAgencyId = MV_IncidentUnits.AgencyId and (MV_IncidentUnits.CallSign like ('m[0-9][0-9][0-9]') or MV_IncidentUnits.Callsign like ('r[0-3][0-9]') or MV_IncidentUnits.callsign like ('r[5-9[0-9]')) ) Select DISTINCT * FROM CTE WHERE CTE.[Answer Time] >= @IncidentDate AND CTE.[Answer Time] <= @IncidentDate2 AND Disposition in ('*Transport 1','*Transport 2','*Transport 3') AND cte.[Incident Description] in (@Description) AND cte.Location in (@Location) The above query is complex for me, and parts of it were pulled from existing reports and put together to meet the needs of the report I am creating. ![41581-image.png][1] These are the kind of results I receive when running this query. My dilemma is I need to exclude ONE of the lines that have the destination updated on. The comparison options are the destination field or the unit history time. I need to keep the one that was selected second as well as any other results that did not incur a change of destination. I have tried Last_Value, Row_Number, and Case but I either get a "not contained in group error" or I can't figure out how to keep the second occurrence of the duplicates PLUS all of the unique one time occurrences. Using the example results I'd like the query to return only rows 1, 3, 5, 6, and 7. I'm open to achieving this in SQL syntax or an expression (if that option exists). Thank you! [1]: /api/attachments/41581-image.png?platform=QnA

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2020-11-20T22:21:42.057+00:00
    SELECT *
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION Code ORDER BY UnitHistoryTime DESC) AS RN
        FROM CTE
        WHERE CTE.[Answer Time] >=  @IncidentDate
          AND CTE.[Answer Time] <= @IncidentDate2
          AND Disposition in ('*Transport 1','*Transport 2','*Transport 3')
          AND cte.[Incident Description] in (@Description)
          AND cte.Location in (@Location)
    ) AS t
    WHERE t.RN = 1
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-11-23T03:21:39.973+00:00

    Hi anonymous user,

    You could use ROW_NUMBER() as mentioned by other experts.

     SELECT *  
     FROM (  
         SELECT *, ROW_NUMBER() OVER(PARTITION BY Code ORDER BY UnitHistoryTime DESC) AS RN  
         FROM CTE  
         WHERE CTE.[Answer Time] >=  @IncidentDate  
           AND CTE.[Answer Time] <= @IncidentDate2  
           AND Disposition in ('*Transport 1','*Transport 2','*Transport 3')  
           AND cte.[Incident Description] in (@Description)  
           AND cte.Location in (@Location)  
     ) AS t  
     WHERE t.RN = 1  
    

    You could also try with an alternative way to produce the same result but may have your query run faster as below:

     Select DISTINCT *    
     FROM CTE  
     WHERE CTE.[Answer Time] >=  @IncidentDate  
       AND CTE.[Answer Time] <= @IncidentDate2  
       AND Disposition in ('*Transport 1','*Transport 2','*Transport 3')  
       AND cte.[Incident Description] in (@Description)  
       AND cte.Location in (@Location)  
       AND UnitHistoryTime=(Select MAX(UnitHistoryTime) from CTE as b Where b.CODE = a.CODE)  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table