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].& #91;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 ,
& #91;fldLocationCode]
 [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_A
S
NULL ,
[fldTimeStamp] [datetime] NULL ,
& #91;fldRuleAppliedID
] [int] NULL ,
[fldBarCode] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NUL
L
) 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.
> 
>
>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_c
ode)
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 XXXX down?

There is a right way and a wrong way to correct people.  You use the
wrong (read, XXXXXXX) 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.