How to get a DATEDIFF type calculation for values on differents rows with two columns

Question

I have been tasked to do something that I'm not sure is possible and have no idea where to start. I need to calculate the duration between the first unit's DispatchTime and the OnSceneTime of the unit that actually went to the scene. The column that the units have in common will be the incident number. It does not occur on every incident and I have to include the incidents where only one unit was dispatched and arrived on scene. I also need to compare the IncidentDate against the unit OnSceneTime, but I have a field called CreateTimeToOnSceneTimeInS that does it for me. My query returns values like this:

I have a basic query with no joins, but would possibly need to add one join to get different columns for the incident location and city. I appreciate ANY advice. I am a SQL newbie and am learning trial by error and with advice obtained here.

SELECT  
   IncidentNumber  
  ,IncidentDate  
  ,Agency  
  ,Unit  
  ,DispatchTime  
  ,EnrouteTime  
  ,OnSceneTime  
  ,ClearTime  
  ,CreateTimeToOnSceneTimeInS  
  
FROM  
MV_IncidentUnits  
  
WHERE  
Agency ='EMS'  
and IncidentDate >= @IncidentDate  
and IncidentDate <= @IncidentDate2  

Answer 1

It would help if you provided sample data - I have put this together:

 --==== Create a test table  
Declare @testTable Table (  
        IncidentNumber int  
      , IncidentDate datetime  
      , Agency char(3)  
      , Unit char(2)  
      , DispatchTime datetime  
      , EnrouteTime datetime  
      , OnSceneTime datetime  
      , ClearTime datetime  
        );  
  
 --==== Populate test table with sample data  
 Insert Into @testTable (IncidentNumber, IncidentDate, Agency, Unit, DispatchTime, EnrouteTime, OnSceneTime, ClearTime)  
 Values (1, '2021-03-01 00:00:00.000', 'EMS', 'M1', '2021-03-01 00:01:00.000', '2021-03-01 00:02:00.000', '2021-03-01 00:15:00.000', '2021-03-01 00:30:00.000')  
      , (2, '2021-03-01 09:00:00.000', 'EMS', 'M1', '2021-03-01 09:01:00.000', '2021-03-01 09:02:00.000',                      Null, '2021-03-01 09:05:00.000')          
      , (2, '2021-03-01 09:00:00.000', 'EMS', 'M1', '2021-03-01 09:04:00.000', '2021-03-01 09:05:00.000', '2021-03-01 09:10:00.000', '2021-03-01 09:30:00.000')          
      , (3, '2021-03-01 10:00:00.000', 'EMS', 'M1', '2021-03-01 10:02:00.000', '2021-03-01 10:03:00.000',                      Null, '2021-03-01 10:00:00.000')          
      , (4, '2021-03-01 12:59:00.000', 'EMS', 'M1', '2021-03-01 13:00:00.000',                      Null,                      Null, '2021-03-01 13:02:00.000')          
      , (4, '2021-03-01 12:59:00.000', 'EMS', 'M1', '2021-03-01 13:01:00.000', '2021-03-01 13:02:00.000',                      Null, '2021-03-01 13:15:00.000')          
      , (4, '2021-03-01 12:59:00.000', 'EMS', 'M1', '2021-03-01 13:14:00.000', '2021-03-01 13:20:00.000', '2021-03-01 13:20:00.000', '2021-03-01 13:40:00.000');  
  
 --==== Solution  
 Select tt.IncidentNumber  
      , tt.IncidentDate  
      , tt.Agency  
      , tt.Unit  
      , tt.DispatchTime  
      , tt.EnrouteTime  
      , tt.OnSceneTime  
      , tt.ClearTime  
      , TimeToOnScene = datediff(second, tt.IncidentDate, tt.OnSceneTime)  
      , MinOnSceneTime = min(tt.IncidentDate) over(Partition By tt.IncidentNumber)  
      , MaxOnSceneTime = max(tt.OnSceneTime) over(Partition By tt.IncidentNumber)  
      , TotalOnSceneTime = datediff(second, min(tt.IncidentDate) over(Partition By tt.IncidentNumber), max(tt.OnSceneTime) over(Partition By tt.IncidentNumber))  
   From @testTable      tt;  

Given the above information - what are you looking for as expected results? Your narrative describes additional columns not shown - no idea how that fits in with the expected solution.

Incident 3 does not have an OnSceneTime for any unit...how would that be calculated?

I have included some columns I think you are looking for - but not sure it is correct.

Answer 2

Hi @Tamayo, Ashley or anonymous user,

Welcome to Microsoft Q&A!

It is recommended for you to login with the account posted this question and provide the expected result of the sample.

You could refer below and check whether it is helpful to you.

 ;with cte as (  
 SELECT IncidentNumber  
   ,IncidentDate  
   ,Agency  
   ,Unit  
   ,DispatchTime  
   ,EnrouteTime  
   ,OnSceneTime  
   ,ClearTime  
   ,CreateTimeToOnSceneTimeInS   
 FROM MV_IncidentUnits  
 WHERE Agency ='EMS'  
 and IncidentDate >= @IncidentDate  
 and IncidentDate <= @IncidentDate2)  
 select IncidentNumber  
 ,DATEDIFF(SECOND,min(DispatchTime),max(OnSceneTime)) calculation   
 from cte  
 group by IncidentNumber  

The output could be like below:

IncidentNumber	calculation  
1	840  
2	540  
3	NULL  
4	1200  

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.