question

KrishnaRaoKandala-9898 avatar image
0 Votes"
KrishnaRaoKandala-9898 asked KrishnaRaoKandala-9898 commented

Select records with multiple consecutive zero values

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

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What column contains consecutive zeroes?


0 Votes 0 ·

Loading value is given by RATED_LOAD_PCT.

0 Votes 0 ·

Hi @KrishnaRaoKandala-9898

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data,We also need to see the expected result of the sample.


Regards
Echo

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ErlandSommarskog commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Viore-1,
Thank you for the quick response. I tried the above code and it gives me the results of those Equipment which starts working with the zero Loading for the selected number of records.
Now I realize that I need to look for those Equipment where there is a break in the Loading pattern.... where the Loading records are not continuous. The END_TIME which is an increment of 15 mins is not continuous. Any idea how I can search for the Equipment which meets that condition in a given period of USAGE_DATE?
TKrishna

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog KrishnaRaoKandala-9898 ·

So you mean that you want to find the Equipment which has recorded only zeroes for the last four hours, but there may be not exactly 16 rows, as there can be records missing (or extraneous records)?

0 Votes 0 ·
KrishnaRaoKandala-9898 avatar image
0 Votes"
KrishnaRaoKandala-9898 answered KrishnaRaoKandala-9898 commented

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

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi All,
Here is the code I compiled:

WITH CTE1 AS(
WITH CTE AS (
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"
)
SELECT ,
LAG("END_TIME",1,'20190101') OVER (ORDER BY RN) AS END_TIME_PREV
FROM CTE
)
SELECT
,
DATEDIFF (MINUTE, END_TIME_PREV, "END_TIME") AS TIME_DIFF
FROM CTE1

The code works well until compiling a column END_TIME_PREV. But I get an error to add a column for the TIME_DIFF. The error msg is:
"Could not execute 'WITH CTE1 AS( WITH CTE AS ( SELECT "EQUIPMENT", "END_TIME", "USAGE_DATE", "INTERVAL_NUMBER", ...'
SAP DBTech JDBC: [260]: invalid column name: MINUTE: line 23 col 12 (at pos 537)"

I tried using 'm', 'mi' in place of MINUTE but I am getting the same error.
Any suggestions where I am going wrong?

Krishna

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 KrishnaRaoKandala-9898 ·

Check if the syntax error is solved:

 ;
 WITH CTE AS (
 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"
 ),
 CTE1 as
 (
     SELECT *,
     LAG("END_TIME",1,'20190101') OVER (ORDER BY RN) AS END_TIME_PREV
     FROM CTE
 )
 SELECT *,
 DATEDIFF (MINUTE, END_TIME_PREV, "END_TIME") AS TIME_DIFF
 FROM CTE1
0 Votes 0 ·

Hi Viorel ..... It is not solved. I have the error msg:

Could not execute 'WITH CTE AS ( SELECT "EQUIPMENT", "END_TIME", "USAGE_DATE", "INTERVAL_NUMBER", "SUBSTATION", ...'
SAP DBTech JDBC: [260]: invalid column name: MINUTE: line 24 col 12 (at pos 564)

0 Votes 0 ·
Show more comments