see a set-based solution. Can some of you?
The table ScanLog records the time, operator, and machine at which a certain
operation was performed as entered by a barcode scanning application.
CREATE TABLE [dbo].[ScanLog] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Ord_Num] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WO_Suf] nvarchar(10) NULL,
[OpCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateAction] [datetime] NULL ,
[TimeAction] [datetime] NULL ,
[Action] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OpID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MachineCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PiecesCompleted] [int] NULL ,
[DTCreated] [datetime] not NULL default(getdate())
) ON [PRIMARY]
GO
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:57AM', 'FINISH',
'JSW', 'M-06', 39, 'Jun 16 2005 6:57AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:49AM', 'FINISH',
'JSW', 'M-06', 97, 'Jun 16 2005 6:49AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88770 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:00PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 3:00PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88770 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:40PM', 'FINISH',
'TAC', 'M-07', 72, 'Jun 6 2005 3:41PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:41PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 3:41PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:00PM', 'FINISH',
'TAC', 'M-07', 22, 'Jun 6 2005 4:00PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:00PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 4:00PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:10PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 6 2005 4:10PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106178',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:34PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 4:34PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106178',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:43PM', 'FINISH',
'TAC', 'M-07', 9, 'Jun 6 2005 4:43PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92194 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:48PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 4:48PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92194 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 5:17PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 6 2005 5:17PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('87308 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 5:36PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 5:36PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('87308 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:36PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 6 2005 6:37PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88375 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:41PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 6:41PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:56PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 6:56PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:18PM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 6 2005 7:18PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:31PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 7:31PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:47PM', 'FINISH',
'TAC', 'M-08', 39, 'Jun 6 2005 7:47PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88375 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 8:06PM', 'FINISH',
'TAC', 'M-07', 52, 'Jun 6 2005 8:06PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('93253 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:24PM', 'START',
'TAC', 'M-07', 0, 'Jun 6 2005 9:24PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:21AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 7:22AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88948 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:22AM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 7:22AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88948 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 8:28AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 8:28AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106181',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:15AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 9:15AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:50AM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 9:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'START',
'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105805',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:41PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 9:41PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105805',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 10:04PM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 6 2005 10:05PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 10:30PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 10:30PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('93253 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:01PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 6 2005 11:01PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'START',
'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105936',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:00AM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 12:00AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105936',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:09AM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 7 2005 12:09AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:12AM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 12:12AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:17AM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 7 2005 12:17AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:17AM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 12:17AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:30AM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 7 2005 12:30AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92768 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:13AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 6:13AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92768 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:55AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 7 2005 6:55AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('89314 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:55AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 6:55AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('89314 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 7:25AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 7 2005 7:25AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 7:25AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 7:25AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'FINISH',
'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106341',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'START',
'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96159 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 3:58PM', 'START',
'TAC', 'M-07', 11, 'Jun 7 2005 3:58PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96159 ',
'00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:04PM', 'FINISH',
'TAC', 'M-07', 11, 'Jun 7 2005 4:04PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:12PM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 4:12PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106344',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:42PM', 'START',
'TAC', 'M-07', 0, 'Jun 7 2005 4:42PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 9:50PM', 'FINISH',
'TAC', 'M-08', 135, 'Jun 7 2005 9:50PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 10:23PM', 'START',
'TAC', 'M-08', 0, 'Jun 7 2005 10:23PM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:21AM', 'FINISH',
'TAC', 'M-08', 0, 'Jun 8 2005 12:21AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106344',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:44AM', 'STOP',
'TAC', 'M-07', 168, 'Jun 8 2005 12:45AM' )
Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96317 ',
'00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:57PM', 'FINISH',
'TAC', 'M-07', 0, 'Jun 20 2005 5:05PM' )
GO
I then must create a report that tells for a given operator and day how much
time the operator worked and how many pieces he completed.
Operator Date Hours Pieces
TAC 6/6/05 8.5 400
TAC 6/7/05 8 350
JSW 6/6/05 8.5 350
JSW 6/7/05 8 400
It gets difficult when you have to deduct break times and realize that an
operator may be running more than one machine at a time. The ScanLog_Pairs
view helps display this:
Create View dbo.ScanLog_TimeResolved
AS
Select ID, Ord_Num, WO_Suf, OpCode,
DateAdd(ms, DatePart(ms, TimeAction),
DateAdd(ss, DatePart(ss, TimeAction),
DateAdd(n,datepart(n, TimeAction),
DateAdd(hh, datepart(hh,TimeAction), DateAction)))) as dtAction ,
Action, OpID, MachineCode, PiecesCompleted, DTCreated
From ScanLog
GO
Create View dbo.ScanLog_Starts
As
Select ID, Ord_Num, WO_Suf, OpCode, dtAction ,
Action, OpID, MachineCode, PiecesCompleted,
DTCreated
From ScanLog_TimeResolved
Where Action = 'START'
GO
Create View dbo.ScanLog_StopsFinishes
As
Select ID, Ord_Num, WO_Suf, OpCode, dtAction ,
Action, OpID, MachineCode, PiecesCompleted,
DTCreated
From ScanLog_TimeResolved
Where Action in ('STOP', 'FINISH')
go
Create View dbo.ScanLog_Pairs
As
Select IsNull(starts.Ord_Num, stops.Ord_Num) as Ord_Num ,
IsNull(starts.WO_Suf, stops.WO_Suf) as WO_Suf ,
IsNull(starts.OpCode, stops.OpCode) as OpCode ,
IsNull(starts.MachineCode, stops.MachineCode) as MachineCode ,
starts.dtAction as StartDT,
stops.dtAction as StopDT,
starts.Action as StartAction, --probably irrelevant
stops.Action as StopAction,
Starts.OpID as StartOperator,
stops.OpID as StopOperator,
SecondsElapsed = Case when starts.dtAction is NULL then Null
when stops.dtAction is NULL then NULL
else DateDiff(ss, starts.dtAction, stops.dtAction)
END,
stops.PiecesCompleted
from
ScanLog_Starts starts full join ScanLog_StopsFinishes stops on
starts.dtAction < stops.dtAction And starts.Ord_Num = stops.Ord_Num and
starts.WO_Suf = stops.WO_Suf and starts.OpCode = stops.OpCode
Left Join Scanlog_StopsFinishes early on
early.dtAction > starts.dtaction and early.dtAction < stops.dtAction And
early.Ord_Num = stops.Ord_Num and early.WO_Suf = stops.WO_Suf and
early.OpCode = stops.OpCode
Where early.ID is null
go
Select * From ScanLog_Pairs Order By StartOperator, StartDT
Operator TAC starts order 88375 on M-07 at 6:41 PM. Before it finishes at
8:06 PM, he has started and finished 2 orders on M-08. The number of pieces
he has completed should be summed, but the amount of time elapsed should
not.
So, is there a set-based solution to this time-gap / time-overlap problem,
or do I need to write a cursor?
Thanks in advance.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyWhy did you split date and time apart? Why is there no key and all the
columns are NULL-able to prevent having a relational key? Why are
there so many varying national character columns when most codes are
fixed length and in simple Latin characters, not Chinese? It is also
unusual that they are all multiples of five; you did research the
proper lengths for all your data elements instead of just pulling this
out of the air, didn't you?
Why do you have a creation date in a base table - auditing is not
done in the table itself. Why do you have a temporal model without
durations? Why did you use AM/PM instead of ISO-8601 for your temporal
data? Why are there no DRI or CHECK() clauses?
You have made a common Newbie design error. You
the datacollection forms with the data model. Your table is basically a
clipboard with log sheets and numbered lines (hence IDENTITY). If you
want a set-based DML, you need to have a proper, relational DDL. Here
is my guess, sans any specs:
CREATE TABLE ScanLog - weird name, standard in your industry?
(ord_nbr CHAR(25) NOT NULL
REFERENCES Orders(ord_nbr),
wo_suf CHAR(10) NOT NULL,
op_code CHAR(10) NOT NULL,
action_start_time DATETIME NOT NULL,
action_end_time DATETIME, -- null is currently active
CHECK(action_start_time < action_end_time),
action_code CHAR(10) NOT NULL,
op_id CHAR(10) NOT NULL
REFERENCES Operators (op_id),
machine_code CHAR(20) NOT NULL
REFERENCES Machines(machine_code),
pieces_completed INTEGER NOT NULL
CHECK (pieces_completed >= 0));
You can now write a simple query to find the MIN(action_start_time) and
MAX(action_end_time)to get the total elapsed time, something like
this:.
SELECT op_id, SUM(pieces_completed) AS pieces_total,
DATEDIFF(mm, MIN(action_start_time), MAX(action_end_time)) AS
elapsed_time
FROM ScanLog
WHERE op_id = 'TAC'
GROUP BY
I did oneof these applications for a company that makes a timeclock
device for the construction trades. Watch the data scrubbing when you
build the durations.|||> (ord_nbr CHAR(25) NOT NULL
> REFERENCES Orders(ord_nbr),
Joe, can you tell us:
(a) how is ord_nbr generated in your ideal world?
(b) what is wrong with using order_number?
A|||There is a set-based solution, but it is extremely difficult and convoluted
to write. In this case a cursor will be a lot simpler to write, will be a
lot more understandable--that is, easier to maintain, and will perform a lot
better, because you can do all of the work in a single pass through the
table, instead of using four or five self-joins.
You should probably consider creating a separate table to store the results
for reporting purposes. That way the calculation only has to happen once.
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:Onku8nOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> I am trying hard to avoid writing a cursor to drive a report, but I can't
> see a set-based solution. Can some of you?
> The table ScanLog records the time, operator, and machine at which a
certain
> operation was performed as entered by a barcode scanning application.
>
> CREATE TABLE [dbo].[ScanLog] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Ord_Num] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WO_Suf] nvarchar(10) NULL,
> [OpCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateAction] [datetime] NULL ,
> [TimeAction] [datetime] NULL ,
> [Action] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OpID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MachineCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PiecesCompleted] [int] NULL ,
> [DTCreated] [datetime] not NULL default(getdate())
> ) ON [PRIMARY]
> GO
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:57AM', 'FINISH',
> 'JSW', 'M-06', 39, 'Jun 16 2005 6:57AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:49AM', 'FINISH',
> 'JSW', 'M-06', 97, 'Jun 16 2005 6:49AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88770 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:00PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 3:00PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88770 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:40PM', 'FINISH',
> 'TAC', 'M-07', 72, 'Jun 6 2005 3:41PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 3:41PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 3:41PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:00PM', 'FINISH',
> 'TAC', 'M-07', 22, 'Jun 6 2005 4:00PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:00PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 4:00PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106261',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:10PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 6 2005 4:10PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106178',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:34PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 4:34PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106178',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:43PM', 'FINISH',
> 'TAC', 'M-07', 9, 'Jun 6 2005 4:43PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92194 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:48PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 4:48PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92194 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 5:17PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 6 2005 5:17PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('87308 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 5:36PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 5:36PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('87308 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:36PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 6 2005 6:37PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88375 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:41PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 6:41PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 6:56PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 6:56PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:18PM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 6 2005 7:18PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:31PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 7:31PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105942',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:47PM', 'FINISH',
> 'TAC', 'M-08', 39, 'Jun 6 2005 7:47PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88375 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 8:06PM', 'FINISH',
> 'TAC', 'M-07', 52, 'Jun 6 2005 8:06PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('93253 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:24PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 6 2005 9:24PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:01AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:01AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('91584 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:21AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:22AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88948 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 7:22AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 7:22AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('88948 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 8:28AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 8:28AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106181',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:15AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 9:15AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:50AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 9:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'START',
> 'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('94869 ',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 1:44PM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 6 2005 1:44PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105805',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 9:41PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 9:41PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105805',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 10:04PM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 6 2005 10:05PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 10:30PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 10:30PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('93253 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:01PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 6 2005 11:01PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
> 'A0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106117',
> 'B0000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 11:57PM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 6 2005 11:57PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105936',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:00AM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:00AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105936',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:09AM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:09AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:12AM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:12AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:17AM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:17AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:17AM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:17AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('105111',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:30AM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 7 2005 12:30AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92768 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:13AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 6:13AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('92768 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:55AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 7 2005 6:55AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('89314 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 6:55AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 6:55AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('89314 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 7:25AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 7 2005 7:25AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 7:25AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 7:25AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('99355 ',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'FINISH',
> 'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106341',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 8:50AM', 'START',
> 'JSW', 'M-06', 0, 'Jun 7 2005 8:50AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96159 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 3:58PM', 'START',
> 'TAC', 'M-07', 11, 'Jun 7 2005 3:58PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96159 ',
> '00000', 'PR090', 'Jun 6 2005 12:00AM', 'Dec 30 1899 4:04PM', 'FINISH',
> 'TAC', 'M-07', 11, 'Jun 7 2005 4:04PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:12PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 4:12PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106344',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:42PM', 'START',
> 'TAC', 'M-07', 0, 'Jun 7 2005 4:42PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
> 'A0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 9:50PM', 'FINISH',
> 'TAC', 'M-08', 135, 'Jun 7 2005 9:50PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 10:23PM', 'START',
> 'TAC', 'M-08', 0, 'Jun 7 2005 10:23PM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('104355',
> 'B0000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:21AM', 'FINISH',
> 'TAC', 'M-08', 0, 'Jun 8 2005 12:21AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('106344',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 12:44AM', 'STOP',
> 'TAC', 'M-07', 168, 'Jun 8 2005 12:45AM' )
> Insert Into ScanLog (Ord_Num, WO_Suf, OpCode, DateAction, TimeAction,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated) Values ('96317 ',
> '00000', 'PR090', 'Jun 7 2005 12:00AM', 'Dec 30 1899 4:57PM', 'FINISH',
> 'TAC', 'M-07', 0, 'Jun 20 2005 5:05PM' )
> GO
>
> I then must create a report that tells for a given operator and day how
much
> time the operator worked and how many pieces he completed.
> Operator Date Hours Pieces
> TAC 6/6/05 8.5 400
> TAC 6/7/05 8 350
> JSW 6/6/05 8.5 350
> JSW 6/7/05 8 400
> It gets difficult when you have to deduct break times and realize that an
> operator may be running more than one machine at a time. The ScanLog_Pairs
> view helps display this:
> Create View dbo.ScanLog_TimeResolved
> AS
> Select ID, Ord_Num, WO_Suf, OpCode,
> DateAdd(ms, DatePart(ms, TimeAction),
> DateAdd(ss, DatePart(ss, TimeAction),
> DateAdd(n,datepart(n, TimeAction),
> DateAdd(hh, datepart(hh,TimeAction), DateAction)))) as dtAction ,
> Action, OpID, MachineCode, PiecesCompleted, DTCreated
> From ScanLog
> GO
> Create View dbo.ScanLog_Starts
> As
> Select ID, Ord_Num, WO_Suf, OpCode, dtAction ,
> Action, OpID, MachineCode, PiecesCompleted,
> DTCreated
> From ScanLog_TimeResolved
> Where Action = 'START'
> GO
> Create View dbo.ScanLog_StopsFinishes
> As
> Select ID, Ord_Num, WO_Suf, OpCode, dtAction ,
> Action, OpID, MachineCode, PiecesCompleted,
> DTCreated
> From ScanLog_TimeResolved
> Where Action in ('STOP', 'FINISH')
> go
> Create View dbo.ScanLog_Pairs
> As
> Select IsNull(starts.Ord_Num, stops.Ord_Num) as Ord_Num ,
> IsNull(starts.WO_Suf, stops.WO_Suf) as WO_Suf ,
> IsNull(starts.OpCode, stops.OpCode) as OpCode ,
> IsNull(starts.MachineCode, stops.MachineCode) as MachineCode ,
> starts.dtAction as StartDT,
> stops.dtAction as StopDT,
> starts.Action as StartAction, --probably irrelevant
> stops.Action as StopAction,
> Starts.OpID as StartOperator,
> stops.OpID as StopOperator,
> SecondsElapsed = Case when starts.dtAction is NULL then Null
> when stops.dtAction is NULL then NULL
> else DateDiff(ss, starts.dtAction, stops.dtAction)
> END,
> stops.PiecesCompleted
> from
> ScanLog_Starts starts full join ScanLog_StopsFinishes stops on
> starts.dtAction < stops.dtAction And starts.Ord_Num = stops.Ord_Num and
> starts.WO_Suf = stops.WO_Suf and starts.OpCode = stops.OpCode
> Left Join Scanlog_StopsFinishes early on
> early.dtAction > starts.dtaction and early.dtAction < stops.dtAction And
> early.Ord_Num = stops.Ord_Num and early.WO_Suf = stops.WO_Suf and
> early.OpCode = stops.OpCode
> Where early.ID is null
> go
>
> Select * From ScanLog_Pairs Order By StartOperator, StartDT
> Operator TAC starts order 88375 on M-07 at 6:41 PM. Before it finishes at
> 8:06 PM, he has started and finished 2 orders on M-08. The number of
pieces
> he has completed should be summed, but the amount of time elapsed should
> not.
> So, is there a set-based solution to this time-gap / time-overlap problem,
> or do I need to write a cursor?
> Thanks in advance.
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>|||Again your "solution" doesn't answer the question posted. It appears that
the poster was assigned to write a report given raw data from a barcode
scanner. Your "solution" is the obvious table design for a reporting table,
but that doesn't give the poster any direction on how to load it.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123599943.935337.113690@.g43g2000cwa.googlegroups.com...
> Why did you split date and time apart? Why is there no key and all the
> columns are NULL-able to prevent having a relational key? Why are
> there so many varying national character columns when most codes are
> fixed length and in simple Latin characters, not Chinese? It is also
> unusual that they are all multiples of five; you did research the
> proper lengths for all your data elements instead of just pulling this
> out of the air, didn't you?
> Why do you have a creation date in a base table - auditing is not
> done in the table itself. Why do you have a temporal model without
> durations? Why did you use AM/PM instead of ISO-8601 for your temporal
> data? Why are there no DRI or CHECK() clauses?
> You have made a common Newbie design error. You
the data> collection forms with the data model. Your table is basically a
> clipboard with log sheets and numbered lines (hence IDENTITY). If you
> want a set-based DML, you need to have a proper, relational DDL. Here
> is my guess, sans any specs:
> CREATE TABLE ScanLog - weird name, standard in your industry?
> (ord_nbr CHAR(25) NOT NULL
> REFERENCES Orders(ord_nbr),
> wo_suf CHAR(10) NOT NULL,
> op_code CHAR(10) NOT NULL,
> action_start_time DATETIME NOT NULL,
> action_end_time DATETIME, -- null is currently active
> CHECK(action_start_time < action_end_time),
> action_code CHAR(10) NOT NULL,
> op_id CHAR(10) NOT NULL
> REFERENCES Operators (op_id),
> machine_code CHAR(20) NOT NULL
> REFERENCES Machines(machine_code),
> pieces_completed INTEGER NOT NULL
> CHECK (pieces_completed >= 0));
>
of time elapsed should not. So, is there a set-based solution to this
time-gap / time-overlap problem or do I need to write a cursor? <<
> You can now write a simple query to find the MIN(action_start_time) and
> MAX(action_end_time)to get the total elapsed time, something like
> this:.
> SELECT op_id, SUM(pieces_completed) AS pieces_total,
> DATEDIFF(mm, MIN(action_start_time), MAX(action_end_time)) AS
> elapsed_time
> FROM ScanLog
> WHERE op_id = 'TAC'
> GROUP BY
> I did oneof these applications for a company that makes a timeclock
> device for the construction trades. Watch the data scrubbing when you
> build the durations.
>|||Thanks for the reply. As is often the case in medium-large projects, a good
deal of table structure is inherited even if it is not defensible. I may,
however, be able to convert it into something very close to what you
recommend.
I will defend the use of NVARCHAR. Our application imports from our
customers' ERP systems. Since they run a wide range of ERP systems, the
order numbers they use vary significantly in width. Thus CHAR would not
work well for us since prepended spaces make a real mess of things.
Allowing unicode means we are not restricted to a Latin character set when
we want to sell elsewhere in the world.
Your solution handles simultaneous work on multiple machines, which is where
my table structure gives me major problems. How would I handle breaks with
your structure?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123599943.935337.113690@.g43g2000cwa.googlegroups.com...
> Why did you split date and time apart? Why is there no key and all the
> columns are NULL-able to prevent having a relational key? Why are
> there so many varying national character columns when most codes are
> fixed length and in simple Latin characters, not Chinese? It is also
> unusual that they are all multiples of five; you did research the
> proper lengths for all your data elements instead of just pulling this
> out of the air, didn't you?
> Why do you have a creation date in a base table - auditing is not
> done in the table itself. Why do you have a temporal model without
> durations? Why did you use AM/PM instead of ISO-8601 for your temporal
> data? Why are there no DRI or CHECK() clauses?
> You have made a common Newbie design error. You
the data> collection forms with the data model. Your table is basically a
> clipboard with log sheets and numbered lines (hence IDENTITY). If you
> want a set-based DML, you need to have a proper, relational DDL. Here
> is my guess, sans any specs:
> CREATE TABLE ScanLog - weird name, standard in your industry?
> (ord_nbr CHAR(25) NOT NULL
> REFERENCES Orders(ord_nbr),
> wo_suf CHAR(10) NOT NULL,
> op_code CHAR(10) NOT NULL,
> action_start_time DATETIME NOT NULL,
> action_end_time DATETIME, -- null is currently active
> CHECK(action_start_time < action_end_time),
> action_code CHAR(10) NOT NULL,
> op_id CHAR(10) NOT NULL
> REFERENCES Operators (op_id),
> machine_code CHAR(20) NOT NULL
> REFERENCES Machines(machine_code),
> pieces_completed INTEGER NOT NULL
> CHECK (pieces_completed >= 0));
>
of time elapsed should not. So, is there a set-based solution to this
time-gap / time-overlap problem or do I need to write a cursor? <<
> You can now write a simple query to find the MIN(action_start_time) and
> MAX(action_end_time)to get the total elapsed time, something like
> this:.
> SELECT op_id, SUM(pieces_completed) AS pieces_total,
> DATEDIFF(mm, MIN(action_start_time), MAX(action_end_time)) AS
> elapsed_time
> FROM ScanLog
> WHERE op_id = 'TAC'
> GROUP BY
> I did oneof these applications for a company that makes a timeclock
> device for the construction trades. Watch the data scrubbing when you
> build the durations.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123599943.935337.113690@.g43g2000cwa.googlegroups.com...
> Why did you split date and time apart? Why is there no key and all the
> columns are NULL-able to prevent having a relational key? Why are
> there so many varying national character columns when most codes are
> fixed length and in simple Latin characters, not Chinese? It is also
> unusual that they are all multiples of five; you did research the
> proper lengths for all your data elements instead of just pulling this
> out of the air, didn't you?
> Why do you have a creation date in a base table - auditing is not
> done in the table itself. Why do you have a temporal model without
> durations? Why did you use AM/PM instead of ISO-8601 for your temporal
> data? Why are there no DRI or CHECK() clauses?
While I (mostly) agree with the above, I'm having
difficulty with the statement:
> auditing is not done in the table itself.
Why would it not be? Seems to me the easiest,
most convenient, and best place to put it. I've
always done it this way. Do you have suggestions
for something better?|||there are several solutions, neither is easy, such as:
create table scanlog(order_number char(2), event_dt datetime,
event_name char(5))
insert into scanlog values('AA','11/11/2005 8:00AM', 'START')
insert into scanlog values('AB','11/11/2005 9:00AM', 'START')
insert into scanlog values('AA','11/11/2005 10:00AM', 'STOP')
insert into scanlog values('AA','11/11/2005 11:00AM', 'START')
insert into scanlog values('AB','11/11/2005 12:00PM', 'STOP')
insert into scanlog values('AA','11/11/2005 1:00PM', 'STOP')
--lunch 1PM-2PM
insert into scanlog values('AA','11/11/2005 2:00PM', 'START')
insert into scanlog values('AB','11/11/2005 2:00PM', 'START')
insert into scanlog values('AA','11/11/2005 3:00PM', 'STOP')
insert into scanlog values('AA','11/11/2005 4:00AM', 'START')
insert into scanlog values('AB','11/11/2005 5:00PM', 'STOP')
insert into scanlog values('AA','11/11/2005 5:00PM', 'STOP')
go
create view work_interval
as
select s.order_number, s.event_dt start_dt, e.event_dt end_dt
from scanlog s join scanlog e
on s.order_number=e.order_number and s.event_dt<e.event_dt
and s.event_name='START' and e.event_name='STOP'
where not exists(select 1 from scanlog s1
where s1.order_number=e.order_number
and s.event_dt<s1.event_dt and s1.event_dt < e.event_dt)
select * from work_interval
order_number start_dt
end_dt
-- ---
---
AA 2005-11-11 08:00:00.000
2005-11-11 10:00:00.000
AB 2005-11-11 09:00:00.000
2005-11-11 12:00:00.000
AA 2005-11-11 11:00:00.000
2005-11-11 13:00:00.000
AA 2005-11-11 14:00:00.000
2005-11-11 15:00:00.000
AB 2005-11-11 14:00:00.000
2005-11-11 17:00:00.000
AA 2005-11-11 16:00:00.000
2005-11-11 17:00:00.000
(6 row(s) affected)
select identity(int,1,1) as interval_number, start_dt
into #work_start
from (select distinct start_dt from work_interval w where not exists(
select 1 from work_interval w1
where w1.start_dt < w.start_dt and w.start_dt < w1.end_dt
)) t
select * from #work_start
interval_number start_dt
-- ---
1 2005-11-11 08:00:00.000
2 2005-11-11 14:00:00.000
(2 row(s) affected)
select identity(int,1,1) as interval_number, end_dt
into #work_end
from (select distinct end_dt from work_interval w where not exists(
select 1 from work_interval w1
where w1.start_dt < w.end_dt and w.end_dt < w1.end_dt
)) t
select * from #work_end
interval_number end_dt
-- ---
1 2005-11-11 13:00:00.000
2 2005-11-11 17:00:00.000
(2 row(s) affected)
select start_dt, end_dt
from #work_start join #work_end on #work_start.interval_number =
#work_end.interval_number
start_dt end_dt
---
---
2005-11-11 08:00:00.000 2005-11-11
13:00:00.000
2005-11-11 14:00:00.000 2005-11-11
17:00:00.000
(2 row(s) affected)
drop table scanlog
Easier to use row_number()|||>> (a) how is ord_nbr generated in your ideal world? <<
No, no! People who use IDENTITY live in a magical world where
identifiers fall from the hardware and they do not have to work because
they have a magical answer to all the problems.
In my world, I have to look for industry and legal standards (ex.
government contracts define the order numbers to be used), I have to
talk to the "Trolls in Accounting" (a la Dilbert) to find company
policies, etc. This is just the research! Then I have to set up
validation and verification rules. I have to set up audit trails and
account for every order number issues -- just like they were a valuable
company resource (kinda like checks)!
If I have to design an identifier, then i will consider various check
digit schemes (Dihedral five is the best) and additive congruential
generators if I need to guarantee 99.999% data accuracy. If I can live
with 99.99%, I might use weaker tools.
I just find long names to be harder to post in a newsgroup. I also
like "_nbr" as a postfix rather than spelling it out or using "_no"
(too much like "yes/no" in English) or "_num" (not as universal as
nbr)|||>> Seems to me the easiest, most convenient, and best place to put it. I've
always done it this way. Do you have suggestions for something better? <<
You have never had an accounting class, have you? This is the same
reason that two different clerks look at everything. This is the same
reason that you do not put the log files and back up on the same hard
drive as the database. You want to physically keep some things away
from the people that can be held liable for problems
Get a third party tool that is designed for auditing. They will not
degrade performance and they will give you nice reports. Get the right
tool for the job.sql
No comments:
Post a Comment