CREATE OR REPLACE TABLE rel595.t3_scada_alarms_oper_mod_ots_states_cycles_2017_Events
|
ENGINE = Aria
|
WITH t3_scada_alarms_oper_mod_ots_states_cycles_2017_aux
|
AS
|
(
|
SELECT
|
A.id_cycles,
|
A.MachineID,
|
A.MachineUT,
|
A.ENVELOPE,
|
A.Cycle_DownTime_State,
|
A.Cycle_DownTime_State_Description,
|
A.Cycle_DateTimeON,
|
A.Cycle_DateTimeOFF,
|
A.`Cycle_Time[sec]`,
|
A.`Working Hours`,
|
A.Cycle_Alarm_Code_OrderByAlarm,
|
A.Cycle_Alarm_Description_OrderByAlarm,
|
A.Cycle_First_Alarm_Code,
|
A.Cycle_First_Alarm_Description,
|
A.Cycle_Alarm_OrderByDateTimeON,
|
A.Cycle_Alarm_Functional_Group,
|
A.Cycle_Alarm_Turbine_State_Request,
|
A.Cycle_Operation_Mode_Code,
|
A.Cycle_Operation_Mode_Description,
|
A.Cycle_Operation_Mode_Turbine_State_Request,
|
A.Cycle_Final_Reset_Type,
|
A.Cycle_Final_Reset_Criteria,
|
A.Cycle_Final_Availability,
|
A.Cycle_OT_NumOT,
|
A.Cycle_OT_Description,
|
A.Cycle_OT_AlarmCode,
|
A.Cycle_OT_AlarmDescription,
|
A.Cycle_OT_TypeCode,
|
A.Cycle_OT_TypeDescription,
|
A.Cycle_OT_Subtipo,
|
A.Cycle_OT_LCNId,
|
A.Cycle_OT_Planned,
|
A.Cycle_OT_With_Consumption,
|
A.Cycle_OT_Availability_Stop,
|
B.Event_Code,
|
B.Event_Description,
|
B.DateTimeON
|
AS Event_DateTimeON,
|
B.DateTimeOFF
|
AS Event_DateTimeOFF,
|
MAX(B.DateTimeOFF)
|
OVER (PARTITION BY A.id_cycles)
|
AS Event_Max_DateTimeOFF
|
FROM
|
rel595.t3_scada_alarms_oper_mod_ots_states_cycles_2017 AS A
|
LEFT JOIN rel595.t1_scada_events_joined_dbvar_17543 AS B
|
ON
|
B.DateTimeON BETWEEN A.Cycle_DateTimeON AND A.Cycle_DateTimeOFF
|
AND A.MachineUT = B.MachineUT
|
)
|
SELECT
|
MachineID,
|
MachineUT,
|
ENVELOPE,
|
Cycle_DownTime_State,
|
Cycle_DownTime_State_Description,
|
Cycle_DateTimeON,
|
Cycle_DateTimeOFF,
|
`Cycle_Time[sec]`,
|
`Working Hours`,
|
Cycle_Alarm_Code_OrderByAlarm,
|
Cycle_Alarm_Description_OrderByAlarm,
|
Cycle_First_Alarm_Code,
|
Cycle_First_Alarm_Description,
|
Cycle_Alarm_OrderByDateTimeON,
|
Cycle_Alarm_Functional_Group,
|
Cycle_Alarm_Turbine_State_Request,
|
Cycle_Operation_Mode_Code,
|
Cycle_Operation_Mode_Description,
|
Cycle_Operation_Mode_Turbine_State_Request,
|
Cycle_Final_Reset_Type,
|
Cycle_Final_Reset_Criteria,
|
Cycle_Final_Availability,
|
Cycle_OT_NumOT,
|
Cycle_OT_Description,
|
Cycle_OT_AlarmCode,
|
Cycle_OT_AlarmDescription,
|
Cycle_OT_TypeCode,
|
Cycle_OT_TypeDescription,
|
Cycle_OT_Subtipo,
|
Cycle_OT_LCNId,
|
Cycle_OT_Planned,
|
Cycle_OT_With_Consumption,
|
Cycle_OT_Availability_Stop,
|
CASE
|
WHEN
|
Event_Code IS NOT NULL
|
AND NOT Cycle_Final_Reset_Criteria = 'LOCAL-WO'
|
AND NOT Cycle_Alarm_Code_OrderByAlarm NOT REGEXP '903|904'
|
-- Not Alarm Codes "903: Authoriz. person in turbine"
|
-- nor "904:Presence of unauth. person in turbine".
|
THEN
|
1
|
ELSE
|
0
|
END AS `WTG_Reset_by_SCADA_Command`,
|
CASE
|
WHEN
|
Event_DateTimeOFF BETWEEN SUBTIME(Cycle_DateTimeOFF, '00:10:00') AND ADDTIME(Cycle_DateTimeOFF, '00:10:00')
|
AND
|
(
|
Event_Code IS NOT NULL
|
AND NOT Cycle_Final_Reset_Criteria = 'LOCAL-WO'
|
AND NOT Cycle_Alarm_Code_OrderByAlarm NOT REGEXP '903|904'
|
-- Not Alarm Codes "903: Authoriz. person in turbine"
|
-- nor "904:Presence of unauth. person in turbine".
|
)
|
AND
|
(
|
ADDTIME(Cycle_DateTimeOFF, '00:30:00') < LEAD(Cycle_DateTimeON) OVER (PARTITION BY MachineID ORDER BY MachineID, Cycle_DateTimeON)
|
OR (LEAD(Cycle_DateTimeON) OVER (PARTITION BY MachineID ORDER BY MachineID, Cycle_DateTimeON) IS NULL)
|
)
|
THEN
|
1
|
ELSE
|
0
|
END AS `WTG_SCADA_Command_Success_Offset10`,
|
TIMESTAMPDIFF(MINUTE, Event_DateTimeOFF, Cycle_DateTimeOFF)
|
AS DateTimeDiff_StateOFF_EventOFF,
|
Event_Code,
|
Event_Description,
|
Event_DateTimeON,
|
Event_DateTimeOFF
|
FROM
|
t3_scada_alarms_oper_mod_ots_states_cycles_2017_aux
|
WHERE
|
(Event_DateTimeOFF = Event_Max_DateTimeOFF)
|
OR Event_DateTimeOFF IS NULL
|
GROUP BY
|
id_cycles
|
ORDER BY
|
MachineID,
|
Cycle_DateTimeON
|
MDEV-13552.xlsx
NGALARRETA-P700-error.log
mdev13552.test
