Hi experts,
I'm newbie in SQL. I have 3 tables, MST_PACK, MST_WPREC_TEST & MST_SESSION.
MST_PACK - records of data a packing machine can pack a product hourly
MST_WPREC_TEST - records of data total wrapper needed to wrap a product
MST_SESSION - records of data the machine operation session (from 07xx to 15xx as 1st session, 15xx to 23xx as 2nd session every day)
These 3 tables are required to calculate the wrapper wastage. Refer to below sample data.
IF OBJECT_ID('MST_WPREC_TEST', 'U') IS NOT NULL DROP TABLE MST_WPREC_TEST;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE MST_WPREC_TEST(
[WPREC_ID] [int] IDENTITY(1,1) NOT NULL,
[WPREC_NOTE] [varchar](50) NULL,
[WPREC_CODE] [varchar](15) NULL,
[WPREC_REMARK] [varchar](50) NULL,
[WPREC_TYPE] [varchar](15) NULL,
[WPREC_WEIGHT] [float] NULL,
[WPREC_COUNTER] [float] NULL,
[WPREC_DATETIME] [datetime] NULL,
[WPREC_USER] [varchar](15) NULL,
[WPREC_VERIFY] [varchar](1) NULL,
[WPREC_VERIFYTIME] [datetime] NULL,
[WPREC_SUPERVISOR] [varchar](15) NULL,
[SES_NOTE] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT MST_WPREC_TEST ON
INSERT MST_WPREC_TEST ([WPREC_ID], [WPREC_NOTE], [WPREC_CODE], [WPREC_REMARK], [WPREC_TYPE], [WPREC_WEIGHT], [WPREC_COUNTER], [WPREC_DATETIME], [WPREC_USER], [WPREC_VERIFY], [WPREC_VERIFYTIME], [WPREC_SUPERVISOR], [SES_NOTE])
VALUES (1, N'A000000001', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 07:11:41.000' AS DateTime), N'', N'', NULL, N'', N'A000000001')
,(2, N'A000000002', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 07:20:58.000' AS DateTime), N'', N'', NULL, N'', N'A000000001')
,(3, N'A000000003', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 08:15:30.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(4, N'A000000004', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 08:24:39.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(5, N'A000000005', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 09:29:22.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(6, N'A000000006', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 09:31:24.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(7, N'A000000007', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 10:13:30.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(8, N'A000000008', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 10:30:31.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(9, N'A000000009', N'APOPAK', N'A1', N'Return', 1.8, 25600, CAST(N'2017-04-21 11:12:16.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(10, N'A000000010', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 11:18:24.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(11, N'A000000011', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 12:15:11.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(12, N'A000000012', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 12:20:43.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(13, N'A000000013', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 13:16:02.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
,(14, N'A000000014', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 13:18:18.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
,(15, N'A000000015', N'APOPAK', N'A1', N'Receive', 1.8, 26565, CAST(N'2017-04-21 14:18:32.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
,(16, N'A000000016', N'APOPAK', N'A1', N'Receive', 1.8, 26565, CAST(N'2017-04-21 14:23:27.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
,(17, N'A000000017', N'APOPAK', N'A1', N'Receive', 1.8, 29310, CAST(N'2017-04-21 15:09:26.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
--,(18, N'A000000018', N'APOPAK', N'A2', N'Receive', 1.8, 25600, CAST(N'2017-04-21 15:29:57.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(18, N'A000000018', N'APOPAK', N'A1', N'Return', 1.7, 25600, CAST(N'2017-04-21 16:25:49.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(19, N'A000000019', N'APOPAK', N'A2', N'Return', 1.8, 25600, CAST(N'2017-04-21 16:31:22.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(20, N'A000000020', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 17:11:43.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(21, N'A000000021', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 17:23:35.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(22, N'A000000022', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 18:19:42.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(23, N'A000000023', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 18:31:28.000' AS DateTime), N'suppda1', N'Y', CAST(N'2017-04-21 18:40:10.000' AS DateTime), N'suppda1', N'A000000078')
,(24, N'A000000024', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 19:13:26.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(25, N'A000000025', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 19:36:34.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(26, N'A000000026', N'APOPAK', N'A1', N'RECEIVE', 1.8, 26505, CAST(N'2017-04-21 20:12:01.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(27, N'A000000027', N'APOPAK', N'A1', N'RECEIVE', 1.8, 26505, CAST(N'2017-04-21 20:36:52.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(28, N'A000000028', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 21:13:22.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(29, N'A000000029', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 21:20:40.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(30, N'A000000030', N'APOPAK', N'A1', N'Receive', 2.4, 26565, CAST(N'2017-04-21 22:16:02.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(31, N'A000000031', N'APOPAK', N'A1', N'Receive', 2.5, 26565, CAST(N'2017-04-21 22:29:28.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(32, N'A000000032', N'APOPAK', N'A1', N'Receive', 1.8, 29310, CAST(N'2017-04-21 23:14:40.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(33, N'A000000033', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 07:15:41.000' AS DateTime), N'suppda1', N'', NULL, N'', N'')
,(34, N'A000000034', N'APOPAK', N'A1', N'Receive', 1.8, 56669, CAST(N'2017-04-22 07:20:36.000' AS DateTime), N'suppda1', N'', NULL, N'', N'')
,(35, N'A000000035', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 08:13:28.000' AS DateTime), N'suppda1', N'', NULL, N'', N'')
,(36, N'A000000036', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 08:20:03.000' AS DateTime), N'suppda1', N'Y', NULL, N'suppda1', N'')
,(37, N'A000000037', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 09:17:14.000' AS DateTime), N'suppda1', N'Y', NULL, N'suppda1', N'')
,(38, N'A000000038', N'APOPAK', N'A1', N'Receive', 1.8, 55633, CAST(N'2017-04-22 09:25:03.000' AS DateTime), N'suppda1', N'Y', NULL, N'suppda1', N'')
,(39, N'A000000039', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 10:16:49.000' AS DateTime), N'suppda1', N'Y', NULL, N'suppda1', N'')
,(40, N'A000000040', N'APOPAK', N'B1', N'Receive', 1.88, 55699, CAST(N'2017-04-22 10:30:04.000' AS DateTime), N'suppda1', N'', NULL, N'', N'')
GO
SET IDENTITY_INSERT MST_WPREC_TEST OFF
IF OBJECT_ID('MST_PACK', 'U') IS NOT NULL DROP TABLE MST_PACK;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE MST_PACK(
[RP_PRODUCT] [varchar](50) NULL,
[RP_START] [datetime] NULL,
[RP_END] [datetime] NULL,
[RP_TOTAL] [int] NULL, --Actual count of product packed by packing machine
[RP_SPEC] [int] NULL, --Amount of packing machine can pack a product theoretically
[RP_PER] [float] NULL --The packing machine efficiency, [RP_TOTAL] * 100 / [RP_SPEC]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT MST_PACK ON
GO
INSERT MST_PACK ([RP_PRODUCT], [RP_START], [RP_END], [RP_TOTAL], [RP_SPEC], [RP_PER])
VALUES (N'JK CURRY', CAST(N'2017-04-21 07:10:20.000' AS DateTime), CAST(N'2017-04-21 08:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 08:10:20.000' AS DateTime), CAST(N'2017-04-21 09:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 09:10:20.000' AS DateTime), CAST(N'2017-04-21 10:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 10:10:20.000' AS DateTime), CAST(N'2017-04-21 11:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'MK CURRY', CAST(N'2017-04-21 11:10:20.000' AS DateTime), CAST(N'2017-04-21 12:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 12:10:20.000' AS DateTime), CAST(N'2017-04-21 13:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 13:10:20.000' AS DateTime), CAST(N'2017-04-21 14:10:20.000' AS DateTime), 870, 880, 98.86)
,(N'JK CURRY', CAST(N'2017-04-21 14:10:20.000' AS DateTime), CAST(N'2017-04-21 15:10:20.000' AS DateTime), 870, 880, 98.86)
,(N'JK CURRY', CAST(N'2017-04-21 15:20:06.000' AS DateTime), CAST(N'2017-04-21 16:20:06.000' AS DateTime), 870, 880, 98.86)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 16:20:06.000' AS DateTime), CAST(N'2017-04-21 17:20:06.000' AS DateTime), 845, 880, 96.02)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 17:20:06.000' AS DateTime), CAST(N'2017-04-21 18:20:06.000' AS DateTime), 845, 880, 96.02)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 18:20:06.000' AS DateTime), CAST(N'2017-04-21 19:20:06.000' AS DateTime), 845, 880, 96.02)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 19:20:06.000' AS DateTime), CAST(N'2017-04-21 20:20:06.000' AS DateTime), 870, 880, 98.86)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 20:20:06.000' AS DateTime), CAST(N'2017-04-21 21:20:06.000' AS DateTime), 870, 880, 98.86)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 21:20:06.000' AS DateTime), CAST(N'2017-04-21 22:20:06.000' AS DateTime), 870, 880, 98.86)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 22:20:06.000' AS DateTime), CAST(N'2017-04-21 23:20:06.000' AS DateTime), 870, 880, 98.86)
GO
SET IDENTITY_INSERT MST_PACK OFF
GO
IF OBJECT_ID('MST_SESSION_TEST', 'U') IS NOT NULL DROP TABLE MST_SESSION_TEST;
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE MST_SESSION_TEST(
[SES_ID] [int] IDENTITY(1,1) NOT NULL,
[SES_NOTE] [varchar](10) PRIMARY KEY CLUSTERED NOT NULL,
[SES_START] [datetime] NULL,
[SES_END] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT MST_SESSION_TEST ON
GO
INSERT MST_SESSION_TEST ([SES_ID], [SES_NOTE], [SES_START], [SES_END])
VALUES (1, N'A000000001', CAST(N'2017-04-21 07:10:20.000' AS DateTime), CAST(N'2017-04-21 15:10:33.000' AS DateTime))
, (2, N'A000000078', CAST(N'2017-04-21 15:20:06.000' AS DateTime), CAST(N'2017-04-21 23:20:36.000' AS DateTime))
GO
SET IDENTITY_INSERT MST_SESSION_TEST OFF
GO
Desire result as below:
WPREC_CODE WPR_START_TIME COUNTER/HOUR WRAP_USED/HOUR WASTAGE(%)
APOPAK 2017-04-21 0700 25600 25350 0.98
APOPAK 2017-04-21 0800 25600 25350 0.98
APOPAK 2017-04-21 0900 25600 25350 0.98
APOPAK 2017-04-21 1000 25600 25350 0.98
APOPAK 2017-04-21 1100 25600 25350 0.98
APOPAK 2017-04-21 1200 26505 25350 4.36
APOPAK 2017-04-21 1300 26505 25350 4.36
APOPAK 2017-04-21 1400 26505 26100 1.53
APOPAK 2017-04-21 1500 29310 26100 10.95
APOPAK 2017-04-21 1600 26565 26100 1.75
APOPAK 2017-04-21 1700 25600 25350 0.98
APOPAK 2017-04-21 1800 25600 25350 0.98
APOPAK 2017-04-21 1900 25600 25350 0.98
APOPAK 2017-04-21 2000 26505 26100 1.53
APOPAK 2017-04-21 2100 26505 26100 1.53
APOPAK 2017-04-21 2200 26565 26100 1.75
APOPAK 2017-04-21 2300 29310 26100 10.95
where
- WPREC_CODE direct from table MST_WPREC_TEST
- Data of COUNTER/HOUR come from WPREC_COUNTER in table MST_WPREC_TEST (* just retrieve one unique WPREC_COUNTER value within hourly time)
- Value of WRAP_USED/HOUR come from RP_TOTAL in table MST_PACK with each entry value need to times variable (assume as 30 pack). Eg, 845 * 30 = 25,350.
- WASTAGE = [(COUNTER/HOUR - WRAP_USED/HOUR) * 100] / COUNTER/HOUR
No idea where to start. Appreciate someone can help.