break down into smaller queries

Question

With much help from people here last week, I was able to complete a rather complex request. BUT now I am being asked (or suggested) to break the query into multiple simple queries, rather than the complex one. The current query is:

select i.IncidentNumber,
   r.unit_type, 
      r.unit,
      r.arv_dttm as Onscene_time,
   r.clr_dttm as Clear_time,
      datediff(minute, arv_time, clr_time) as time_diff
from (select CADIncidentNumber, 
         unit_type, unit, 
                  arv_dttm, 
                  clr_dttm,
                 arv_time,
                 clr_time,
         sum(case when 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 
  where CallTypeGrp2 = 'ALS' 
     and unit_type in ('Ambulance', 'Medic', 'Paramedic Engine', 'Paramedic Truck', 'Paramedic Tower', 'Paramedic Rescue Engine', 'Paramedic Brush Engine', 'Paramedic Rescue Squad')
  and DATEDIFF(DAY, arv_dttm,GETDATE()) < 8 
     and datediff(minute, arv_time, clr_time) > 5 
     ) r
left join INC_UnitInformation u on u.IncidentNumber = r.CADIncidentNumber
left join INC_Incident i on i.IncidentNumber = r.CADIncidentNumber
where ambulance_cnt > 0 and cnt >= 2
and not (u.PrimaryRoleOfUnit = 411000 or u.PrimaryRoleOfUnit = 411005) 
and r.unit_type not like 'Ambulance'
group by i.IncidentNumber, r.unit_type, r.unit, r.arv_dttm, r.clr_dttm, 
r.arv_time,r.clr_time
having COUNT(i.IncidentID) < 2
and sum(case when u.EMSUnitNumber like 'A%' then 1 else 0 end) > 0
order by r.arv_dttm

The query's result needs to include "Ambulance" in its values, which it does right now. There is no problem with the query result. I'm just trying to see if this complex query can be broken down into multiple smaller queries glued joined together. Other than the function to count and ensure the ambulance value is included in the result, I don't think this is all that complicated, but I don't know....

Thanks!

Answer 1

The most obvious way I see I to break your query down into multiple (two) queries, is to take your derived table:

from (select CADIncidentNumber, 
         unit_type, unit, 
                  arv_dttm, 
                  clr_dttm,
                 arv_time,
                 clr_time,
         sum(case when 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 
  where CallTypeGrp2 = 'ALS' 
     and unit_type in ('Ambulance', 'Medic', 'Paramedic Engine', 'Paramedic Truck', 'Paramedic Tower', 'Paramedic Rescue Engine', 'Paramedic Brush Engine', 'Paramedic Rescue Squad')
  and DATEDIFF(DAY, arv_dttm,GETDATE()) < 8 
     and datediff(minute, arv_time, clr_time) > 5 
     ) r

And use it by itself to populate a table variable. Then replace it with the table variable in the main query.