|
|
Posted by teddysnips on 10/25/05 12:30
My application is to capture employee locations.
Whenever an employee arrives at a location (whether it is arriving for
work, or at one of the company's other sites) they scan the barcode on
their employee badge. This writes a record to the tblTSCollected table
(DDL and dummy data below).
The application needs to be able to display to staff in a control room
the CURRENT location of each employee.
>From the data I've provided, this would be:
EMPLOYEE ID LOCATION CODE
963 VB002
964 VB003
966 VB003
968 VB004
977 VB001
982 VB001
Note that, for example, Employee 963 had formerly been at VB001 but was
more recently logged in at VB002, so therefore the application is not
concerned with the earlier record.
What would also be particularly useful would be the NUMBER of staff at
each location - viz.
LOCATION CODE NUM STAFF
VB001 2
VB002 1
VB003 2
VB004 1
Can anyone help?
Many thanks in advance
Edward
NOTES ON DDL:
THE BARCODE IS CAPTURED BECAUSE THE COMPANY MAY RE-USE BARCODE NUMBERS
(WHICH IS DERIVED FROM THE EMPLOYEE PIN), SO THEREFORE THE BARCODE
CANNOT BE RELIED UPON TO BE UNIQUE.
THE COLUMN fldRuleAppliedID IS NULL BECAUSE THAT PARTICULAR ROW HAS NOT
BEEN PROCESSED. THERE ARE BUSINESS RULES CONCERNING EMPLOYEE HOURS
WHICH OPERATE ON THIS DATA. ONCE A ROW HAS BEEN PROCESSED FOR
UPLOADING TO THE PAYROLL APPLICATION, THE fldRuleAppliedID COLUMN WILL
CONTAIN A VALUE. IN THE PRODUCTION SYSTEM, THEREFORE, ANY SQL AS
REQUESTED ABOVE WILL CONTAIN IN ITS WHERE CLAUSE (fldRuleAppliedID Is
NULL)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSCollected]
GO
CREATE TABLE [dbo].[tblTSCollected] (
[fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldEmployeeID] [int] NULL ,
[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldTimeStamp] [datetime] NULL ,
[fldRuleAppliedID] [int] NULL ,
[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (
963, 'VB001', '2005-10-18 11:59:27.383', 45480)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (
963, 'VB002', '2005-10-18 12:06:17.833', 45480)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (
964, 'VB001', '2005-10-18 12:56:20.690', 45481)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (964, 'VB002', '2005-10-18 15:30:35.117', 45481)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (964, 'VB003', '2005-10-18 16:05:05.880', 45481)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (966, 'VB001', '2005-10-18 11:52:28.307', 97678)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (966, 'VB002', '2005-10-18 13:59:34.807', 97678)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (966, 'VB001', '2005-10-18 14:04:55.820', 97678)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (966, 'VB003', '2005-10-18 16:10:01.943', 97678)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (968, 'VB001', '2005-10-18 11:59:34.307', 98374)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (968, 'VB002', '2005-10-18 12:04:56.037', 98374)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (968, 'VB004', '2005-10-18 12:10:02.723', 98374)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (977, 'VB001', '2005-10-18 12:05:06.630', 96879)
INSERT INTO dbo.tblTSCollected
(fldEmployeeID,
fldLocationCode,
fldTimeStamp,
fldBarCode)
VALUES (982, 'VB001', '2005-10-18 12:06:13.787', 96697)
[Back to original message]
|