Display current hour Production

Rakesh Ponnala 21 Reputation points
2020-08-17T12:15:33.477+00:00

CREATE TABLE dbo.RAWTable(Time_Stamp datetime ,
Date date ,
Shift_Id varchar(5) ,
Line_Code varchar(10) ,
Machine_Code varchar(10) ,
Variant_Code varchar(20) ,
Machine_Status varchar(30) ,
OK_Parts int ,
NOK_Parts int ,
Rework_Parts int ,
Rejection_Reasons varchar(50) ,
Auto__Mode_Selected int ,
Manual_Mode_Slected int ,
Auto_Mode_Running int ,
CompanyCode nvarchar(100) ,
PlantCode nvarchar(100)
)

GO
SET ANSI_PADDING OFF
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F73593 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 368, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7481B AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 370, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F74F83 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 371, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F758FC AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 372, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F76735 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 373, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F77083 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 374, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F77C3E AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 375, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F787E7 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 376, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F793AA AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 377, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F79F85 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 378, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7B0FE AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 379, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7B7E4 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 380, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7C74E AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 381, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7D018 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 382, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7DA32 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 383, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7E60C AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 384, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7F1CD AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 385, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F7FD84 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 386, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F80950 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 387, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F81B7E AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 389, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F82106 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 389, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F830E0 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 391, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F83877 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 392, 27, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F84426 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 392, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F84FEC AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 393, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F85BC1 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 394, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F86768 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 395, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F87310 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 396, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8859B AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 398, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F88C7E AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 399, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8967B AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 400, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8A40F AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 401, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8ADD9 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 402, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8B9B0 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 403, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8C594 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 404, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8D2F5 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 405, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8DD23 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 406, 28, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8E8E8 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 406, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F8F4A7 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 407, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F90045 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 408, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F90C22 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 409, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F91867 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 410, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9236F AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 411, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F92F40 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 412, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F93B22 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 413, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F946BF AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 414, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F952B9 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 415, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F95F94 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 416, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F96A27 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 417, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F975E3 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 418, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F98199 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 419, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F98D43 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 420, 29, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F99916 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 420, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9A4CC AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 421, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9B084 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 422, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9BC41 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 423, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9CFE9 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 425, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9D6D4 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 426, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9E009 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 426, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9EBB1 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 427, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F9F77D AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'2', 428, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA033B AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 429, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA1570 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 431, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA1C33 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 432, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA287B AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 432, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA356A AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 433, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA3E1A AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 434, 30, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA49D2 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 434, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA6F29 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 437, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA7F4A AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 439, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA887C AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 440, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA9127 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 440, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FAA21E AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 442, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FAA973 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 443, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FAB441 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 444, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FAC017 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 445, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FACBD7 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 446, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FAD792 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 447, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FAE34B AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 448, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FAEEF8 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 448, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FB0201 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 450, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FB086A AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 451, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FB159A AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 452, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FB1E27 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 453, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FB29C0 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 454, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FB35BE AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 455, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FB4174 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 455, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FB4D36 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 456, 32, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00FA61A8 AS DateTime), CAST(0x75410B00 AS Date), N'S2', N'VFOE', N'M1', N'V2', N'1', 437, 31, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F729D7 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 367, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F5D52A AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 337, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F5E0E8 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 338, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F5EC9C AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 339, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F5F854 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 340, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F60AFC AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 342, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F61290 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 343, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F61BAB AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 344, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6277E AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 345, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6333F AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 346, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F63EDB AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 347, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F64A95 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 348, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6566E AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 349, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F66232 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 350, 24, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6744A AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 351, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F67B87 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 352, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6856F AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 353, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F69121 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 354, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F69CD7 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 355, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6A8B3 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 356, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6B466 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 357, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6C027 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 358, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6CBDC AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 359, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6DDD8 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 361, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6E56C AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 362, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6F323 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 363, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F6FAF7 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 364, 25, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F70695 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 364, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F71271 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 365, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO
INSERT INTO RAWTable VALUES (CAST(0x0000AC1A00F71E31 AS DateTime), CAST(0x75410B00 AS Date), N'S1', N'VFOE', N'M1', N'V2', N'1', 366, 26, 0, N'reason', 1, 0, 1, N'TEAL', N'Hosur_Unit_1')
GO

Query for OutPut:

DECLARE @From As DateTime='2020-08-17 15:00:00.000',
@To As DateTime='2020-08-17 15:30:00.000',
@Variant VARCHAR(50)='V1',
@shift_id VARCHAR(50)='S1',
@Shift AS Varchar(10)='S1',
@Line AS VARCHAR(10)='VFOE',
@麦麦 _code As Varchar(50)='M1',
@CompanyCode As Nvarchar(100)='TEAL',
@PlantCode NVarchar(100)='Hosur_UNIT_1'

;With MyCTE as (
select
Time_Stamp, Variant_code
, MyGroup = ROW_NUMBER() OVER (ORDER BY Time_Stamp) - ROW_NUMBER() OVER (PARTITION BY Variant_code,CompanyCode ORDER BY Time_Stamp)
from RAWTable(Nolock) where Time_Stamp between @From and @To
),
MyCTE2 as (
select Variant_code, StartTime = MIN(Time_Stamp), EndTime = MAX(Time_Stamp)
FROM MyCTE
GROUP BY MyGroup, Variant_code
--ORDER BY MIN(Timestamp)
)
SELECT
m.Variant_code, m.StartTime, m.EndTime, s.Machine_code
, s.Ok_Parts - ISNULL(Lead(s.Ok_Parts) OVER (PARTITION BY m.Variant_code ORDER BY m.StartTime desc), 0) as OK_parts
, s.NOk_Parts - ISNULL(Lead(s.NOk_Parts) OVER (PARTITION BY m.Variant_code ORDER BY m.StartTime desc), 0) as NOK_parts
, s.Rework_Parts - ISNULL(Lead(s.Rework_Parts) OVER (PARTITION BY m.Variant_code ORDER BY m.StartTime desc), 0) as Rework_Parts
FROM MyCTE2 m
LEFT JOIN RAWTable(Nolock) s ON m.EndTime = s.Time_Stamp where Time_Stamp between @From and @To
ORDER BY m.StartTime

Expected result:
Variant_code StartTime EndTime Machine_code OK_parts NOK_parts Rework_Parts
V2 2020-08-17 15:00:03.690 2020-08-17 15:29:50.400 M1 180 13 0

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,486 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,461 Reputation points
    2020-08-18T04:50:05.403+00:00

    I think this may be what you want. Note that the result it gives does not match the result you said you wanted from your sample data, but I think it does match your description of what you want.

    ;With cte As
    (Select r.Variant_Code, r.Machine_Code, 
      Min(r.Time_Stamp) As MinTime,
      Max(r.Time_Stamp) As MaxTime
    From RAWTable r
    Where r.Time_Stamp Between @From And @To
    Group By r.Variant_Code, r.Machine_Code)
    
    Select c.Variant_Code, MinRow.Time_Stamp As StartTime, MaxRow.Time_Stamp As EndTime, c.Machine_Code, 
       MaxRow.OK_Parts - MinRow.OK_Parts As OK_Parts,
       MaxRow.NOK_Parts - MinRow.NOK_Parts As NOK_Parts,
       MaxRow.Rework_Parts - MinRow.Rework_Parts As Rework_Parts
    From cte c
    Cross Apply (Select r1.Time_Stamp, r1.OK_Parts, r1.NOK_Parts, r1.Rework_Parts From RAWTable r1 
       Where c.Variant_Code = r1.Variant_Code And c.Machine_Code = r1.Machine_Code And c.MinTime = r1.Time_Stamp) MinRow
    Cross Apply (Select r2.Time_Stamp, r2.OK_Parts, r2.NOK_Parts, r2.Rework_Parts From RAWTable r2 
       Where c.Variant_Code = r2.Variant_Code And c.Machine_Code = r2.Machine_Code And c.MaxTime = r2.Time_Stamp) MaxRow;
    

    Tom

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2020-08-18T09:53:32.827+00:00

    Hi RakeshPonnala,
    There is no 2020-08-17 15:29:50.400 in your Time_Stamp column,Is your shared insert data complete? If incomplete, please share the complete data. Otherwise please check your expected output.

    Best Regards
    Echo

    0 comments No comments