Posted by Hugo Kornelis on 10/26/05 00:12

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)

[Back to original message]