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

Tamayo, Ashley 121 Reputation points
2021-03-01T18:30:31.16+00:00

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:

73086-image.png

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
0 comments
Accepted answer
  1. Jeffrey Williams 1,886 Reputation points
    2021-03-01T22:05:05.75+00:00

    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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,136 Reputation points
    2021-03-02T05:45:39.453+00:00

    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.

    0 comments