Average Duration in Status - Gaps and Islands

Question

I'm trying to calculate the average turnover time of a piece of equipment in REPAIR status.

I was able to create a query containing a list of equipments with their snapshotted status on each day.

+-----------------+--------------+--------+----------------------+------------+------------------+
| equipmentNumber | snapshotDate | status | previousSnapshotDate | prevStatus | statusChangeFlag |
+-----------------+--------------+--------+----------------------+------------+------------------+
|          123456 | 2018-04-29   | ONHIRE | 2018-04-28           | AVAILABLE  |                1 |
|          123456 | 2018-04-30   | ONHIRE | 2018-04-29           | ONHIRE     |                0 |
|          123456 | 2018-05-01   | ONHIRE | 2018-04-30           | ONHIRE     |                0 |
|          123456 | 2018-05-02   | REPAIR | 2018-05-01           | ONHIRE     |                1 |
|          123456 | 2018-05-03   | REPAIR | 2018-05-02           | REPAIR     |                0 |
|          123456 | 2018-05-04   | ONHIRE | 2018-05-03           | REPAIR     |                1 |
|          654321 | 2018-04-30   | REPAIR | 2018-04-29           | AVAILABLE  |                1 |
|          654321 | 2018-05-01   | REPAIR | 2018-04-30           | REPAIR     |                0 |
|          654321 | 2018-05-02   | REPAIR | 2018-05-01           | REPAIR     |                0 |
+-----------------+--------------+--------+----------------------+------------+------------------+

So, in this example, we have 2 equipments, "123456" was in REPAIR status 2 days on 5/2 and 5/3, and "654321" was in REPAIR status 3 days on 4/30, 5/1, and 5/2. That would be an average repair turnaround time of (2+3) / 2 = 2.5 days.

I tried this algorithm (Detect consecutive dates ranges using SQL) but it doesn't seem to be quite working for my needs.

score 1 · Accepted Answer · answered May 31 '18 at 12:28

I attempt to answer Gaps and Islands using an Incrementing ID column, create one if one doesn't exist, and the ROW_NUMBER window function

CREATE TABLE T1
    ([equipmentNumber] int, [snapshotDate] datetime, [status] varchar(6), [previousSnapshotDate] datetime, [prevStatus] varchar(9), [statusChangeFlag] int)
;

INSERT INTO T1
    ([equipmentNumber], [snapshotDate], [status], [previousSnapshotDate], [prevStatus], [statusChangeFlag])
VALUES
    (123456, '2018-04-29 00:00:00', 'ONHIRE', '2018-04-28 00:00:00', 'AVAILABLE', 1),
    (123456, '2018-04-30 00:00:00', 'ONHIRE', '2018-04-29 00:00:00', 'ONHIRE', 0),
    (123456, '2018-05-01 00:00:00', 'ONHIRE', '2018-04-30 00:00:00', 'ONHIRE', 0),
    (123456, '2018-05-02 00:00:00', 'REPAIR', '2018-05-01 00:00:00', 'ONHIRE', 1),
    (123456, '2018-05-03 00:00:00', 'REPAIR', '2018-05-02 00:00:00', 'REPAIR', 0),
    (123456, '2018-05-04 00:00:00', 'ONHIRE', '2018-05-03 00:00:00', 'REPAIR', 1),
    (654321, '2018-04-30 00:00:00', 'REPAIR', '2018-04-29 00:00:00', 'AVAILABLE', 1),
    (654321, '2018-05-01 00:00:00', 'REPAIR', '2018-04-30 00:00:00', 'REPAIR', 0),
    (654321, '2018-05-02 00:00:00', 'REPAIR', '2018-05-01 00:00:00', 'REPAIR', 0)
;
;WITH cteX
AS(
    SELECT
         Id = ROW_NUMBER()OVER(ORDER BY T.equipmentNumber, T.snapshotDate)
        ,T.equipmentNumber
        ,T.snapshotDate
        ,T.[status]
        ,T.previousSnapshotDate
        ,T.prevStatus
        ,T.statusChangeFlag
    FROM dbo.T1 T
),cteIsland
AS(
    SELECT 
         Island = X.Id - ROW_NUMBER()OVER(ORDER BY X.Id)
        ,*
    FROM cteX X
    WHERE X.[status] = 'REPAIR'
)
SELECT * FROM cteIsland

Note the Island Column

Island  Id  equipmentNumber status
3       4   123456          REPAIR
3       5   123456          REPAIR
4       7   654321          REPAIR
4       8   654321          REPAIR
4       9   654321          REPAIR

Using the Island Column you can get the answer you need with this TSQL

;WITH cteX
AS(
    SELECT
         Id = ROW_NUMBER()OVER(ORDER BY T.equipmentNumber, T.snapshotDate)
        ,T.equipmentNumber
        ,T.snapshotDate
        ,T.[status]
        ,T.previousSnapshotDate
        ,T.prevStatus
        ,T.statusChangeFlag
    FROM dbo.T1 T
),cteIsland
AS(
    SELECT 
         Island = X.Id - ROW_NUMBER()OVER(ORDER BY X.Id)
        ,*
    FROM cteX X
    WHERE X.[status] = 'REPAIR'
)
SELECT 
     AvgDuration =SUM(Totals.IslandCounts) / (COUNT(Totals.IslandCounts) * 1.0)
FROM
(
    SELECT 
          IslandCounts = COUNT(I.Island)
         ,I.equipmentNumber
    FROM cteIsland I
    GROUP BY I.equipmentNumber
) Totals

Answer

AvgDuration
2.50000000000000

Here's the SQLFiddle

score 0 · Answer 2 · answered May 31 '18 at 12:17

0

That method should work to identify the repair periods:

select equipmentNumber, min(snapshotDate), max(snapshotDate)
from (select t.*,
             row_number() over (partition by equipmentNumber order by snapshotDate) as seqnum
      from t
     ) t
where status = 'REPAIR'
group by equipmentNumber, dateadd(day, - seqnum, snapshotDate);

You can get the average using a subquery:

select avg(datediff(day, minsd, maxsd) * 1.0)
from (select equipmentNumber, min(snapshotDate) as minsd, max(snapshotDate) as maxsd
      from (select t.*,
                   row_number() over (partition by equipmentNumber order by snapshotDate) as seqnum
            from t
           ) t
      where status = 'REPAIR'
      group by equipmentNumber, dateadd(day, - seqnum, snapshotDate)
     ) e;

answered May 31 '18 at 12:17

Gordon Linoff

1,242,037
58
646
786

I don't think this method accounts for a single piece of equipment with 2 separate repairs. For example equipment 123456 could have a repair from 2018-03-04 to 2018-03-08 (lasting 4 days) and also from 2018-05-01 to 2018-05-05 (also lasting 4 days). Because we group by equipmentNumber and duration, that would only account for one of those instances. correct me if i'm wrong? – Ted May 31 '18 at 12:43
@Ted . . . Of course it does. This is how one handles the gaps-and-islands problem with sequential dates. That's why it subtracts the sequence number from the date. – Gordon Linoff Jun 01 '18 at 01:52
This gives the incorrect answer - when I run on the test data I supplied in the question, it gives an average of 1.5 days when it should be returning 2.5 days. – Ted Jun 04 '18 at 12:51

Average Duration in Status - Gaps and Islands

2 Answers2