December 15, 2010 at 9:14 am
Hello--
A request has come down to me where a manager would like to know, at any given time, a list of the employees that are currently in the building. Each employee is issued an ID badge which he/she swipes at any number of readers for access to the building. Upon exiting the building, each employee must swipe the badge at any number of readers to leave the building.
There is a table which captures all access information: tblaccess
Each employee is uniquely identified in tblaccess with a field called: usernumber
Each reader is uniquely identified in tblaccess with a field called: readernumber
Each swipe is uniquely identified in tblaccess with a field called: accessid
The time of each swipe is captured by a field called: accessdatetime
Conveniently, tblaccess only holds the current day's access information. Any previous days are copied to a separate report server database.
As stated, each reader is uniquely identified by readernumber and I have a set of readernumbers that correspond to the entry readers [entry reader set] and a set of readernumbers that correspond to the exit readers [exit reader set]. These sets are necessary because accesses and readers of other buildings are included in tblaccess and I only need to report who is in the one building defined by the reader sets.
Keep in mind that employees can enter, exit, and reenter the building - so a query that only checks if an employee swiped today on an entry reader and then an exit reader is not sufficient.
My initial idea was to form a list of usernumbers who's first swipe of the day (using MIN(accessdatetime)) was on any of the entry readers. Then form another list of usernumbers who's last swipe of the day (using MAX(accessdatetime) was on any of the exit readers. Using the EXCEPT operator between the two data sets, I should get a list of usernumbers who are in the first data set (swiped into the building) and who are not in the second data set (swiped out of the building) - which tells me who entered the building today but has not left.
This idea didn't quite work out due to my inability to code the query so that a swipe was on an entry reader AND was the first swipe of the day. Likewise I had trouble coding so the query confirmed that the swipe was on an exit reader AND was the last swipe of the day.
I'm open to any and all ideas on how to solve this problem. I have to assume other organizations may want to know the employees in the building at any given time - whether it's for attendance or emergency preparedness purposes.
I'm relatively well-versed in SQL but not very experienced trying to use T-SQL, which may be necessary to solve this problem.
Unfortunately the system we use still requires SQL Server 2000, so that's the environment I'm working in. In addition, tblaccess is a heavily utilized table so my aim is to have this query have a minimal impact on it.
Hopefully someone can push me in the right direction!
Thanks!
December 15, 2010 at 10:01 am
Assuming your DDL is something like:
CREATE TABLE tblaccess
(
accessid int IDENTITY NOT NULL
PRIMARY KEY
,accessdatetime datetime
,usernumber int NOT NULL
,readernumber int NOT NULL
)
CREATE TABLE readernumbers
(
readernumber int NOT NULL
PRIMARY KEY
,readerSet varchar(5)
CHECK (readerSet IN ('entry', 'exit'))
)
try something like:
SELECT A.usernumber
FROM tblaccess A
JOIN readernumbers R
ON A.readernumber = R.readernumber
GROUP BY A.usernumber
HAVING MAX(CASE WHEN R.readerSet = 'entry' THEN A.accessid ELSE 0 END)
> MAX(CASE WHEN R.readerSet = 'exit' THEN A.accessid ELSE 0 END)
December 15, 2010 at 11:18 am
Unfortunately the records in the readers table aren't specified as "Entry" or "Exit" but I have a list of both types. For example, entry readers are WHERE readernumber IN (1,2,3) and exit readers are WHERE readernumber IN (4,5,6).
But your query looks helpful. If I understand correctly, if swipe on a entry reader, my usernumber is returned. If I don't swipe on an exit reader, a 0 is returned. Since my usernumber will be greater than 0, my usernumber is returned in the SELECT statement. However, if I did swipe on the exit reader, then my usernumber won't be greater than my usernumber so I won't be returned by the SELECT statement. What does the MAX do in this case?
December 16, 2010 at 5:12 am
Try:
SELECT usernumber
FROM tblaccess
WHERE readernumber IN (1,2,3,4,5,6)
GROUP BY A.usernumber
HAVING
-- Max entry accessid (assuming accessid is an IDENTITY. If not use accessdatetime.
MAX(CASE WHEN readernumber IN (1,2,3) THEN A.accessid ELSE 0 END)
>
-- Max exit accessid (assuming accessid is an IDENTITY. If not use accessdatetime.
MAX(CASE WHEN readernumber IN (4,5,6) THEN A.accessid ELSE 0 END)
The logic is that if the MAX(<Entry Access ID>) > MAX(<Exit Access ID>) then display the usernumber.
If accessid is not an IDENTITY then change the logic so that MAX(<Entry accessdatetime>) > MAX(<Exit accessdatetime>)
If you want quick and better answers to your questions you should post:
1. DDL
2. sample data in a consumable format. (ie INSERT statements for the DDL)
3. expected result from sample data.
4. what you have tried so far.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply