Deriving unique rows from historical data

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)On 25 Oct 2005 02:30:17 -0700, teddysnips@.hotmail.com wrote:

>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).

Hi Edward,

Thanks for posting the DDL and the data - makes writing and testing a
sloution so much easier!!

>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

This query works for the data given:

SELECT a.fldEmployeeID, a.fldLocationCode
FROM tblTSCollected AS a
WHERE NOT EXISTS
(SELECT *
FROM tblTSCollected AS b
WHERE b.fldEmployeeID = a.fldEmployeeID
AND b.fldTimeStamp > a.fldTimeStamp)

Since the data in the table is checked against the data in the table
itself, execution time might explode if the table has lots of rows. That
can be controlled with proper indexing. A non-clustered index on
(fldEmployeeID, fldTimeStamp) would do wonders for this query (but be
aware that it might hurt performance in other parts of your system!)

>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

Using the previous query as a starting point:

SELECT a.fldLocationCode, COUNT(*) AS Num_Staff
FROM tblTSCollected AS a
WHERE NOT EXISTS
(SELECT *
FROM tblTSCollected AS b
WHERE b.fldEmployeeID = a.fldEmployeeID
AND b.fldTimeStamp > a.fldTimeStamp)
GROUP BY a.fldLocationCode

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 25 Oct 2005 02:30:17 -0700, teddysnips@.hotmail.com wrote:
[...]
> Best, Hugo

Many thanks, Hugo. That does the trick perfectly!

Edward|||Your DDL is wrong in almost every way possible. IDENTITY is not a key,
barcodes are fixed length and none of them are CHAR(50) -- you never
did even the minimal research!! You use the magical, "I have no
brains!!" VARCHAR(50) all over the place, TIMESTAMP is a reserved word
in SQL, etc.

Where did you get the stupid idea that you need to put "fld-" and
"tbl-" prefixes on names? In violation of both common sense and
ISO-11179? One of the major principles of RDBMS is to avoid redundance;
Do you put "noun-" in your English?

When you design a history table, you need to learn that time comes in
durations; you need a (stsrt, end). You need to think of the schema as
a whole and not a bunch disjoint files. you need to avoid havign more
NULLs than the entie payroll of Genral Motors. More like this: .

CREATE TABLE EmpLocationHistory
(emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
location_code INTEGER NOT NULL
REFERENCES Locations(location_code)
ON UPDATE CASCADE,
start_time DATETIME NOT NULL,
end_time DATETIME, -- null means current
CHECK (start_time < end_time),
PRIMARY KEY (emp_id, location_code,start_time),
etc. );

Google how to code for this schema.

Among the errors in this posting, you do not know that SQL uses
ISO-8601 format for temporal data. You might want to look at the
research on camelCase and program readability; it sucks because the eye
jumps to the uppercase letter then flicks back to the start of the
word.

I was not kidding when I said that your code is wrong in almost every
way possible.|||Wow! Where can I send you some money so you can buy your medication
and calm the fuck down?

There is a right way and a wrong way to correct people. You use the
wrong (read, asshole) way. There is no call for lines like "I have no
brains!!" or "stupid idea".

Being civil is worth so much more than posting DDL or not calling a
column a field and a row a record.

Get a grip and be nicer.