We have on/Off (0/1) entries in source table for 12 machines
Cerate 2 Stored Procedures which will take start time and current system time and calcuate on-off duration for each machine
and populate 2 target tables
1. Scheduler SP
- Calculate On-off duration for each machine for scheduled time (5 mins)
2. Program SP
- Calucate On-off duration for ech machine after scheduledes SP is called till current time time (less than 5 mins)
Source Table
id, event_time, line_id, dt, tm, M01, M02, M03, M04, M05, M06, M07, M08, M09, M10, M11, M12, M13, M14, M15, M16, M17, M18, M19, M20, M21, M22, M23, M24, uploaded
Target Table 1
id, machine_id, on_start_time, on_end_time, off_start_time, off_end_time, on_duration, off_duration
Target Table 2
id, event_time, line_id, dt, tm, M01, M01_on, M01_off, M02, M02_on, M02_off, M03, M03_on, M03_off, M04, M04_on, M04_off, M05, M05_on, M05_off, M06, M06_on, M06_off, M07, M07_on, M07_off, M08, M08_on, M08_off, M09, M09_on, M09_off, M10, M10_on, M10_off, M11, M11_on, M11_off, M12, M12_on, M12_off, uploaded
Sample data can be provided