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