SQL how to remove duplicate rows from CTE with multiple joins

Question

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

Answer 1

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

Answer 2

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