sql - Datediff in hours

Question

I have this script that ranks trailer status by date and the Statusing_Facility_CD responsible for statusing the trailer. I have successfully ranked them in order - now I am attempting to get the datediff(hh) between status times. The kicker is that I only want the datediff of the status times when there are sequential Statusing_Facility_CDs. For example: This result shows 9 statuses

Equipment_Unit_NB   Equipment_Status_System_TS  Equipment_Status_Type_CD    Statusing_Facility_CD   Ranking
10941               2020-12-11 22:13:43.8260000              ENR                  402                1
10941               2020-12-11 09:01:31.5350000              AVL                  402                2
10941               2020-12-11 01:29:43.1120000              ENR                  455                3
10941               2020-12-11 01:13:57.1300000              AVL                  455                4
10941               2020-12-11 00:05:01.0000000              AVL                  462                5
10941               2020-12-11 00:03:55.2930000              AVL                  431                6  
10941               2020-12-11 00:03:49.9480000              ENR                  455                7
10941               2020-12-10 00:10:58.2090000              AVL                  455                8
10941               2020-12-09 15:57:58.1800000              ENR                  402                9

Notice rank 1 and rank 2 have the same Statusing_Facility_CD - so I would want to see the datediff(hh) between those two Equipment_Status_System_TS's. Now see rank 2 and 3, they're not the same Statusing_Facility_CD so I do not want to see the datediff(hh) between those two statuses. The end result goal here is to get the average time (hh) for each Statusing_Facility_CD between each status change. The expected result for rank 1 and rank 2 would be:

 Statusing_Facility_CD       Equipment_Status_Type_CD            Hrs2Status
          402                       AVL - ENR                        13 

What modifications do I need to make to the code below in order to get the desired result?

SQL:

DECLARE
    @start DATE
    , @end DATE;

SET @start = '2020-12-07';
SET @end = '2020-12-11';

SELECT --[Standard_Carrier_Alpha_CD]
    [Equipment_Unit_NB]
    , CAST([Equipment_Status_System_TS] AS VARCHAR) AS Equipment_Status_System_TS
    , [Equipment_Status_Type_CD]
    , [Statusing_Facility_CD]
    , ROW_NUMBER() OVER (PARTITION BY CONCAT([Standard_Carrier_Alpha_CD],'',[Equipment_Unit_NB]) ORDER BY Equipment_Status_System_TS DESC) Ranking
FROM
    dbo.DILKOPS.[Equipment_Status] with (nolock)
    LEFT JOIN dbo.DILKOPS.[Facility] F
        ON [Statusing_Facility_CD] = f.Facility_CD
WHERE
    CAST(Equipment_Status_System_TS AS DATE) BETWEEN @start AND @end
    AND Standard_Carrier_Alpha_CD = 'RDWY'
    AND Equipment_Unit_NB = '10941'

question from:https://stackoverflow.com/questions/65602129/datediff-in-hours

Answer 1

Waitting for answers