SQL Finding duplicates that includes a certain value

Question

Trying to find duplicates (two or more) that one of the duplicates includes "Ambulance". Tried using SUM(CASE...), but the query result returns only the values with "Ambulance".

select r.CADIncidentNumber, r.unit_type
from dw_prod.dbo.vw_unit_response r
where r.incident_arv_date >= DATEADD(day,-7, getdate())
and datediff(minute, r.incident_arv_time, r.incident_clr_time) > 5
and r.CallTypeGrp2 = 'ALS'
and r.unit_type in ('Ambulance', 'Medic', 'Paramedic Engine', 'Paramedic 
Truck', 'Paramedic Tower', 'Paramedic Rescue Engine', 'Paramedic Brush 
Engine', 'Paramedic Rescue Squad')
group by r.CADIncidentNumber, r.unit_type
having count(r.CADIncidentNumber) >= 2
and sum(case when r.unit_type = 'Ambulance' then 1 else 0 end) > 0
order by r.CADIncidentNumber

Current output:
CADIncidentNumber   Unit_type
F1800022174         Ambulance
F1800022283         Ambulance
F1800022737         Ambulance

Desired output is something like:

CADIncidentNumber   Unit_type
F1800022174         Ambulance
F1800022174         Paramedic Engine
F1800022737         Ambulance
F1800022737         Medic

Thanks!

Gordon Linoff · Accepted Answer · 2018-02-22 13:34:39Z

2

If you want the original rows, use window functions:

select CADIncidentNumber, unit_type
from (select r.CADIncidentNumber, r.unit_type,
             sum(case when r.unit_type = 'Ambulance' then 1 else 0 end) over (partition by CADIncidentNumber) as ambulance_cnt,
             count(*) over (partition by CADIncidentNumber) as cnt
      from dw_prod.dbo.vw_unit_response r
      where r.incident_arv_date >= DATEADD(day, -7, getdate()) and
            datediff(minute, r.incident_arv_time, r.incident_clr_time) > 5 and
            r.CallTypeGrp2 = 'ALS' and
            r.unit_type in ('Ambulance', 'Medic', 'Paramedic Engine', 'Paramedic 
Truck', 'Paramedic Tower', 'Paramedic Rescue Engine', 'Paramedic Brush 
Engine', 'Paramedic Rescue Squad')
     ) r
where ambulance_cnt > 0 and cnt >= 2
order by r.CADIncidentNumber;

answered Feb 22, 2018 at 13:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Finding duplicates that includes a certain value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related