Hourly Sum of Data

Question

Hourly Sum of Data

madcloud97

SSC Enthusiast

Points: 171
More actions
July 19, 2017 at 12:42 am

#322766

Hi,
Newbie in SQL. I have 2 tables session & record. Session table stored job start & end time data in 2 session, normally start on 0700 to 1500 (first session), 1500 to 2300 (2nd session). The record table is the main table record total amount of a packing machine can pack a product every 5 or 10 minutes. Refer below table code & sample data.
IF OBJECT_ID('tempdb..#MST_REC_TEST', 'U') IS NOT NULL DROP TABLE #MST_REC_TEST;
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO
--This is the main table recording how many product packed by machine every 5 or 10 minutes CREATE TABLE #MST_REC_TEST( [REC_ID] [int] IDENTITY(1,1) NOT NULL, [REC_NOTE] [varchar](10) PRIMARY KEY CLUSTERED NOT NULL, [REC_DATETIME] [datetime] NULL, [REC_BUYERNO] [varchar](20) NULL, [REC_BUYER] [varchar](50) NULL, [REC_SKU] [varchar](20) NULL, [REC_PRODUCT] [varchar](50) NULL, [REC_SIZE] [varchar](20) NULL, [REC_TOTAL] [int] NULL, [USER_NAME] [varchar](15) NULL, [REC_VERIFY] [varchar](1) NULL, [REC_VERIFYTIME] [datetime] NULL, [REC_SUPERVISOR] [varchar](15) NULL, [SES_NOTE] [varchar](10) NULL ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO
SET IDENTITY_INSERT #MST_REC_TEST ON GO INSERT #MST_REC_TEST ([REC_ID], [REC_NOTE], [REC_DATETIME], [REC_BUYERNO], [REC_BUYER], [REC_SKU], [REC_PRODUCT], [REC_SIZE], [REC_TOTAL], [USER_NAME], [REC_VERIFY], [REC_VERIFYTIME], [REC_SUPERVISOR], [SES_NOTE]) VALUES (1, N'A000000001', CAST(N'2017-04-21 07:10:20.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda1', N'', NULL, N'', N'A000000001') , (2, N'A000000002', CAST(N'2017-04-21 07:15:11.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 97, N'pda1', N'', NULL, N'', N'A000000001') , (3, N'A000000003', CAST(N'2017-04-21 07:20:33.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'pda1', N'', NULL, N'', N'A000000001') , (4, N'A000000004', CAST(N'2017-04-21 07:30:06.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 111, N'pda1', N'', NULL, N'', N'A000000001') , (5, N'A000000005', CAST(N'2017-04-21 07:40:23.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 120, N'pda1', N'', NULL, N'', N'A000000001') , (6, N'A000000006', CAST(N'2017-04-21 07:45:44.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'pda1', N'', NULL, N'', N'A000000001') , (7, N'A000000007', CAST(N'2017-04-21 07:50:18.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 92, N'pda1', N'', NULL, N'', N'A000000001') , (8, N'A000000008', CAST(N'2017-04-21 08:00:15.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 87, N'pda1', N'', NULL, N'', N'A000000001') , (9, N'A000000009', CAST(N'2017-04-21 08:05:01.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'MK CURRY', N'1x30', 95, N'pda1', N'', NULL, N'', N'A000000001') , (10, N'A000000010', CAST(N'2017-04-21 08:10:22.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001') , (11, N'A000000011', CAST(N'2017-04-21 08:20:38.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 126, N'pda1', N'', NULL, N'', N'A000000001') , (12, N'A000000012', CAST(N'2017-04-21 08:30:50.000' AS DateTime), N'ABC1234568', N'1x30', N'ABC1234568', N'MK CURRY', N'1x30', 118, N'pda1', N'', NULL, N'', N'A000000001') , (13, N'A000000013', CAST(N'2017-04-21 08:40:27.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 130, N'pda1', N'', NULL, N'', N'A000000001') , (14, N'A000000014', CAST(N'2017-04-21 08:45:48.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'pda1', N'', NULL, N'', N'A000000001') , (15, N'A000000015', CAST(N'2017-04-21 08:50:36.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001') , (16, N'A000000016', CAST(N'2017-04-21 09:00:58.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 98, N'pda1', N'', NULL, N'', N'A000000001') , (17, N'A000000017', CAST(N'2017-04-21 09:05:54.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'pda1', N'', NULL, N'', N'A000000001') , (18, N'A000000018', CAST(N'2017-04-21 09:10:12.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 95, N'pda1', N'', NULL, N'', N'A000000001') , (19, N'A000000019', CAST(N'2017-04-21 09:20:29.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001') , (20, N'A000000020', CAST(N'2017-04-21 09:25:25.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001') , (21, N'A000000021', CAST(N'2017-04-21 09:30:29.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 100, N'pda1', N'', NULL, N'', N'A000000001') , (22, N'A000000022', CAST(N'2017-04-21 09:40:45.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 130, N'pda1', N'', NULL, N'', N'A000000001') , (23, N'A000000023', CAST(N'2017-04-21 09:50:31.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 120, N'pda1', N'', NULL, N'', N'A000000001') , (24, N'A000000024', CAST(N'2017-04-21 10:00:02.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 110, N'pda1', N'', NULL, N'', N'A000000001') , (25, N'A000000025', CAST(N'2017-04-21 10:05:05.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 111, N'pda1', N'', NULL, N'', N'A000000001') , (26, N'A000000026', CAST(N'2017-04-21 10:10:23.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 77, N'pda1', N'Y', CAST(N'2017-05-02 15:39:29.000' AS DateTime), N'suppda1', N'A000000001') , (27, N'A000000027', CAST(N'2017-04-21 10:15:56.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 69, N'suppda1', N'', NULL, N'', N'A000000001') , (28, N'A000000028', CAST(N'2017-04-21 10:20:01.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'suppda1', N'', NULL, N'', N'A000000001') , (29, N'A000000029', CAST(N'2017-04-21 10:25:02.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 85, N'pda1', N'', NULL, N'', N'A000000001') , (30, N'A000000030', CAST(N'2017-04-21 10:30:07.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'suppda1', N'', NULL, N'', N'A000000001') , (31, N'A000000031', CAST(N'2017-04-21 10:35:09.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'pda1', N'', NULL, N'', N'A000000001') , (32, N'A000000032', CAST(N'2017-04-21 10:40:13.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 60, N'pda1', N'', NULL, N'', N'A000000001') , (33, N'A000000033', CAST(N'2017-04-21 10:50:41.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 120, N'pda1', N'', NULL, N'', N'A000000001') , (34, N'A000000034', CAST(N'2017-04-21 10:55:48.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 59, N'pda1', N'', NULL, N'', N'A000000001') , (35, N'A000000035', CAST(N'2017-04-21 11:00:10.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 78, N'pda1', N'', NULL, N'', N'A000000001') , (36, N'A000000036', CAST(N'2017-04-21 11:05:23.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 82, N'pda1', N'', NULL, N'', N'A000000001') , (37, N'A000000037', CAST(N'2017-04-21 11:10:11.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001') , (38, N'A000000038', CAST(N'2017-04-21 11:20:26.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 119, N'pda1', N'', NULL, N'', N'A000000001') , (39, N'A000000039', CAST(N'2017-04-21 11:25:33.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 75, N'pda1', N'', NULL, N'', N'A000000001') , (40, N'A000000040', CAST(N'2017-04-21 11:30:56.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 76, N'pda1', N'Y', CAST(N'2017-05-17 16:40:48.000' AS DateTime), N'suppda1', N'A000000001') , (41, N'A000000041', CAST(N'2017-04-21 11:35:43.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 67, N'pda1', N'', NULL, N'', N'A000000001') , (42, N'A000000042', CAST(N'2017-04-21 11:40:24.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 84, N'pda1', N'', NULL, N'', N'A000000001') , (43, N'A000000043', CAST(N'2017-04-21 11:50:12.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001') , (44, N'A000000044', CAST(N'2017-04-21 11:55:28.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 74, N'pda1', N'', NULL, N'', N'A000000001') , (45, N'A000000045', CAST(N'2017-04-21 12:00:01.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 80, N'pda1', N'', NULL, N'', N'A000000001') , (46, N'A000000046', CAST(N'2017-04-21 12:05:04.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 77, N'pda1', N'', NULL, N'', N'A000000001') , (47, N'A000000047', CAST(N'2017-04-21 12:10:17.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 90, N'pda1', N'', NULL, N'', N'A000000001') , (48, N'A000000048', CAST(N'2017-04-21 12:20:41.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 89, N'pda1', N'', NULL, N'', N'A000000001') , (49, N'A000000049', CAST(N'2017-04-21 12:25:11.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 88, N'pda1', N'', NULL, N'', N'A000000001') , (50, N'A000000050', CAST(N'2017-04-21 12:30:54.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda2', N'', NULL, N'', N'A000000001') , (51, N'A000000051', CAST(N'2017-04-21 12:35:31.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 79, N'pda1', N'', NULL, N'', N'A000000001') , (52, N'A000000052', CAST(N'2017-04-21 12:40:21.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda1', N'', NULL, N'', N'A000000001') , (53, N'A000000053', CAST(N'2017-04-21 12:50:14.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'pda1', N'', NULL, N'', N'A000000001') , (54, N'A000000054', CAST(N'2017-04-21 12:55:09.000' AS DateTime), N'MB145103632', N'1x100', N'MB145103632', N'ZEBRA PRINTER', N'1x100', 90, N'pda1', N'', NULL, N'', N'A000000001') , (55, N'A000000055', CAST(N'2017-04-21 13:00:02.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 93, N'pda1', N'', NULL, N'', N'A000000001') , (56, N'A000000056', CAST(N'2017-04-21 13:05:08.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda1', N'', NULL, N'', N'A000000001') , (57, N'A000000057', CAST(N'2017-04-21 13:10:50.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 70, N'pda1', N'', NULL, N'', N'A000000001') , (58, N'A000000058', CAST(N'2017-04-21 13:20:44.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 65, N'pda1', N'', NULL, N'', N'A000000001') , (59, N'A000000059', CAST(N'2017-04-21 13:25:37.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda1', N'', NULL, N'', N'A000000001') , (60, N'A000000060', CAST(N'2017-04-21 13:30:12.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'pda1', N'', NULL, N'', N'A000000001') , (61, N'A000000061', CAST(N'2017-04-21 13:35:23.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 70, N'pda1', N'', NULL, N'', N'A000000001') , (62, N'A000000062', CAST(N'2017-04-21 13:40:41.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 78, N'pda1', N'', NULL, N'', N'A000000001') , (63, N'A000000063', CAST(N'2017-04-21 13:50:13.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 120, N'pda1', N'', NULL, N'', N'A000000001') , (64, N'A000000064', CAST(N'2017-04-21 13:55:13.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 85, N'pda1', N'', NULL, N'', N'A000000001') , (65, N'A000000065', CAST(N'2017-04-21 14:00:09.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 77, N'pda1', N'', NULL, N'', N'A000000001') , (66, N'A000000066', CAST(N'2017-04-21 14:05:00.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 86, N'pda1', N'', NULL, N'', N'A000000001') , (67, N'A000000067', CAST(N'2017-04-21 14:10:49.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'pda1', N'', NULL, N'', N'A000000001') , (68, N'A000000068', CAST(N'2017-04-21 14:15:18.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 91, N'pda1', N'', NULL, N'', N'A000000001') , (69, N'A000000069', CAST(N'2017-04-21 14:20:13.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 65, N'pda1', N'', NULL, N'', N'A000000001') , (70, N'A000000070', CAST(N'2017-04-21 14:25:19.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 80, N'pda1', N'', NULL, N'', N'A000000001') , (71, N'A000000071', CAST(N'2017-04-21 14:30:48.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 80, N'pda1', N'', NULL, N'', N'A000000001') , (72, N'A000000072', CAST(N'2017-04-21 14:35:37.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 71, N'pda1', N'', NULL, N'', N'A000000001') , (73, N'A000000073', CAST(N'2017-04-21 14:40:44.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 75, N'pda1', N'', NULL, N'', N'A000000001') , (74, N'A000000074', CAST(N'2017-04-21 14:50:51.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 77, N'pda1', N'', NULL, N'', N'A000000001') , (75, N'A000000075', CAST(N'2017-04-21 14:55:27.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 76, N'pda1', N'', NULL, N'', N'A000000001') , (76, N'A000000076', CAST(N'2017-04-21 15:00:35.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 69, N'pda1', N'', NULL, N'', N'A000000001') , (77, N'A000000077', CAST(N'2017-04-21 15:05:10.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 79, N'pda1', N'', NULL, N'', N'A000000001') , (78, N'A000000078', CAST(N'2017-04-21 15:10:33.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001') , (79, N'A000000079', CAST(N'2017-04-21 15:20:06.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 80, N'pda1', N'', NULL, N'', N'A000000079') , (80, N'A000000080', CAST(N'2017-04-21 15:30:23.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 82, N'pda1', N'', NULL, N'', N'A000000079') , (81, N'A000000081', CAST(N'2017-04-21 15:35:44.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 70, N'pda2', N'', NULL, N'', N'A000000079') , (82, N'A000000082', CAST(N'2017-04-21 15:40:18.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 60, N'pda1', N'', NULL, N'', N'A000000079') , (83, N'A000000083', CAST(N'2017-04-21 15:50:15.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000079') , (84, N'A000000084', CAST(N'2017-04-21 15:55:01.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 111, N'pda1', N'', NULL, N'', N'A000000079') , (85, N'A000000085', CAST(N'2017-04-21 16:00:22.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 74, N'pda1', N'', NULL, N'', N'A000000079') , (86, N'A000000086', CAST(N'2017-04-21 16:05:38.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 81, N'pda1', N'', NULL, N'', N'A000000079') , (87, N'A000000087', CAST(N'2017-04-21 16:10:50.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 85, N'pda1', N'', NULL, N'', N'A000000079') , (88, N'A000000088', CAST(N'2017-04-21 16:20:27.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 84, N'pda1', N'', NULL, N'', N'A000000079') , (89, N'A000000089', CAST(N'2017-04-21 16:30:48.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 136, N'suppda1', N'', NULL, N'', N'A000000079') , (90, N'A000000090', CAST(N'2017-04-21 16:40:36.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 114, N'pda1', N'', NULL, N'', N'A000000079') , (91, N'A000000091', CAST(N'2017-04-21 16:45:58.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 106, N'suppda1', N'', NULL, N'', N'A000000079') , (92, N'A000000092', CAST(N'2017-04-21 16:50:54.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 83, N'pda1', N'', NULL, N'', N'A000000079') , (93, N'A000000093', CAST(N'2017-04-21 16:55:12.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 60, N'pda1', N'', NULL, N'', N'A000000079') , (94, N'A000000094', CAST(N'2017-04-21 17:00:29.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 89, N'pda1', N'Y', CAST(N'2017-05-23 09:55:42.000' AS DateTime), N'suppda1', N'A000000079') , (95, N'A000000095', CAST(N'2017-04-21 17:05:25.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 99, N'suppda1', N'Y', CAST(N'2017-05-23 09:48:50.000' AS DateTime), N'suppda1', N'A000000079') , (96, N'A000000096', CAST(N'2017-04-21 17:10:29.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 99, N'suppda1', N'Y', CAST(N'2017-05-23 09:37:56.000' AS DateTime), N'suppda1', N'A000000079') , (97, N'A000000097', CAST(N'2017-04-21 17:20:45.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 73, N'pda1', N'Y', CAST(N'2017-05-23 16:21:38.000' AS DateTime), N'suppda1', N'A000000079') , (98, N'A000000098', CAST(N'2017-04-21 17:30:31.000' AS DateTime), N'10304202A', N'10X40X25G', N'10304202A', N'MNS 30C BBQ 10X40X25G', N'10X40X25G', 120, N'suppda1', N'', NULL, N'', N'A000000079') , (99, N'A000000099', CAST(N'2017-04-21 17:35:02.000' AS DateTime), N'18312101', N'10X12X25G', N'18312101', N'MNS FP (SINGAPORE) CHICKEN', N'10X12X25G', 125, N'suppda1', N'', NULL, N'', N'A000000079') , (100, N'A000000100', CAST(N'2017-04-21 17:40:05.000' AS DateTime), N'18314402', N'10X40X25G', N'18314402', N'MNS (EX) BBQ', N'10X40X25G', 75, N'suppda1', N'', NULL, N'', N'A000000079') , (101, N'A000000101', CAST(N'2017-04-21 17:45:23.000' AS DateTime), N'18312101', N'10X12X25G', N'18312101', N'MNS FP (SINGAPORE) CHICKEN', N'10X12X25G', 60, N'suppda1', N'', NULL, N'', N'A000000079') , (102, N'A000000102', CAST(N'2017-04-21 17:50:56.000' AS DateTime), N'10304202T', N'10X40X25G', N'10304202T', N'MNS 30C BBQ TESTING', N'10X40X25G', 76, N'suppda1', N'Y', CAST(N'2017-06-22 11:25:17.000' AS DateTime), N'suppda1', N'A000000079') , (103, N'A000000103', CAST(N'2017-04-21 17:55:01.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 64, N'suppda1', N'Y', CAST(N'2017-05-23 09:56:50.000' AS DateTime), N'suppda1', N'A000000079') , (104, N'A000000104', CAST(N'2017-04-21 18:00:02.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 79, N'suppda1', N'Y', CAST(N'2017-05-23 10:39:11.000' AS DateTime), N'suppda1', N'A000000079') , (105, N'A000000105', CAST(N'2017-04-21 18:05:07.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 80, N'suppda1', N'Y', CAST(N'2017-05-23 10:48:24.000' AS DateTime), N'suppda1', N'A000000079') , (106, N'A000000106', CAST(N'2017-04-21 18:10:25.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 75, N'suppda1', N'Y', CAST(N'2017-05-23 16:21:35.000' AS DateTime), N'suppda1', N'A000000079') , (107, N'A000000107', CAST(N'2017-04-21 18:20:29.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 70, N'suppda1', N'Y', CAST(N'2017-05-23 16:21:37.000' AS DateTime), N'suppda1', N'A000000079') , (108, N'A000000108', CAST(N'2017-04-21 18:25:45.000' AS DateTime), N'10304202T', N'10X40X25G', N'10304202T', N'MNS 30C BBQ TESTING', N'10X40X25G', 89, N'suppda1', N'', NULL, N'', N'A000000079') , (109, N'A000000109', CAST(N'2017-04-21 18:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 79, N'pda1', N'', NULL, N'', N'A000000079') , (110, N'A000000110', CAST(N'2017-04-21 18:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 89, N'pda1', N'', NULL, N'', N'A000000079') , (111, N'A000000111', CAST(N'2017-04-21 18:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 68, N'pda1', N'', NULL, N'', N'A000000079') , (112, N'A000000112', CAST(N'2017-04-21 18:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 70, N'pda1', N'', NULL, N'', N'A000000079') , (113, N'A000000113', CAST(N'2017-04-21 18:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 130, N'pda1', N'', NULL, N'', N'A000000079') , (114, N'A000000114', CAST(N'2017-04-21 19:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 77, N'pda1', N'', NULL, N'', N'A000000079') , (115, N'A000000115', CAST(N'2017-04-21 19:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 60, N'pda1', N'', NULL, N'', N'A000000079') , (116, N'A000000116', CAST(N'2017-04-21 19:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 111, N'pda1', N'', NULL, N'', N'A000000079') , (117, N'A000000117', CAST(N'2017-04-21 19:20:29.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 85, N'pda1', N'', NULL, N'', N'A000000079') , (118, N'A000000118', CAST(N'2017-04-21 19:25:45.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 84, N'pda1', N'', NULL, N'', N'A000000079') , (119, N'A000000119', CAST(N'2017-04-21 19:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 73, N'pda1', N'', NULL, N'', N'A000000079') , (120, N'A000000120', CAST(N'2017-04-21 19:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 80, N'pda1', N'', NULL, N'', N'A000000079') , (121, N'A000000121', CAST(N'2017-04-21 19:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 69, N'pda1', N'', NULL, N'', N'A000000079') , (122, N'A000000122', CAST(N'2017-04-21 19:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 70, N'pda1', N'', NULL, N'', N'A000000079') , (123, N'A000000123', CAST(N'2017-04-21 19:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 120, N'pda1', N'', NULL, N'', N'A000000079') , (124, N'A000000124', CAST(N'2017-04-21 20:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 77, N'pda1', N'', NULL, N'', N'A000000079') , (125, N'A000000125', CAST(N'2017-04-21 20:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 83, N'pda1', N'', NULL, N'', N'A000000079') , (126, N'A000000126', CAST(N'2017-04-21 20:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 117, N'pda1', N'', NULL, N'', N'A000000079') , (127, N'A000000127', CAST(N'2017-04-21 20:20:29.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 83, N'pda1', N'', NULL, N'', N'A000000079') , (128, N'A000000128', CAST(N'2017-04-21 20:25:45.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 79, N'pda1', N'', NULL, N'', N'A000000079') , (129, N'A000000129', CAST(N'2017-04-21 20:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 66, N'pda1', N'', NULL, N'', N'A000000079') , (130, N'A000000130', CAST(N'2017-04-21 20:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 65, N'pda1', N'', NULL, N'', N'A000000079') , (131, N'A000000131', CAST(N'2017-04-21 20:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 80, N'pda1', N'', NULL, N'', N'A000000079') , (132, N'A000000132', CAST(N'2017-04-21 20:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 74, N'pda1', N'', NULL, N'', N'A000000079') , (133, N'A000000133', CAST(N'2017-04-21 20:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 120, N'pda1', N'', NULL, N'', N'A000000079') , (134, N'A000000134', CAST(N'2017-04-21 21:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 87, N'pda1', N'', NULL, N'', N'A000000079') , (135, N'A000000135', CAST(N'2017-04-21 21:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 81, N'pda1', N'', NULL, N'', N'A000000079') , (136, N'A000000136', CAST(N'2017-04-21 21:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 130, N'pda1', N'', NULL, N'', N'A000000079') , (137, N'A000000137', CAST(N'2017-04-21 21:20:29.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 78, N'pda1', N'', NULL, N'', N'A000000079') , (138, N'A000000138', CAST(N'2017-04-21 21:25:45.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 85, N'pda1', N'', NULL, N'', N'A000000079') , (139, N'A000000139', CAST(N'2017-04-21 21:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 64, N'pda1', N'', NULL, N'', N'A000000079') , (140, N'A000000140', CAST(N'2017-04-21 21:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 76, N'pda1', N'', NULL, N'', N'A000000079') , (141, N'A000000141', CAST(N'2017-04-21 21:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 86, N'pda1', N'', NULL, N'', N'A000000079') , (142, N'A000000142', CAST(N'2017-04-21 21:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 76, N'pda1', N'', NULL, N'', N'A000000079') , (143, N'A000000143', CAST(N'2017-04-21 21:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 135, N'pda1', N'', NULL, N'', N'A000000079') , (144, N'A000000144', CAST(N'2017-04-21 22:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 66, N'pda1', N'', NULL, N'', N'A000000079') , (145, N'A000000145', CAST(N'2017-04-21 22:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 50, N'pda1', N'', NULL, N'', N'A000000079') , (146, N'A000000146', CAST(N'2017-04-21 22:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 126, N'pda1', N'', NULL, N'', N'A000000079') , (147, N'A000000147', CAST(N'2017-04-21 22:20:29.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 88, N'suppda1', N'', NULL, N'', N'A000000079') , (148, N'A000000148', CAST(N'2017-04-21 22:25:45.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 68, N'pda1', N'', NULL, N'', N'A000000079') , (149, N'A000000149', CAST(N'2017-04-21 22:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 79, N'pda1', N'', NULL, N'', N'A000000079') , (150, N'A000000150', CAST(N'2017-04-21 22:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 89, N'pda1', N'', NULL, N'', N'A000000079') , (151, N'A000000151', CAST(N'2017-04-21 22:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 85, N'pda1', N'', NULL, N'', N'A000000079') , (152, N'A000000152', CAST(N'2017-04-21 22:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 77, N'pda1', N'', NULL, N'', N'A000000079') , (153, N'A000000153', CAST(N'2017-04-21 22:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 90, N'pda1', N'', NULL, N'', N'A000000079') , (154, N'A000000154', CAST(N'2017-04-21 23:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 87, N'pda1', N'', NULL, N'', N'A000000079') , (155, N'A000000155', CAST(N'2017-04-21 23:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 74, N'pda1', N'', NULL, N'', N'A000000079') , (156, N'A000000156', CAST(N'2017-04-21 23:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 121, N'pda1', N'', NULL, N'', N'A000000079') , (157, N'A000000157', CAST(N'2017-04-21 23:20:36.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 111, N'pda1', N'', NULL, N'', N'A000000079')
GO SET IDENTITY_INSERT #MST_REC_TEST OFF
IF OBJECT_ID('tempdb..#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
--Session table to store start & end datetime 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'A000000079', 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
I would like to determine:
1. Hourly sum of REC_TOTAL which refer to start & end time in session table, so that the result as below: HourStart HourEnd Product Total 2017-04-21 07:10 2017-04-21 08:10 JK CURRY 960
where Hour format is yyyy-mm-dd hh:mm. From session table, the job start at 2017-04-21 07:10, then hour sum will be from 07:10 to 08:10 for 07:00 hour, 08:10 to 09:10 for 08:00 hour and so on and ended at 15:10.
Second session start from 15:20, then hour sum will be from 15:20 to 16:20 for 15:00 hour, 16:20 to 17:20 for 16:00 hour and so on and ended at 23:20
2. The packing machine efficiency per hour. For this sample table, I assume the specification of packing machine can pack 1000 product per hour. Thus, the result will be:
HourStart HourEnd Product Total Efficiency (%)
2017-04-21 07:10 2017-04-21 08:10 JK CURRY 960 96.00
Is it possible (1) & (2) can be done in single query?
I'm puzzled & appreciate someone can help on this.
Edited note (24/07/17): After try on @chris-2 query, I realized the result should be with "HourStart" and "HourEnd" instead of "Hour". Silly me.
Edited note (25/07/17): Corrected some errors & duplicate key on sample data.
Edited note (06/09/17): As @Avi1 & @chris-2 pointed out, there were data error after July correction. I am very sorry for my mistake. OP updated.
Edited note (07/09/17): script updated due to error data.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Making a start on your question. Run the query, and confirm whether or not the result set fits the "hour buckets" you describe in your spec:

SELECT *

FROM dbo.MST_SESSION_TEST p

CROSS APPLY (

SELECT TOP(DATEDIFF(HOUR,SES_START,SES_END))

HourStart = DATEADD(HOUR,n,m.SessionStart),

HourEnd = DATEADD(HOUR,n+1,m.SessionStart)

FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) d (n)

CROSS APPLY (SELECT SessionStart = DATEADD(MINUTE,DATEDIFF(MINUTE,'20000101',SES_START),'20000101')) m

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

madcloud97 SSC Enthusiast Points: 171 More actions · Answer 2

Thanks @ChrisM@Work for the head start code. I will study and work from there.

madcloud97 SSC Enthusiast Points: 171 More actions · Answer 3

Hi @ChrisM@Work, tested your query, it fits the "hour buckets" I required. Result below:
SES_ID SES_NOTE SES_START SES_END HourStart HourEnd 1 A000000001 2017-04-21 07:10:20.000 2017-04-21 15:10:33.000 2017-04-21 07:10:00.000 2017-04-21 08:10:00.000 1 A000000001 2017-04-21 07:10:20.000 2017-04-21 15:10:33.000 2017-04-21 08:10:00.000 2017-04-21 09:10:00.000 1 A000000001 2017-04-21 07:10:20.000 2017-04-21 15:10:33.000 2017-04-21 09:10:00.000 2017-04-21 10:10:00.000 1 A000000001 2017-04-21 07:10:20.000 2017-04-21 15:10:33.000 2017-04-21 10:10:00.000 2017-04-21 11:10:00.000 1 A000000001 2017-04-21 07:10:20.000 2017-04-21 15:10:33.000 2017-04-21 11:10:00.000 2017-04-21 12:10:00.000 1 A000000001 2017-04-21 07:10:20.000 2017-04-21 15:10:33.000 2017-04-21 12:10:00.000 2017-04-21 13:10:00.000 1 A000000001 2017-04-21 07:10:20.000 2017-04-21 15:10:33.000 2017-04-21 13:10:00.000 2017-04-21 14:10:00.000 1 A000000001 2017-04-21 07:10:20.000 2017-04-21 15:10:33.000 2017-04-21 14:10:00.000 2017-04-21 15:10:00.000 2 A000000078 2017-04-21 15:20:06.000 2017-04-21 23:20:36.000 2017-04-21 15:20:00.000 2017-04-21 16:20:00.000 2 A000000078 2017-04-21 15:20:06.000 2017-04-21 23:20:36.000 2017-04-21 16:20:00.000 2017-04-21 17:20:00.000 2 A000000078 2017-04-21 15:20:06.000 2017-04-21 23:20:36.000 2017-04-21 17:20:00.000 2017-04-21 18:20:00.000 2 A000000078 2017-04-21 15:20:06.000 2017-04-21 23:20:36.000 2017-04-21 18:20:00.000 2017-04-21 19:20:00.000 2 A000000078 2017-04-21 15:20:06.000 2017-04-21 23:20:36.000 2017-04-21 19:20:00.000 2017-04-21 20:20:00.000 2 A000000078 2017-04-21 15:20:06.000 2017-04-21 23:20:36.000 2017-04-21 20:20:00.000 2017-04-21 21:20:00.000 2 A000000078 2017-04-21 15:20:06.000 2017-04-21 23:20:36.000 2017-04-21 21:20:00.000 2017-04-21 22:20:00.000 2 A000000078 2017-04-21 15:20:06.000 2017-04-21 23:20:36.000 2017-04-21 22:20:00.000 2017-04-21 23:20:00.000

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Ok, next step is to join your data table to this hourly breakdown table. Try first with a full outer join as shown, this helps pinpoint any issues with aligning your data table with the buckets:
;WITH Headers AS ( SELECT * FROM MST_SESSION_TEST p CROSS APPLY ( SELECT TOP(1+DATEDIFF(HOUR,SES_START,SES_END)) HourStart = DATEADD(HOUR,n,m.SessionStart), HourEnd = DATEADD(HOUR,n+1,m.SessionStart) FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d (n) CROSS APPLY (SELECT SessionStart = DATEADD(MINUTE,DATEDIFF(MINUTE,'20000101',SES_START),'20000101')) m ) x ) SELECT h.*, c.REC_ID, c.SES_NOTE, c.REC_DATETIME, c.REC_PRODUCT, c.REC_TOTAL FROM Headers h FULL OUTER JOIN MST_REC_TEST c ON c.SES_NOTE = h.SES_NOTE AND c.REC_DATETIME >= HourStart AND c.REC_DATETIME < HourEnd

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

madcloud97 SSC Enthusiast Points: 171 More actions · Answer 5

ChrisM@Work - Monday, July 24, 2017 5:15 AM
Ok, next step is to join your data table to this hourly breakdown table. Try first with a full outer join as shown, this helps pinpoint any issues with aligning your data table with the buckets:
;WITH Headers AS ( SELECT * FROM MST_SESSION_TEST p CROSS APPLY ( SELECT TOP(1+DATEDIFF(HOUR,SES_START,SES_END)) HourStart = DATEADD(HOUR,n,m.SessionStart), HourEnd = DATEADD(HOUR,n+1,m.SessionStart) FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d (n) CROSS APPLY (SELECT SessionStart = DATEADD(MINUTE,DATEDIFF(MINUTE,'20000101',SES_START),'20000101')) m ) x ) SELECT h.*, c.REC_ID, c.SES_NOTE, c.REC_DATETIME, c.REC_PRODUCT, c.REC_TOTAL FROM Headers h FULL OUTER JOIN MST_REC_TEST c ON c.SES_NOTE = h.SES_NOTE AND c.REC_DATETIME >= HourStart AND c.REC_DATETIME < HourEnd

My test server down due to power failure. Will test it out once back online

madcloud97 SSC Enthusiast Points: 171 More actions · Answer 6

Sorry for very late response as I outstation for 2 months. Result as below. I also included excel file for easy view. From the result, row 79 (read from excel), there were some wrong data for HourStart (2017-04-21 15:10:00) & HourEnd (2017-04-21 16:10:00). Should be HourStart (2017-04-21 14:10:00) & HourEnd (2017-04-21 15:10:00)
Also, there were some null values appeared at row 87 (read from excel)
SES_ID SES_NOTE SES_START SES_END HourStart HourEnd REC_ID SES_NOTE REC_DATETIME REC_PRODUCT REC_TOTAL 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 1 A000000001 2017-04-21 07:10:20 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 2 A000000001 2017-04-21 07:15:11 JK CURRY 97 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 3 A000000001 2017-04-21 07:20:33 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 4 A000000001 2017-04-21 07:30:06 JK CURRY 111 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 5 A000000001 2017-04-21 07:40:23 JK CURRY 120 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 6 A000000001 2017-04-21 07:45:44 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 7 A000000001 2017-04-21 07:50:18 JK CURRY 92 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 8 A000000001 2017-04-21 08:00:15 JK CURRY 87 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 9 A000000001 2017-04-21 08:05:01 MK CURRY 95 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 10 A000000001 2017-04-21 08:10:22 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 11 A000000001 2017-04-21 08:20:38 JK CURRY 126 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 12 A000000001 2017-04-21 08:30:50 MK CURRY 118 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 13 A000000001 2017-04-21 08:40:27 JK CURRY 130 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 14 A000000001 2017-04-21 08:45:48 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 15 A000000001 2017-04-21 08:50:36 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 16 A000000001 2017-04-21 09:00:58 JK CURRY 98 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 17 A000000001 2017-04-21 09:05:54 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 18 A000000001 2017-04-21 09:10:12 JK CURRY 95 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 19 A000000001 2017-04-21 09:20:29 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 20 A000000001 2017-04-21 09:25:25 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 21 A000000001 2017-04-21 09:30:29 JK CURRY 100 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 22 A000000001 2017-04-21 09:40:45 JK CURRY 130 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 23 A000000001 2017-04-21 09:50:31 JK CURRY 120 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 24 A000000001 2017-04-21 10:00:02 JK CURRY 110 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 25 A000000001 2017-04-21 10:05:05 JK CURRY 111 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 26 A000000001 2017-04-21 10:10:23 JK CURRY 77 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 27 A000000001 2017-04-21 10:15:56 JK CURRY 69 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 28 A000000001 2017-04-21 10:20:01 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 29 A000000001 2017-04-21 10:25:02 JK CURRY 85 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 30 A000000001 2017-04-21 10:30:07 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 31 A000000001 2017-04-21 10:35:09 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 32 A000000001 2017-04-21 10:40:13 JK CURRY 60 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 33 A000000001 2017-04-21 10:50:41 JK CURRY 120 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 34 A000000001 2017-04-21 10:55:48 JK CURRY 59 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 35 A000000001 2017-04-21 11:00:10 JK CURRY 78 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 36 A000000001 2017-04-21 11:05:23 JK CURRY 82 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 37 A000000001 2017-04-21 11:10:11 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 38 A000000001 2017-04-21 11:20:26 JK CURRY 119 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 39 A000000001 2017-04-21 11:25:33 JK CURRY 75 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 40 A000000001 2017-04-21 11:30:56 JK CURRY 76 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 41 A000000001 2017-04-21 11:35:43 JK CURRY 67 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 42 A000000001 2017-04-21 11:40:24 JK CURRY 84 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 43 A000000001 2017-04-21 11:50:12 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 44 A000000001 2017-04-21 11:55:28 JK CURRY 74 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 45 A000000001 2017-04-21 12:00:01 MK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 46 A000000001 2017-04-21 12:05:04 MK CURRY 77 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 47 A000000001 2017-04-21 12:10:17 MK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 48 A000000001 2017-04-21 12:20:41 MK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 49 A000000001 2017-04-21 12:25:11 MK CURRY 88 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 50 A000000001 2017-04-21 12:30:54 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 51 A000000001 2017-04-21 12:35:31 JK CURRY 79 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 52 A000000001 2017-04-21 12:40:21 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 53 A000000001 2017-04-21 12:50:14 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 54 A000000001 2017-04-21 12:55:09 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 55 A000000001 2017-04-21 13:00:02 JK CURRY 93 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 56 A000000001 2017-04-21 13:05:08 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 57 A000000001 2017-04-21 13:10:50 JK CURRY 70 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 58 A000000001 2017-04-21 13:20:44 JK CURRY 65 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 59 A000000001 2017-04-21 13:25:37 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 60 A000000001 2017-04-21 13:30:12 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 61 A000000001 2017-04-21 13:35:23 JK CURRY 70 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 62 A000000001 2017-04-21 13:40:41 JK CURRY 78 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 63 A000000001 2017-04-21 13:50:13 JK CURRY 120 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 64 A000000001 2017-04-21 13:55:13 JK CURRY 85 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 65 A000000001 2017-04-21 14:00:09 JK CURRY 77 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 66 A000000001 2017-04-21 14:05:00 JK CURRY 86 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 67 A000000001 2017-04-21 14:10:49 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 68 A000000001 2017-04-21 14:15:18 JK CURRY 91 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 69 A000000001 2017-04-21 14:20:13 MK CURRY 65 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 70 A000000001 2017-04-21 14:25:19 MK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 71 A000000001 2017-04-21 14:30:48 MK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 72 A000000001 2017-04-21 14:35:37 MK CURRY 71 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 73 A000000001 2017-04-21 14:40:44 JK CURRY 75 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 74 A000000001 2017-04-21 14:50:51 MK CURRY 77 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 75 A000000001 2017-04-21 14:55:27 MK CURRY 76 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 76 A000000001 2017-04-21 15:00:35 JK CURRY 69 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 77 A000000001 2017-04-21 15:05:10 MK CURRY 79 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 15:10:00 2017-04-21 16:10:00 78 A000000001 2017-04-21 15:10:33 JK CURRY 99 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 79 A000000078 2017-04-21 15:20:06 MK CURRY 80 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 80 A000000078 2017-04-21 15:30:23 MK CURRY 82 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 81 A000000078 2017-04-21 15:35:44 JK CURRY 70 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 82 A000000078 2017-04-21 15:40:18 MK CURRY 60 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 83 A000000078 2017-04-21 15:50:15 JK CURRY 99 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 84 A000000078 2017-04-21 15:55:01 JK CURRY 111 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 85 A000000078 2017-04-21 16:00:22 JK CURRY 74 NULL NULL NULL NULL NULL NULL 86 A000000078 2017-04-24 16:05:38 JK CURRY 81 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 87 A000000078 2017-04-21 16:10:50 JK CURRY 85 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 88 A000000078 2017-04-21 16:20:27 JK CURRY 84 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 89 A000000078 2017-04-21 16:30:48 JK CURRY 136 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 90 A000000078 2017-04-21 16:40:36 JK CURRY 114 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 91 A000000078 2017-04-21 16:45:58 JK CURRY 106 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 92 A000000078 2017-04-21 16:50:54 JK CURRY 83 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 93 A000000078 2017-04-21 16:55:12 JK CURRY 60 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 94 A000000078 2017-04-21 17:00:29 JK CURRY 89 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 95 A000000078 2017-04-21 17:05:25 JK CURRY 99 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 96 A000000078 2017-04-21 17:10:29 JK CURRY 99 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 97 A000000078 2017-04-21 17:20:45 JK CURRY 73 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 98 A000000078 2017-04-21 17:30:31 JK CURRY 120 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 99 A000000078 2017-04-21 17:35:02 JK CURRY 125 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 100 A000000078 2017-04-21 17:40:05 JK CURRY 75 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 101 A000000078 2017-04-21 17:45:23 JK CURRY 60 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 102 A000000078 2017-04-21 17:50:56 JK CURRY 76 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 103 A000000078 2017-04-21 17:55:01 JK CURRY 64 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 104 A000000078 2017-04-21 18:00:02 JK CURRY 79 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 105 A000000078 2017-04-21 18:05:07 JK CURRY 80 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 106 A000000078 2017-04-21 18:10:25 JK CURRY 75 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 107 A000000078 2017-04-21 18:20:29 JK CURRY 70 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 108 A000000078 2017-04-21 18:25:45 JK CURRY 89 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 109 A000000078 2017-04-21 18:30:31 JK CURRY 79 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 110 A000000078 2017-04-21 18:35:02 JK CURRY 89 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 111 A000000078 2017-04-21 18:40:05 JK CURRY 68 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 112 A000000078 2017-04-21 18:50:23 JK CURRY 70 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 113 A000000078 2017-04-21 18:55:56 JK CURRY 130 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 114 A000000078 2017-04-21 19:00:01 JK CURRY 77 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 115 A000000078 2017-04-21 19:05:07 JK CURRY 60 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 116 A000000078 2017-04-21 19:10:25 JK CURRY 111 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 117 A000000078 2017-04-21 19:20:29 JK CURRY 85 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 118 A000000078 2017-04-21 19:25:45 JK CURRY 84 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 119 A000000078 2017-04-21 19:30:31 JK CURRY 73 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 120 A000000078 2017-04-21 19:35:02 JK CURRY 80 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 121 A000000078 2017-04-21 19:40:05 JK CURRY 69 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 122 A000000078 2017-04-21 19:50:23 JK CURRY 70 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 123 A000000078 2017-04-21 19:55:56 JK CURRY 120 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 124 A000000078 2017-04-21 20:00:01 JK CURRY 77 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 125 A000000078 2017-04-21 20:05:07 JK CURRY 83 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 126 A000000078 2017-04-21 20:10:25 JK CURRY 117 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 127 A000000078 2017-04-21 20:20:29 JK CURRY 83 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 128 A000000078 2017-04-21 20:25:45 JK CURRY 79 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 129 A000000078 2017-04-21 20:30:31 JK CURRY 66 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 130 A000000078 2017-04-21 20:35:02 JK CURRY 65 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 131 A000000078 2017-04-21 20:40:05 JK CURRY 80 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 132 A000000078 2017-04-21 20:50:23 JK CURRY 74 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 133 A000000078 2017-04-21 20:55:56 JK CURRY 120 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 134 A000000078 2017-04-21 21:00:01 JK CURRY 87 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 135 A000000078 2017-04-21 21:05:07 JK CURRY 81 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 136 A000000078 2017-04-21 21:10:25 JK CURRY 130 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 137 A000000078 2017-04-21 21:20:29 JK CURRY 78 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 138 A000000078 2017-04-21 21:25:45 JK CURRY 85 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 139 A000000078 2017-04-21 21:30:31 JK CURRY 64 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 140 A000000078 2017-04-21 21:35:02 JK CURRY 76 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 141 A000000078 2017-04-21 21:40:05 JK CURRY 86 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 142 A000000078 2017-04-21 21:50:23 JK CURRY 76 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 143 A000000078 2017-04-21 21:55:56 JK CURRY 135 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 144 A000000078 2017-04-21 22:00:01 JK CURRY 66 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 145 A000000078 2017-04-21 22:05:07 JK CURRY 50 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 146 A000000078 2017-04-21 22:10:25 JK CURRY 126 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 147 A000000078 2017-04-21 22:20:29 JK CURRY 88 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 148 A000000078 2017-04-21 22:25:45 JK CURRY 68 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 149 A000000078 2017-04-21 22:30:31 JK CURRY 79 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 150 A000000078 2017-04-21 22:35:02 JK CURRY 89 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 151 A000000078 2017-04-21 22:40:05 JK CURRY 85 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 152 A000000078 2017-04-21 22:50:23 JK CURRY 77 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 153 A000000078 2017-04-21 22:55:56 JK CURRY 90 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 154 A000000078 2017-04-21 23:00:01 JK CURRY 87 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 155 A000000078 2017-04-21 23:05:07 JK CURRY 74 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 156 A000000078 2017-04-21 23:10:25 JK CURRY 121 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 23:20:00 2017-04-22 00:20:00 157 A000000078 2017-04-21 23:20:36 JK CURRY 111

Avi1 SSCrazy Points: 2323 More actions · Answer 7

It is a data issue, please analyze. the date for that record is "2017-04-24 16:05:38.000" not 04-21, there is no session defined for that day. query given by <a title="Go to ChrisM@Works profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl05_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisMWork" style="text-decoration: none; color: rgb(0, 51, 102); cursor: pointer; font-weight: 600; font-family: Arial, Helvetica, sans-serif; font-size: 12.8px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;">ChrisM@Work is correct. if you want to ignore these records then need to change the full outer to LEFT or INNER join based on your requirement.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Sorry, I haven't had time to get near this today. Here's the script I've been using for setting up the data sample I've used. If there's an issue with the data, please use this script as a starting point for a fix and post up the full script with amendments when you are done.
Thanks.

IF OBJECT_ID('tempdb..#MST_REC_TEST', 'U') IS NOT NULL DROP TABLE #MST_REC_TEST;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

--This is the main table recording how many product packed by machine every 5 or 10 minutes
CREATE TABLE #MST_REC_TEST(
    [REC_ID] [int] IDENTITY(1,1) NOT NULL,
    [REC_NOTE] [varchar](10) PRIMARY KEY CLUSTERED NOT NULL,
    [REC_DATETIME] [datetime] NULL,
    [REC_BUYERNO] [varchar](20) NULL,
    [REC_BUYER] [varchar](50) NULL,
    [REC_SKU] [varchar](20) NULL,
    [REC_PRODUCT] [varchar](50) NULL,
    [REC_SIZE] [varchar](20) NULL,
    [REC_TOTAL] [int] NULL,
    [USER_NAME] [varchar](15) NULL,
    [REC_VERIFY] [varchar](1) NULL,
    [REC_VERIFYTIME] [datetime] NULL,
    [REC_SUPERVISOR] [varchar](15) NULL,
    [SES_NOTE] [varchar](10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT #MST_REC_TEST ON
GO
INSERT #MST_REC_TEST ([REC_ID], [REC_NOTE], [REC_DATETIME], [REC_BUYERNO], [REC_BUYER], [REC_SKU], [REC_PRODUCT], [REC_SIZE], [REC_TOTAL], [USER_NAME], [REC_VERIFY], [REC_VERIFYTIME], [REC_SUPERVISOR], [SES_NOTE])
VALUES (1, N'A000000001', CAST(N'2017-04-21 07:10:20.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda1', N'', NULL, N'', N'A000000001')
, (2, N'A000000002', CAST(N'2017-04-21 07:15:11.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 97, N'pda1', N'', NULL, N'', N'A000000001')
, (3, N'A000000003', CAST(N'2017-04-21 07:20:33.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'pda1', N'', NULL, N'', N'A000000001')
, (4, N'A000000004', CAST(N'2017-04-21 07:30:06.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 111, N'pda1', N'', NULL, N'', N'A000000001')
, (5, N'A000000005', CAST(N'2017-04-21 07:40:23.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 120, N'pda1', N'', NULL, N'', N'A000000001')
, (6, N'A000000006', CAST(N'2017-04-21 07:45:44.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'pda1', N'', NULL, N'', N'A000000001')
, (7, N'A000000007', CAST(N'2017-04-21 07:50:18.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 92, N'pda1', N'', NULL, N'', N'A000000001')
, (8, N'A000000008', CAST(N'2017-04-21 08:00:15.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 87, N'pda1', N'', NULL, N'', N'A000000001')
, (9, N'A000000009', CAST(N'2017-04-21 08:05:01.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'MK CURRY', N'1x30', 95, N'pda1', N'', NULL, N'', N'A000000001')
, (10, N'A000000010', CAST(N'2017-04-21 08:10:22.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001')
, (11, N'A000000011', CAST(N'2017-04-24 08:20:38.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 126, N'pda1', N'', NULL, N'', N'A000000001')
, (12, N'A000000012', CAST(N'2017-04-21 08:30:50.000' AS DateTime), N'ABC1234568', N'1x30', N'ABC1234568', N'MK CURRY', N'1x30', 118, N'pda1', N'', NULL, N'', N'A000000001')
, (13, N'A000000013', CAST(N'2017-04-21 08:40:27.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 130, N'pda1', N'', NULL, N'', N'A000000001')
, (14, N'A000000014', CAST(N'2017-04-21 08:45:48.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'pda1', N'', NULL, N'', N'A000000001')
, (15, N'A000000015', CAST(N'2017-04-21 08:50:36.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001')
, (16, N'A000000016', CAST(N'2017-04-21 09:00:58.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 98, N'pda1', N'', NULL, N'', N'A000000001')
, (17, N'A000000017', CAST(N'2017-04-21 09:05:54.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'pda1', N'', NULL, N'', N'A000000001')
, (18, N'A000000018', CAST(N'2017-04-21 09:10:12.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 95, N'pda1', N'', NULL, N'', N'A000000001')
, (19, N'A000000019', CAST(N'2017-04-21 09:20:29.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001')
, (20, N'A000000020', CAST(N'2017-04-21 09:25:25.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001')
, (21, N'A000000021', CAST(N'2017-04-21 09:30:29.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 100, N'pda1', N'', NULL, N'', N'A000000001')
, (22, N'A000000022', CAST(N'2017-04-21 09:40:45.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 130, N'pda1', N'', NULL, N'', N'A000000001')
, (23, N'A000000023', CAST(N'2017-04-21 09:50:31.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 120, N'pda1', N'', NULL, N'', N'A000000001')
, (24, N'A000000024', CAST(N'2017-04-21 10:00:02.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 110, N'pda1', N'', NULL, N'', N'A000000001')
, (25, N'A000000025', CAST(N'2017-04-21 10:05:05.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 111, N'pda1', N'', NULL, N'', N'A000000001')
, (26, N'A000000026', CAST(N'2017-04-21 10:10:23.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 77, N'pda1', N'Y', CAST(N'2017-05-02 15:39:29.000' AS DateTime), N'suppda1', N'A000000001')
, (27, N'A000000027', CAST(N'2017-04-21 10:15:56.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 69, N'suppda1', N'', NULL, N'', N'A000000001')
, (28, N'A000000028', CAST(N'2017-04-21 10:20:01.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'suppda1', N'', NULL, N'', N'A000000001')
, (29, N'A000000029', CAST(N'2017-04-21 10:25:02.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 85, N'pda1', N'', NULL, N'', N'A000000001')
, (30, N'A000000030', CAST(N'2017-04-21 10:30:07.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'suppda1', N'', NULL, N'', N'A000000001')
, (31, N'A000000031', CAST(N'2017-04-21 10:35:09.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'pda1', N'', NULL, N'', N'A000000001')
, (32, N'A000000032', CAST(N'2017-04-21 10:40:13.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 60, N'pda1', N'', NULL, N'', N'A000000001')
, (33, N'A000000033', CAST(N'2017-04-21 10:50:41.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 120, N'pda1', N'', NULL, N'', N'A000000001')
, (34, N'A000000034', CAST(N'2017-04-21 10:55:48.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 59, N'pda1', N'', NULL, N'', N'A000000001')
, (35, N'A000000035', CAST(N'2017-04-21 11:00:10.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 78, N'pda1', N'', NULL, N'', N'A000000001')
, (36, N'A000000036', CAST(N'2017-04-21 11:05:23.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 82, N'pda1', N'', NULL, N'', N'A000000001')
, (37, N'A000000037', CAST(N'2017-04-21 11:10:11.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001')
, (38, N'A000000038', CAST(N'2017-04-21 11:20:26.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 119, N'pda1', N'', NULL, N'', N'A000000001')
, (39, N'A000000039', CAST(N'2017-04-21 11:25:33.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 75, N'pda1', N'', NULL, N'', N'A000000001')
, (40, N'A000000040', CAST(N'2017-04-21 11:30:56.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 76, N'pda1', N'Y', CAST(N'2017-05-17 16:40:48.000' AS DateTime), N'suppda1', N'A000000001')
, (41, N'A000000041', CAST(N'2017-04-21 11:35:43.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 67, N'pda1', N'', NULL, N'', N'A000000001')
, (42, N'A000000042', CAST(N'2017-04-21 11:40:24.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 84, N'pda1', N'', NULL, N'', N'A000000001')
, (43, N'A000000043', CAST(N'2017-04-21 11:50:12.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000001')
, (44, N'A000000044', CAST(N'2017-04-21 11:55:28.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 74, N'pda1', N'', NULL, N'', N'A000000001')
, (45, N'A000000045', CAST(N'2017-04-21 12:00:01.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 80, N'pda1', N'', NULL, N'', N'A000000001')
, (46, N'A000000046', CAST(N'2017-04-21 12:05:04.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 77, N'pda1', N'', NULL, N'', N'A000000001')
, (47, N'A000000047', CAST(N'2017-04-21 12:10:17.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 90, N'pda1', N'', NULL, N'', N'A000000001')
, (48, N'A000000048', CAST(N'2017-04-21 12:20:41.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 89, N'pda1', N'', NULL, N'', N'A000000001')
, (49, N'A000000049', CAST(N'2017-04-21 12:25:11.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 88, N'pda1', N'', NULL, N'', N'A000000001')
, (50, N'A000000050', CAST(N'2017-04-21 12:30:54.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda2', N'', NULL, N'', N'A000000001')
, (51, N'A000000051', CAST(N'2017-04-21 12:35:31.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 79, N'pda1', N'', NULL, N'', N'A000000001')
, (52, N'A000000052', CAST(N'2017-04-21 12:40:21.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda1', N'', NULL, N'', N'A000000001')
, (53, N'A000000053', CAST(N'2017-04-21 12:50:14.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'pda1', N'', NULL, N'', N'A000000001')
, (54, N'A000000054', CAST(N'2017-04-21 12:55:09.000' AS DateTime), N'MB145103632', N'1x100', N'MB145103632', N'ZEBRA PRINTER', N'1x100', 90, N'pda1', N'', NULL, N'', N'A000000001')
, (55, N'A000000055', CAST(N'2017-04-21 13:00:02.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 93, N'pda1', N'', NULL, N'', N'A000000001')
, (56, N'A000000056', CAST(N'2017-04-21 13:05:08.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda1', N'', NULL, N'', N'A000000001')
, (57, N'A000000057', CAST(N'2017-04-21 13:10:50.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 70, N'pda1', N'', NULL, N'', N'A000000001')
, (58, N'A000000058', CAST(N'2017-04-21 13:20:44.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 65, N'pda1', N'', NULL, N'', N'A000000001')
, (59, N'A000000059', CAST(N'2017-04-21 13:25:37.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 90, N'pda1', N'', NULL, N'', N'A000000001')
, (60, N'A000000060', CAST(N'2017-04-21 13:30:12.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 89, N'pda1', N'', NULL, N'', N'A000000001')
, (61, N'A000000061', CAST(N'2017-04-21 13:35:23.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 70, N'pda1', N'', NULL, N'', N'A000000001')
, (62, N'A000000062', CAST(N'2017-04-21 13:40:41.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 78, N'pda1', N'', NULL, N'', N'A000000001')
, (63, N'A000000063', CAST(N'2017-04-21 13:50:13.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 120, N'pda1', N'', NULL, N'', N'A000000001')
, (64, N'A000000064', CAST(N'2017-04-21 13:55:13.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 85, N'pda1', N'', NULL, N'', N'A000000001')
, (65, N'A000000065', CAST(N'2017-04-21 14:00:09.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 77, N'pda1', N'', NULL, N'', N'A000000001')
, (66, N'A000000066', CAST(N'2017-04-21 14:05:00.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 86, N'pda1', N'', NULL, N'', N'A000000001')
, (67, N'A000000067', CAST(N'2017-04-21 14:10:49.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 80, N'pda1', N'', NULL, N'', N'A000000001')
, (68, N'A000000068', CAST(N'2017-04-21 14:15:18.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 91, N'pda1', N'', NULL, N'', N'A000000001')
, (69, N'A000000069', CAST(N'2017-04-21 14:20:13.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 65, N'pda1', N'', NULL, N'', N'A000000001')
, (70, N'A000000070', CAST(N'2017-04-21 14:25:19.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 80, N'pda1', N'', NULL, N'', N'A000000001')
, (71, N'A000000071', CAST(N'2017-04-21 14:30:48.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 80, N'pda1', N'', NULL, N'', N'A000000001')
, (72, N'A000000072', CAST(N'2017-04-21 14:35:37.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 71, N'pda1', N'', NULL, N'', N'A000000001')
, (73, N'A000000073', CAST(N'2017-04-21 14:40:44.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 75, N'pda1', N'', NULL, N'', N'A000000001')
, (74, N'A000000074', CAST(N'2017-04-21 14:50:51.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 77, N'pda1', N'', NULL, N'', N'A000000001')
, (75, N'A000000075', CAST(N'2017-04-21 14:55:27.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 76, N'pda1', N'', NULL, N'', N'A000000001')
, (76, N'A000000076', CAST(N'2017-04-21 15:00:35.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 69, N'pda1', N'', NULL, N'', N'A000000001')
, (77, N'A000000077', CAST(N'2017-04-21 15:05:10.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 79, N'pda1', N'', NULL, N'', N'A000000001')
, (78, N'A000000078', CAST(N'2017-04-21 15:10:33.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000078')
, (79, N'A000000079', CAST(N'2017-04-21 15:20:06.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 80, N'pda1', N'', NULL, N'', N'A000000078')
, (80, N'A000000080', CAST(N'2017-04-21 15:30:23.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 82, N'pda1', N'', NULL, N'', N'A000000078')
, (81, N'A000000081', CAST(N'2017-04-21 15:35:44.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 70, N'pda2', N'', NULL, N'', N'A000000078')
, (82, N'A000000082', CAST(N'2017-04-21 15:40:18.000' AS DateTime), N'ABC1234568', N'1x45', N'ABC1234568', N'MK CURRY', N'1x45', 60, N'pda1', N'', NULL, N'', N'A000000078')
, (83, N'A000000083', CAST(N'2017-04-21 15:50:15.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 99, N'pda1', N'', NULL, N'', N'A000000078')
, (84, N'A000000084', CAST(N'2017-04-21 15:55:01.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 111, N'pda1', N'', NULL, N'', N'A000000078')
, (85, N'A000000085', CAST(N'2017-04-21 16:00:22.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 74, N'pda1', N'', NULL, N'', N'A000000078')
, (86, N'A000000086', CAST(N'2017-04-24 16:05:38.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 81, N'pda1', N'', NULL, N'', N'A000000078')
, (87, N'A000000087', CAST(N'2017-04-21 16:10:50.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 85, N'pda1', N'', NULL, N'', N'A000000078')
, (88, N'A000000088', CAST(N'2017-04-21 16:20:27.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 84, N'pda1', N'', NULL, N'', N'A000000078')
, (89, N'A000000089', CAST(N'2017-04-21 16:30:48.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 136, N'suppda1', N'', NULL, N'', N'A000000078')
, (90, N'A000000090', CAST(N'2017-04-21 16:40:36.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 114, N'pda1', N'', NULL, N'', N'A000000078')
, (91, N'A000000091', CAST(N'2017-04-21 16:45:58.000' AS DateTime), N'ABC1234567', N'1x30', N'ABC1234567', N'JK CURRY', N'1x30', 106, N'suppda1', N'', NULL, N'', N'A000000078')
, (92, N'A000000092', CAST(N'2017-04-21 16:50:54.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 83, N'pda1', N'', NULL, N'', N'A000000078')
, (93, N'A000000093', CAST(N'2017-04-21 16:55:12.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 60, N'pda1', N'', NULL, N'', N'A000000078')
, (94, N'A000000094', CAST(N'2017-04-21 17:00:29.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 89, N'pda1', N'Y', CAST(N'2017-05-23 09:55:42.000' AS DateTime), N'suppda1', N'A000000078')
, (95, N'A000000095', CAST(N'2017-04-21 17:05:25.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 99, N'suppda1', N'Y', CAST(N'2017-05-23 09:48:50.000' AS DateTime), N'suppda1', N'A000000078')
, (96, N'A000000096', CAST(N'2017-04-21 17:10:29.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 99, N'suppda1', N'Y', CAST(N'2017-05-23 09:37:56.000' AS DateTime), N'suppda1', N'A000000078')
, (97, N'A000000097', CAST(N'2017-04-21 17:20:45.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 73, N'pda1', N'Y', CAST(N'2017-05-23 16:21:38.000' AS DateTime), N'suppda1', N'A000000078')
, (98, N'A000000098', CAST(N'2017-04-21 17:30:31.000' AS DateTime), N'10304202A', N'10X40X25G', N'10304202A', N'MNS 30C BBQ 10X40X25G', N'10X40X25G', 120, N'suppda1', N'', NULL, N'', N'A000000078')
, (99, N'A000000099', CAST(N'2017-04-21 17:35:02.000' AS DateTime), N'18312101', N'10X12X25G', N'18312101', N'MNS FP (SINGAPORE) CHICKEN', N'10X12X25G', 125, N'suppda1', N'', NULL, N'', N'A000000078')
, (100, N'A000000100', CAST(N'2017-04-21 17:40:05.000' AS DateTime), N'18314402', N'10X40X25G', N'18314402', N'MNS (EX) BBQ', N'10X40X25G', 75, N'suppda1', N'', NULL, N'', N'A000000078')
, (101, N'A000000101', CAST(N'2017-04-21 17:45:23.000' AS DateTime), N'18312101', N'10X12X25G', N'18312101', N'MNS FP (SINGAPORE) CHICKEN', N'10X12X25G', 60, N'suppda1', N'', NULL, N'', N'A000000078')
, (102, N'A000000102', CAST(N'2017-04-21 17:50:56.000' AS DateTime), N'10304202T', N'10X40X25G', N'10304202T', N'MNS 30C BBQ TESTING', N'10X40X25G', 76, N'suppda1', N'Y', CAST(N'2017-06-22 11:25:17.000' AS DateTime), N'suppda1', N'A000000078')
, (103, N'A000000103', CAST(N'2017-04-21 17:55:01.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 64, N'suppda1', N'Y', CAST(N'2017-05-23 09:56:50.000' AS DateTime), N'suppda1', N'A000000078')
, (104, N'A000000104', CAST(N'2017-04-21 18:00:02.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 79, N'suppda1', N'Y', CAST(N'2017-05-23 10:39:11.000' AS DateTime), N'suppda1', N'A000000078')
, (105, N'A000000105', CAST(N'2017-04-21 18:05:07.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 80, N'suppda1', N'Y', CAST(N'2017-05-23 10:48:24.000' AS DateTime), N'suppda1', N'A000000078')
, (106, N'A000000106', CAST(N'2017-04-21 18:10:25.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 75, N'suppda1', N'Y', CAST(N'2017-05-23 16:21:35.000' AS DateTime), N'suppda1', N'A000000078')
, (107, N'A000000107', CAST(N'2017-04-21 18:20:29.000' AS DateTime), N'10304201', N'10X40X25G', N'10304201', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 70, N'suppda1', N'Y', CAST(N'2017-05-23 16:21:37.000' AS DateTime), N'suppda1', N'A000000078')
, (108, N'A000000108', CAST(N'2017-04-21 18:25:45.000' AS DateTime), N'10304202T', N'10X40X25G', N'10304202T', N'MNS 30C BBQ TESTING', N'10X40X25G', 89, N'suppda1', N'', NULL, N'', N'A000000078')
, (109, N'A000000109', CAST(N'2017-04-21 18:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 79, N'pda1', N'', NULL, N'', N'A000000078')
, (110, N'A000000110', CAST(N'2017-04-21 18:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 89, N'pda1', N'', NULL, N'', N'A000000078')
, (111, N'A000000111', CAST(N'2017-04-21 18:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 68, N'pda1', N'', NULL, N'', N'A000000078')
, (112, N'A000000112', CAST(N'2017-04-21 18:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 70, N'pda1', N'', NULL, N'', N'A000000078')
, (113, N'A000000113', CAST(N'2017-04-21 18:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 130, N'pda1', N'', NULL, N'', N'A000000078')
, (114, N'A000000114', CAST(N'2017-04-21 19:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 77, N'pda1', N'', NULL, N'', N'A000000078')
, (115, N'A000000115', CAST(N'2017-04-21 19:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 60, N'pda1', N'', NULL, N'', N'A000000078')
, (116, N'A000000116', CAST(N'2017-04-21 19:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 111, N'pda1', N'', NULL, N'', N'A000000078')
, (117, N'A000000117', CAST(N'2017-04-21 19:20:29.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 85, N'pda1', N'', NULL, N'', N'A000000078')
, (118, N'A000000118', CAST(N'2017-04-21 19:25:45.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 84, N'pda1', N'', NULL, N'', N'A000000078')
, (119, N'A000000119', CAST(N'2017-04-21 19:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 73, N'pda1', N'', NULL, N'', N'A000000078')
, (120, N'A000000120', CAST(N'2017-04-21 19:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 80, N'pda1', N'', NULL, N'', N'A000000078')
, (121, N'A000000121', CAST(N'2017-04-21 19:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 69, N'pda1', N'', NULL, N'', N'A000000078')
, (122, N'A000000122', CAST(N'2017-04-21 19:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 70, N'pda1', N'', NULL, N'', N'A000000078')
, (123, N'A000000123', CAST(N'2017-04-21 19:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 120, N'pda1', N'', NULL, N'', N'A000000078')
, (124, N'A000000124', CAST(N'2017-04-21 20:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 77, N'pda1', N'', NULL, N'', N'A000000078')
, (125, N'A000000125', CAST(N'2017-04-21 20:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 83, N'pda1', N'', NULL, N'', N'A000000078')
, (126, N'A000000126', CAST(N'2017-04-21 20:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 117, N'pda1', N'', NULL, N'', N'A000000078')
, (127, N'A000000127', CAST(N'2017-04-21 20:20:29.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 83, N'pda1', N'', NULL, N'', N'A000000078')
, (128, N'A000000128', CAST(N'2017-04-21 20:25:45.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 79, N'pda1', N'', NULL, N'', N'A000000078')
, (129, N'A000000129', CAST(N'2017-04-21 20:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 66, N'pda1', N'', NULL, N'', N'A000000078')
, (130, N'A000000130', CAST(N'2017-04-21 20:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 65, N'pda1', N'', NULL, N'', N'A000000078')
, (131, N'A000000131', CAST(N'2017-04-21 20:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 80, N'pda1', N'', NULL, N'', N'A000000078')
, (132, N'A000000132', CAST(N'2017-04-21 20:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 74, N'pda1', N'', NULL, N'', N'A000000078')
, (133, N'A000000133', CAST(N'2017-04-21 20:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 120, N'pda1', N'', NULL, N'', N'A000000078')
, (134, N'A000000134', CAST(N'2017-04-21 21:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 87, N'pda1', N'', NULL, N'', N'A000000078')
, (135, N'A000000135', CAST(N'2017-04-21 21:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 81, N'pda1', N'', NULL, N'', N'A000000078')
, (136, N'A000000136', CAST(N'2017-04-21 21:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 130, N'pda1', N'', NULL, N'', N'A000000078')
, (137, N'A000000137', CAST(N'2017-04-21 21:20:29.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 78, N'pda1', N'', NULL, N'', N'A000000078')
, (138, N'A000000138', CAST(N'2017-04-21 21:25:45.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 85, N'pda1', N'', NULL, N'', N'A000000078')
, (139, N'A000000139', CAST(N'2017-04-21 21:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 64, N'pda1', N'', NULL, N'', N'A000000078')
, (140, N'A000000140', CAST(N'2017-04-21 21:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 76, N'pda1', N'', NULL, N'', N'A000000078')
, (141, N'A000000141', CAST(N'2017-04-21 21:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 86, N'pda1', N'', NULL, N'', N'A000000078')
, (142, N'A000000142', CAST(N'2017-04-21 21:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 76, N'pda1', N'', NULL, N'', N'A000000078')
, (143, N'A000000143', CAST(N'2017-04-21 21:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 135, N'pda1', N'', NULL, N'', N'A000000078')
, (144, N'A000000144', CAST(N'2017-04-21 22:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 66, N'pda1', N'', NULL, N'', N'A000000078')
, (145, N'A000000145', CAST(N'2017-04-21 22:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 50, N'pda1', N'', NULL, N'', N'A000000078')
, (146, N'A000000146', CAST(N'2017-04-21 22:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 126, N'pda1', N'', NULL, N'', N'A000000078')
, (147, N'A000000147', CAST(N'2017-04-21 22:20:29.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 88, N'suppda1', N'', NULL, N'', N'A000000078')
, (148, N'A000000148', CAST(N'2017-04-21 22:25:45.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 68, N'pda1', N'', NULL, N'', N'A000000078')
, (149, N'A000000149', CAST(N'2017-04-21 22:30:31.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 79, N'pda1', N'', NULL, N'', N'A000000078')
, (150, N'A000000150', CAST(N'2017-04-21 22:35:02.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 89, N'pda1', N'', NULL, N'', N'A000000078')
, (151, N'A000000151', CAST(N'2017-04-21 22:40:05.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 85, N'pda1', N'', NULL, N'', N'A000000078')
, (152, N'A000000152', CAST(N'2017-04-21 22:50:23.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 77, N'pda1', N'', NULL, N'', N'A000000078')
, (153, N'A000000153', CAST(N'2017-04-21 22:55:56.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 90, N'pda1', N'', NULL, N'', N'A000000078')
, (154, N'A000000154', CAST(N'2017-04-21 23:00:01.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 87, N'pda1', N'', NULL, N'', N'A000000078')
, (155, N'A000000155', CAST(N'2017-04-21 23:05:07.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 74, N'pda1', N'', NULL, N'', N'A000000078')
, (156, N'A000000156', CAST(N'2017-04-21 23:10:25.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 121, N'pda1', N'', NULL, N'', N'A000000078')
, (157, N'A000000157', CAST(N'2017-04-21 23:20:36.000' AS DateTime), N'ABC1234567', N'10X40X25G', N'ABC1234567', N'MNS 30C CHIC 10X40X25G', N'10X40X25G', 111, N'pda1', N'', NULL, N'', N'A000000078')

GO
SET IDENTITY_INSERT #MST_REC_TEST OFF

IF OBJECT_ID('tempdb..#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--Session table to store start & end datetime
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

SELECT p.*, '#' '#', c.*
FROM #MST_SESSION_TEST p
INNER JOIN #MST_REC_TEST c ON c.SES_NOTE = p.SES_NOTE

;WITH Headers AS (
SELECT *
FROM #MST_SESSION_TEST p
CROSS APPLY (
  SELECT TOP(1+DATEDIFF(HOUR,SES_START,SES_END))
   HourStart = DATEADD(HOUR,n,m.SessionStart),
   HourEnd = DATEADD(HOUR,n+1,m.SessionStart)
  FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d (n)
  CROSS APPLY (SELECT SessionStart = DATEADD(MINUTE,DATEDIFF(MINUTE,'20000101',SES_START),'20000101')) m
) x
)
SELECT h.*, c.REC_ID, c.SES_NOTE, c.REC_DATETIME, c.REC_PRODUCT, c.REC_TOTAL
FROM Headers h
FULL OUTER JOIN #MST_REC_TEST c
ON c.SES_NOTE = h.SES_NOTE
AND c.REC_DATETIME >= HourStart
AND c.REC_DATETIME < HourEnd

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

madcloud97 SSC Enthusiast Points: 171 More actions · Answer 9

Avi1 - Tuesday, September 5, 2017 8:58 AM
It is a data issue, please analyze. the date for that record is "2017-04-24 16:05:38.000" not 04-21, there is no session defined for that day. query given by ChrisM@Works profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl05_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisMWork" style="text-decoration: none; color: rgb(0, 51, 102); cursor: pointer; font-weight: 600; font-family: Arial, Helvetica, sans-serif; font-size: 12.8px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;">ChrisM@Work is correct. if you want to ignore these records then need to change the full outer to LEFT or INNER join based on your requirement.

Yes, I noticed that after I posted the thread & I made the correction on July (refer to my edited note on 25/7). Sorry for the errors as I randomly created the sample data before upload to MS SQL.

Edited note (06/09/17): Thanks for pointed out the data error after I re-check OP again. I didn't realized got data error after the correction on July.

madcloud97 SSC Enthusiast Points: 171 More actions · Answer 10

Sorry for the data error, @Chris.
I will double check again the sample data I posted.

Edited note: OP updated. I'm very sorry for the data error.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

madcloud97 - Tuesday, September 5, 2017 6:18 PM
Sorry for the data error, @Chris.
I will double check again the sample data I posted.
Edited note: OP updated. I'm very sorry for the data error.

That's okay, we all make mistakes! What's important is that the error is fixed so folks can work with a predictable data set.
Holler when you're done, please make it very clear which set of data is the correct one to use, and someone will finish this off for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

madcloud97 SSC Enthusiast Points: 171 More actions · Answer 12

Test result as below. Attached also excel for easy view.
Correct me if I'm wrong, row 79 (from excel, also same thing happened for row 158), the HourStart & HourEnd captured as "2017-04-21 15:10:00" & "2017-04-21 16:10:00" respectively, is due to the REC_DATETIME captured "2017-04-21 15:10:33"; which timed at "15:10:33" already exceed within hour "15:10:00".
If this is the case, can we inclusive the time range for hour 15:10:00 is from "15:10:01" to "15:10:59" or maybe other best practice?
SES_ID SES_NOTE SES_START SES_END HourStart HourEnd REC_ID SES_NOTE REC_DATETIME REC_PRODUCT REC_TOTAL 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 1 A000000001 2017-04-21 07:10:20 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 2 A000000001 2017-04-21 07:15:11 JK CURRY 97 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 3 A000000001 2017-04-21 07:20:33 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 4 A000000001 2017-04-21 07:30:06 JK CURRY 111 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 5 A000000001 2017-04-21 07:40:23 JK CURRY 120 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 6 A000000001 2017-04-21 07:45:44 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 7 A000000001 2017-04-21 07:50:18 JK CURRY 92 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 8 A000000001 2017-04-21 08:00:15 JK CURRY 87 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 07:10:00 2017-04-21 08:10:00 9 A000000001 2017-04-21 08:05:01 MK CURRY 95 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 10 A000000001 2017-04-21 08:10:22 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 11 A000000001 2017-04-21 08:20:38 JK CURRY 126 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 12 A000000001 2017-04-21 08:30:50 MK CURRY 118 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 13 A000000001 2017-04-21 08:40:27 JK CURRY 130 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 14 A000000001 2017-04-21 08:45:48 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 15 A000000001 2017-04-21 08:50:36 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 16 A000000001 2017-04-21 09:00:58 JK CURRY 98 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 08:10:00 2017-04-21 09:10:00 17 A000000001 2017-04-21 09:05:54 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 18 A000000001 2017-04-21 09:10:12 JK CURRY 95 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 19 A000000001 2017-04-21 09:20:29 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 20 A000000001 2017-04-21 09:25:25 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 21 A000000001 2017-04-21 09:30:29 JK CURRY 100 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 22 A000000001 2017-04-21 09:40:45 JK CURRY 130 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 23 A000000001 2017-04-21 09:50:31 JK CURRY 120 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 24 A000000001 2017-04-21 10:00:02 JK CURRY 110 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 09:10:00 2017-04-21 10:10:00 25 A000000001 2017-04-21 10:05:05 JK CURRY 111 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 26 A000000001 2017-04-21 10:10:23 JK CURRY 77 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 27 A000000001 2017-04-21 10:15:56 JK CURRY 69 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 28 A000000001 2017-04-21 10:20:01 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 29 A000000001 2017-04-21 10:25:02 JK CURRY 85 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 30 A000000001 2017-04-21 10:30:07 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 31 A000000001 2017-04-21 10:35:09 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 32 A000000001 2017-04-21 10:40:13 JK CURRY 60 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 33 A000000001 2017-04-21 10:50:41 JK CURRY 120 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 34 A000000001 2017-04-21 10:55:48 JK CURRY 59 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 35 A000000001 2017-04-21 11:00:10 JK CURRY 78 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 10:10:00 2017-04-21 11:10:00 36 A000000001 2017-04-21 11:05:23 JK CURRY 82 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 37 A000000001 2017-04-21 11:10:11 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 38 A000000001 2017-04-21 11:20:26 JK CURRY 119 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 39 A000000001 2017-04-21 11:25:33 JK CURRY 75 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 40 A000000001 2017-04-21 11:30:56 JK CURRY 76 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 41 A000000001 2017-04-21 11:35:43 JK CURRY 67 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 42 A000000001 2017-04-21 11:40:24 JK CURRY 84 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 43 A000000001 2017-04-21 11:50:12 JK CURRY 99 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 44 A000000001 2017-04-21 11:55:28 JK CURRY 74 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 45 A000000001 2017-04-21 12:00:01 MK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 11:10:00 2017-04-21 12:10:00 46 A000000001 2017-04-21 12:05:04 MK CURRY 77 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 47 A000000001 2017-04-21 12:10:17 MK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 48 A000000001 2017-04-21 12:20:41 MK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 49 A000000001 2017-04-21 12:25:11 MK CURRY 88 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 50 A000000001 2017-04-21 12:30:54 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 51 A000000001 2017-04-21 12:35:31 JK CURRY 79 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 52 A000000001 2017-04-21 12:40:21 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 53 A000000001 2017-04-21 12:50:14 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 54 A000000001 2017-04-21 12:55:09 ZEBRA PRINTER 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 55 A000000001 2017-04-21 13:00:02 JK CURRY 93 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 12:10:00 2017-04-21 13:10:00 56 A000000001 2017-04-21 13:05:08 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 57 A000000001 2017-04-21 13:10:50 JK CURRY 70 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 58 A000000001 2017-04-21 13:20:44 JK CURRY 65 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 59 A000000001 2017-04-21 13:25:37 JK CURRY 90 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 60 A000000001 2017-04-21 13:30:12 JK CURRY 89 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 61 A000000001 2017-04-21 13:35:23 JK CURRY 70 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 62 A000000001 2017-04-21 13:40:41 JK CURRY 78 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 63 A000000001 2017-04-21 13:50:13 JK CURRY 120 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 64 A000000001 2017-04-21 13:55:13 JK CURRY 85 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 65 A000000001 2017-04-21 14:00:09 JK CURRY 77 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 13:10:00 2017-04-21 14:10:00 66 A000000001 2017-04-21 14:05:00 JK CURRY 86 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 67 A000000001 2017-04-21 14:10:49 JK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 68 A000000001 2017-04-21 14:15:18 JK CURRY 91 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 69 A000000001 2017-04-21 14:20:13 MK CURRY 65 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 70 A000000001 2017-04-21 14:25:19 MK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 71 A000000001 2017-04-21 14:30:48 MK CURRY 80 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 72 A000000001 2017-04-21 14:35:37 MK CURRY 71 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 73 A000000001 2017-04-21 14:40:44 JK CURRY 75 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 74 A000000001 2017-04-21 14:50:51 MK CURRY 77 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 75 A000000001 2017-04-21 14:55:27 MK CURRY 76 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 76 A000000001 2017-04-21 15:00:35 JK CURRY 69 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 14:10:00 2017-04-21 15:10:00 77 A000000001 2017-04-21 15:05:10 MK CURRY 79 1 A000000001 2017-04-21 07:10:20 2017-04-21 15:10:33 2017-04-21 15:10:00 2017-04-21 16:10:00 78 A000000001 2017-04-21 15:10:33 JK CURRY 99 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 79 A000000078 2017-04-21 15:20:06 MK CURRY 80 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 80 A000000078 2017-04-21 15:30:23 MK CURRY 82 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 81 A000000078 2017-04-21 15:35:44 JK CURRY 70 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 82 A000000078 2017-04-21 15:40:18 MK CURRY 60 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 83 A000000078 2017-04-21 15:50:15 JK CURRY 99 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 84 A000000078 2017-04-21 15:55:01 JK CURRY 111 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 85 A000000078 2017-04-21 16:00:22 JK CURRY 74 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 86 A000000078 2017-04-21 16:05:38 JK CURRY 81 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 15:20:00 2017-04-21 16:20:00 87 A000000078 2017-04-21 16:10:50 JK CURRY 85 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 88 A000000078 2017-04-21 16:20:27 JK CURRY 84 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 89 A000000078 2017-04-21 16:30:48 JK CURRY 136 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 90 A000000078 2017-04-21 16:40:36 JK CURRY 114 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 91 A000000078 2017-04-21 16:45:58 JK CURRY 106 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 92 A000000078 2017-04-21 16:50:54 MNS 30C CHIC 10X40X25G 83 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 93 A000000078 2017-04-21 16:55:12 MNS 30C CHIC 10X40X25G 60 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 94 A000000078 2017-04-21 17:00:29 MNS 30C CHIC 10X40X25G 89 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 95 A000000078 2017-04-21 17:05:25 MNS 30C CHIC 10X40X25G 99 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 16:20:00 2017-04-21 17:20:00 96 A000000078 2017-04-21 17:10:29 MNS 30C CHIC 10X40X25G 99 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 97 A000000078 2017-04-21 17:20:45 MNS 30C CHIC 10X40X25G 73 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 98 A000000078 2017-04-21 17:30:31 MNS 30C BBQ 10X40X25G 120 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 99 A000000078 2017-04-21 17:35:02 MNS FP (SINGAPORE) CHICKEN 125 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 100 A000000078 2017-04-21 17:40:05 MNS (EX) BBQ 75 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 101 A000000078 2017-04-21 17:45:23 MNS FP (SINGAPORE) CHICKEN 60 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 102 A000000078 2017-04-21 17:50:56 MNS 30C BBQ TESTING 76 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 103 A000000078 2017-04-21 17:55:01 MNS 30C CHIC 10X40X25G 64 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 104 A000000078 2017-04-21 18:00:02 MNS 30C CHIC 10X40X25G 79 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 105 A000000078 2017-04-21 18:05:07 MNS 30C CHIC 10X40X25G 80 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 17:20:00 2017-04-21 18:20:00 106 A000000078 2017-04-21 18:10:25 MNS 30C CHIC 10X40X25G 75 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 107 A000000078 2017-04-21 18:20:29 MNS 30C CHIC 10X40X25G 70 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 108 A000000078 2017-04-21 18:25:45 MNS 30C BBQ TESTING 89 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 109 A000000078 2017-04-21 18:30:31 MNS 30C CHIC 10X40X25G 79 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 110 A000000078 2017-04-21 18:35:02 MNS 30C CHIC 10X40X25G 89 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 111 A000000078 2017-04-21 18:40:05 MNS 30C CHIC 10X40X25G 68 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 112 A000000078 2017-04-21 18:50:23 MNS 30C CHIC 10X40X25G 70 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 113 A000000078 2017-04-21 18:55:56 MNS 30C CHIC 10X40X25G 130 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 114 A000000078 2017-04-21 19:00:01 MNS 30C CHIC 10X40X25G 77 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 115 A000000078 2017-04-21 19:05:07 MNS 30C CHIC 10X40X25G 60 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 18:20:00 2017-04-21 19:20:00 116 A000000078 2017-04-21 19:10:25 MNS 30C CHIC 10X40X25G 111 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 117 A000000078 2017-04-21 19:20:29 MNS 30C CHIC 10X40X25G 85 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 118 A000000078 2017-04-21 19:25:45 MNS 30C CHIC 10X40X25G 84 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 119 A000000078 2017-04-21 19:30:31 MNS 30C CHIC 10X40X25G 73 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 120 A000000078 2017-04-21 19:35:02 MNS 30C CHIC 10X40X25G 80 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 121 A000000078 2017-04-21 19:40:05 MNS 30C CHIC 10X40X25G 69 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 122 A000000078 2017-04-21 19:50:23 MNS 30C CHIC 10X40X25G 70 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 123 A000000078 2017-04-21 19:55:56 MNS 30C CHIC 10X40X25G 120 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 124 A000000078 2017-04-21 20:00:01 MNS 30C CHIC 10X40X25G 77 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 125 A000000078 2017-04-21 20:05:07 MNS 30C CHIC 10X40X25G 83 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 19:20:00 2017-04-21 20:20:00 126 A000000078 2017-04-21 20:10:25 MNS 30C CHIC 10X40X25G 117 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 127 A000000078 2017-04-21 20:20:29 MNS 30C CHIC 10X40X25G 83 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 128 A000000078 2017-04-21 20:25:45 MNS 30C CHIC 10X40X25G 79 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 129 A000000078 2017-04-21 20:30:31 MNS 30C CHIC 10X40X25G 66 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 130 A000000078 2017-04-21 20:35:02 MNS 30C CHIC 10X40X25G 65 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 131 A000000078 2017-04-21 20:40:05 MNS 30C CHIC 10X40X25G 80 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 132 A000000078 2017-04-21 20:50:23 MNS 30C CHIC 10X40X25G 74 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 133 A000000078 2017-04-21 20:55:56 MNS 30C CHIC 10X40X25G 120 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 134 A000000078 2017-04-21 21:00:01 MNS 30C CHIC 10X40X25G 87 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 135 A000000078 2017-04-21 21:05:07 MNS 30C CHIC 10X40X25G 81 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 20:20:00 2017-04-21 21:20:00 136 A000000078 2017-04-21 21:10:25 MNS 30C CHIC 10X40X25G 130 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 137 A000000078 2017-04-21 21:20:29 MNS 30C CHIC 10X40X25G 78 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 138 A000000078 2017-04-21 21:25:45 MNS 30C CHIC 10X40X25G 85 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 139 A000000078 2017-04-21 21:30:31 MNS 30C CHIC 10X40X25G 64 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 140 A000000078 2017-04-21 21:35:02 MNS 30C CHIC 10X40X25G 76 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 141 A000000078 2017-04-21 21:40:05 MNS 30C CHIC 10X40X25G 86 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 142 A000000078 2017-04-21 21:50:23 MNS 30C CHIC 10X40X25G 76 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 143 A000000078 2017-04-21 21:55:56 MNS 30C CHIC 10X40X25G 135 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 144 A000000078 2017-04-21 22:00:01 MNS 30C CHIC 10X40X25G 66 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 145 A000000078 2017-04-21 22:05:07 MNS 30C CHIC 10X40X25G 50 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 21:20:00 2017-04-21 22:20:00 146 A000000078 2017-04-21 22:10:25 MNS 30C CHIC 10X40X25G 126 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 147 A000000078 2017-04-21 22:20:29 MNS 30C CHIC 10X40X25G 88 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 148 A000000078 2017-04-21 22:25:45 MNS 30C CHIC 10X40X25G 68 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 149 A000000078 2017-04-21 22:30:31 MNS 30C CHIC 10X40X25G 79 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 150 A000000078 2017-04-21 22:35:02 MNS 30C CHIC 10X40X25G 89 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 151 A000000078 2017-04-21 22:40:05 MNS 30C CHIC 10X40X25G 85 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 152 A000000078 2017-04-21 22:50:23 MNS 30C CHIC 10X40X25G 77 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 153 A000000078 2017-04-21 22:55:56 MNS 30C CHIC 10X40X25G 90 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 154 A000000078 2017-04-21 23:00:01 MNS 30C CHIC 10X40X25G 87 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 155 A000000078 2017-04-21 23:05:07 MNS 30C CHIC 10X40X25G 74 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 22:20:00 2017-04-21 23:20:00 156 A000000078 2017-04-21 23:10:25 MNS 30C CHIC 10X40X25G 121 2 A000000078 2017-04-21 15:20:06 2017-04-21 23:20:36 2017-04-21 23:20:00 2017-04-22 00:20:00 157 A000000078 2017-04-21 23:20:36 MNS 30C CHIC 10X40X25G 111

Edited: correct wrong data

madcloud97 SSC Enthusiast Points: 171 More actions · Answer 13

ChrisM@Work - Wednesday, September 6, 2017 1:30 AM
madcloud97 - Tuesday, September 5, 2017 6:18 PM
Sorry for the data error, @Chris.
I will double check again the sample data I posted.
Edited note: OP updated. I'm very sorry for the data error.
That's okay, we all make mistakes! What's important is that the error is fixed so folks can work with a predictable data set.
Holler when you're done, please make it very clear which set of data is the correct one to use, and someone will finish this off for you.

Thanks for the guidelines and suggestions.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

madcloud97 - Wednesday, September 6, 2017 2:00 AM
ChrisM@Work - Wednesday, September 6, 2017 1:30 AM
madcloud97 - Tuesday, September 5, 2017 6:18 PM
Sorry for the data error, @Chris.
I will double check again the sample data I posted.
Edited note: OP updated. I'm very sorry for the data error.
That's okay, we all make mistakes! What's important is that the error is fixed so folks can work with a predictable data set.
Holler when you're done, please make it very clear which set of data is the correct one to use, and someone will finish this off for you.
Thanks for the guidelines and suggestions.

Using the sample data, I get unexpected results for three rows:

REC_ID = 11 Should REC_DATETIME be '2017-04-21 08:20:38.000'?

REC_ID = 78 Should SES_NOTE be 'A000000001'?

REC_ID = 86 Should REC_DATETIME be '2017-04-21 16:05:38.000'?

If you can answer these, we can probably wrap this up.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden