CODEWITHSUNDEEP
sqlsql-serverdatabasegaps-and-islands
koolaide
koolaide

Reputation: 155

Stuck on a Gaps and Islands query involving time periods

I have a pending request to write a report for a temperature log application that I have written and I just can't seem to nail the query.

The application stores temperature logs. These logs are to be done 3 times a day at several different locations. Each log contains one or more areas, which contain one or more piece of equipment.

My report needs to show equipment (EquipmentStoreIDs) which have had a bad temperature for more than 3 log periods in a row for a given report date range. I need to show the "island", or date range of when the bad temps started and ended as well. Some caveats:

Here is my SQLFiddle. My results are not correct because the consecutive fails start on a time period that was actually missed and I cant figure out how to handle multiple logs for the same time period.

Any help is much appreciated!

Upvotes: 3

Views: 551

Answers (1)

jlee-tessik
jlee-tessik

Reputation: 1520

Here's what I came up with.

To eliminate starting an island on a missed log, I compared the row's date with the first logged date we see per Store (minlog in the query below). If the row's date falls before that first log date, then we know it happened before we started recording, and I do not mark that with an error flag.

To handle multiple logs for the same time period, I added a Priority field in StagedRows, which we can use to grab only the first entry per Store/Datetime, prioritizing on a successful log.

WITH TargetLogs 
     AS (SELECT le.*, 
                CONVERT(DATETIME, CONVERT(DATE, lh.StartDateTime)) as Date,
                lh.TimePeriodID, 
                lh.StartDateTime 
         FROM   [dbo].[LogEquipment] le 
                JOIN [dbo].[LogArea] la 
                  ON le.LogAreaID = la.LogAreaID 
                JOIN [dbo].[LogHeader] lh 
                  ON lh.LogHeaderID = la.LogHeaderID 
         WHERE  lh.StartDateTime Between CAST('2015-01-14' AS DateTime)
         AND CAST('2015-01-16' AS DateTime)
        ), 

     Dates --Generate date range
     AS (SELECT CAST('2015-01-14' AS DateTime) 'date' 
         UNION ALL 
         SELECT Dateadd(dd, 1, t.date) 
         FROM   Dates t 
         WHERE  Dateadd(dd, 1, t.date) <= CAST('2015-01-16' AS DateTime)), 

     DesiredDatesAndTimePeriods --Generate datetimes for all timeperiods between date range
     AS (SELECT DISTINCT tp.TimePeriodID, 
                tp.TimeDescription, 
                tp.StartTime,
                d.Date,
                d.Date + CONVERT(DATETIME, tp.StartTime) AS LogStartDateTime,
                le.EquipmentStoreID
         FROM   dbo.TimePeriod tp 
                CROSS JOIN Dates d 
                CROSS JOIN LogEquipment le 
         WHERE  tp.IsActive = 1), 

     StagedRows 
     AS (SELECT * FROM
     (
     SELECT d.LogStartDateTime, 
            d.EquipmentStoreID, 
            t.LogEquipmentID, 
            t.CorrectiveAction, 
            CASE WHEN minlog.MinStartDateTime <= d.LogStartDateTime 
                AND (t.LogEquipmentID IS NULL OR CorrectiveAction IS NOT NULL)  
                THEN 1 ELSE 0 END AS FailedFlag,
            ROW_NUMBER() OVER (PARTITION BY d.Date, d.TimePeriodID 
                 ORDER BY CASE WHEN CorrectiveAction IS NULL THEN 0 ELSE 1 END) 
                 AS Priority
     FROM DesiredDatesAndTimePeriods d
         LEFT OUTER JOIN TargetLogs t 
             on d.Date = t.Date AND d.TimePeriodId = t.TimePeriodId
         LEFT OUTER JOIN (SELECT EquipmentStoreId, MIN(StartDateTime) as MinStartDateTime FROM TargetLogs GROUP BY EquipmentStoreId) minlog 
             on d.EquipmentStoreID = minlog.EquipmentStoreID 
    ) dt WHERE Priority = 1) 

SELECT EquipmentStoreID,
          Count(*) AS ConsecutiveFails, 
       Start_date = Min(LogStartDateTime), 
       Stop_date = Max(LogStartDateTime) 
FROM   (SELECT EquipmentStoreID,
                        FailedFlag, 
               LogStartDateTime, 
               ROW_NUMBER() 
                 OVER ( 
                   ORDER BY EquipmentStoreID, LogStartDateTime) - ROW_NUMBER() 
                                                  OVER ( 
                                                    PARTITION BY EquipmentStoreID, FailedFlag 
                                                    ORDER BY EquipmentStoreID, LogStartDateTime) 
               grp 
        FROM   StagedRows) A 
GROUP  BY EquipmentStoreID, FailedFlag, 
          grp 
HAVING FailedFlag = 1 
       AND Count(*) > 3 
ORDER  BY Min(LogStartDateTime)

Upvotes: 1

Related Questions