Select records with multiple consecutive zero values

Krishna Rao Kandala 21 Reputation points
2021-09-14T15:30:10.097+00:00

We have a table that records percentage loading capacity of the Equipment every 15 minutes. Since we have a large number of Equipment, the table is very large. I want to select the Equipment that has recorded consecutive zero values of loading for 4 hours at a time during a specific time period (say one year)... that is 16 consecutive zero values of loading capacity.
I compiled the following code that selects the Equipment which has a zero loading during the time period, but do not know how to extend the logic to include 16 consecutive zero values.
WITH CTE AS (
SELECT
DISTINCT "EQUIPMENT"
FROM "_SYS_BIC"."ZPSE.PLANT_MAINTENANCE.ASSET.FIN/CV_SINGLE_TRFN_OVERLOAD_USAGE"
WHERE ("USAGE_DATE">'20210331') AND ("RATED_LOAD_PCT" = 0)

)
SELECT ,
/
"EQUIPMENT"
"END_TIME",
"USAGE_DATE",
"INTERVAL_NUMBER",
"SUBSTATION",
"ZGRIDNBR",
"CIRCUIT",
"COMPANY_ID",
"STATUS",
"RATED_LOAD_PCT",
"LOAD_STATUS", */
ROW_NUMBER() OVER ( ORDER BY "EQUIPMENT", "USAGE_DATE", "INTERVAL_NUMBER") AS RN
FROM "_SYS_BIC"."ZPSE.PLANT_MAINTENANCE.ASSET.FIN/CV_SINGLE_TRFN_OVERLOAD_USAGE"
WHERE ("USAGE_DATE">'20210331') AND ("EQUIPMENT" IN (
SELECT * FROM CTE
))
The above code is resulting in the table with row numbers and ordered by Equipment, Usage_Date and Interval_Number.
A day has 96 (24x4) intervals for measurements every 15 minutes.
Please assist me to select Equipment which has consecutive zero values as explained above.
Thank you
Krishna

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 110.7K Reputation points
    2021-09-14T18:38:19.9+00:00

    Try something like this:

    ;
    with Q as
    (
        select *, 
            count(case RATED_LOAD_PCT when 0 then 0 end) 
                over (partition by EQUIPMENT order by USAGE_DATE, INTERVAL_NUMBER
                    rows 15 preceding) c
        from [_SYS_BIC].[ZPSE.PLANT_MAINTENANCE.ASSET.FIN/CV_SINGLE_TRFN_OVERLOAD_USAGE]
        where USAGE_DATE > '2021-03-31'
    )
    select distinct EQUIPMENT
    from Q
    where c = 16
    order by EQUIPMENT
    

  2. Krishna Rao Kandala 21 Reputation points
    2021-09-16T13:48:23.94+00:00

    Hi Erland... thanks for the response. Let me explain the situation ... when an Equipment (in this case a Dist. Transformer) gets inactivated (and eventually replaced) there will be a break in the sequence of recorded Loading measurements. We are trying to identify those Equipment to study the loading pattern just BEFORE it stopped functioning. Here the zero values are not important... it is the break in the sequence of continuous operation. We have more than 60K of this Equipment installed in the field and each Equipment has 24x4 = 96 measurements every day.
    I tried to use the LAG function to record the previous row's END_TIME as a separate END_TIME_PREV column ; have another column to calculate the difference between END_TIME and END_TIME_PREV; if this exceeds the 15 min interval, select that EQUIPMENT. As I do not normally work with SQL, I could not write the correct code.
    We can select the date range for our study with USAGE_DATE.... as USAGE_DATE BETWEEN '20200901' AND '20210831'.
    END_TIME is a DateTime data type.
    Hope that I clarified my objective... please help..
    Thanks again
    Krishna