I need to build a dashboard which looks like -
Up Time %:
| Shift | Last 24 hrs | Last 7 days | Last 30 days |
|---|---|---|---|
| 1st | % time running | % time running | % time running |
| 2nd | % time running | % time running | % time running |
| 3rd | % time running | % time running | % time running |
| Total Shifts | % time running | % time running | % time running |
Basically, I need the time the machine has been running across the shifts per CELL. I have listed 2 cells here.
Cycle Complete = 1 is successful, = 0 is unsuccessful.
StartStopTrigger = 0 is start, =1 is Stop.
WE use the PLC Time as the time frame, DTStamp is only for audit purposes.
Shift 1 : 6:30 – 14:30
Shift2 : 14:30 – 22:30
Shift 3 : 22:30 – 6:30
%Time Running = Run Time / Total Time
Table BANK1RAW -
CREATE TABLE [dbo].[BANK1RAW](
[DTStamp] NOT NULL,
[PLCTime] NOT NULL,
[CellNumber] [int] NOT NULL,
[StartStopTrigger] [bit] NOT NULL,
[ReasonCode] [int] NOT NULL,
[Product] nvarchar NOT NULL,
[Shift] [int] NOT NULL,
[CycleComplete] [bit] NOT NULL
) ON [PRIMARY]
Sample DATA is –
Insert into BANK1RAW Values (GetDate(), '2018-09-27 5:53:17', '1', '1','0', 'C-300', '3', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 5:58:17', '1', '0','0', 'C-300', '3', '1')
Time Running here is 5 minutes, Shift 3
Insert into BANK1RAW Values (GetDate(), '2018-09-27 6:03:17', '1', '1','0', 'C-300', '3', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 6:08:17', '1', '0','0', 'C-300', '3', '1')
Time Running here is 5 minutes, Shift 3
Insert into BANK1RAW Values (GetDate(), '2018-09-27 10:53:17', '1', '1','0', 'C-300', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 10:58:17', '1', '0','0', 'C-300', '1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:03:17', '1', '1','0', 'C-300', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:08:17', '1', '0','0', 'C-300', '1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:10:17', '2', '1','0', 'C-500', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:12:17', '2', '0','7', 'C-500', '1', '0')
Time Running here is 2 minutes, Shift 1, with a fault error code 7.
Cycle is not complete.
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:13:17', '2', '1','0', 'C-500', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:28:17', '2', '0','0', 'C-500', '1', '1')
Time Running here is 15 minutes, Shift 1, After fault 7 was corrected.
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:30:17', '1', '1','0', 'C-300', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:38:17', '1', '0','0', 'C-300', '1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:43:17', '1', '1','0', 'C-300', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 11:48:17', '1', '0','0', 'C-300', '1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 17:30:17', '1', '1','0', 'C-300', '2', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 17:38:17', '1', '0','0', 'C-300', '2', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 17:43:17', '1', '1','0', 'C-300', ‘2', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-27 17:48:17', '1', '0','0', 'C-300', '2', '1')
LAST 7 DAYS
Insert into BANK1RAW Values (GetDate(), '2018-09-25 11:10:17', '2', '1','0', 'C-700', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-25 11:12:17', '2', '0','7', 'C-700', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-25 11:13:17', '2', '1','0', 'C-700', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-25 11:28:17', '2', '0','0', 'C-700', '1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-25 11:30:17', '1', '1','0', 'C-700', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-25 11:38:17', '1', '0','0', 'C-700', '1', '1')
Insert into BANK1RAW Values (GetDate(), '2018-09-25 11:43:17', '1', '1','0', 'C-700', '1', '0')
Insert into BANK1RAW Values (GetDate(), '2018-09-25 11:48:17', '1', '0','0', 'C-700', '1', '1')