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

Tamayo, Ashley 121 Reputation points

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.

Agency ='EMS'  
and IncidentDate >= @IncidentDate  
and IncidentDate <= @IncidentDate2  
0 comments No comments
{count} votes

Accepted answer
  1. Jeffrey Williams 1,886 Reputation points

    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 would that be calculated?

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

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,136 Reputation points

    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  
     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

    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.

    0 comments No comments