May 23, 2003 at 4:38 am
Hi
I have a problem, which I'm not sure can be done with SQL.
I have a table which consists of: keyholder name, date/time door accessed by keyholder. I have filtered that table by using a where clause specifying a start date and end date.
What I'm looking to do with that record set is to:
see if there exists within it a 360 minute period, for any keyholder, where the number of times a door has been accessed (count of date/time door accessed) exceeds a specified value?
Any help is most appreciated.
Cheers
Andy
May 23, 2003 at 5:11 am
very rough solution
Returns the Keyholder with the start record for the specified period
USE PUBS
DECLARE @key TABLE
(
Tidint identity,
KeyHolderchar(3),
Accesseddatetime
)
INSERT INTO @key (KeyHolder,Accessed)
VALUES ('AAA' , '2003-05-23 16:27:00.100')
INSERT INTO @key (KeyHolder,Accessed)
VALUES ('AAA' , '2003-05-23 16:23:00.100')
INSERT INTO @key (KeyHolder,Accessed)
VALUES ('AAA' , '2003-05-23 16:21:00.100')
INSERT INTO @key (KeyHolder,Accessed)
VALUES ('BBB' , '2003-05-23 16:30:00.100')
INSERT INTO @key (KeyHolder,Accessed)
VALUES ('CCC' , '2003-05-23 16:31:00.100')
INSERT INTO @key (KeyHolder,Accessed)
VALUES ('AAA' , '2003-05-23 16:50:00.100')
INSERT INTO @key (KeyHolder,Accessed)
VALUES ('AAA' , '2003-05-23 16:55:00.100')
--SELECT * FROM @key
SELECT *
FROM@key As T1
WHERE EXISTS
(
SELECT *
FROM @key As T2
WHERE (T1.KeyHolder = T2.KeyHolder) AND
(T2.Accessed BETWEEN T1.Accessed AND DATEADD(mi,10,T1.Accessed)) -- 10 minute interval
GROUP BY KeyHolder
HAVING COUNT(*) > 2 --Number of occurances
)
Hope this helps
May 23, 2003 at 6:53 am
try:
CREATE TABLE DoorAccess
(
DoorID INT
, EmployeeID INT
, AccessTime DATETIME
)
--This code will get the number of times
--doors accessed in last 6 hours
DECLARE @NumHoursToQuery INT
DECLARE @StartTime DATETIME
DECLARE @FilterCount INT
SET @StartTime = GETDATE()
SET @NumHoursToQuery = 6
SET @FilterCount = 10
SELECT DoorID , EmployeeID, COUNT(*) AS "# Times Accessed"
FROM DoorAccess
WHERE AccessTime BETWEEN @StartTime AND DATEADD(hh, -1 * @NumHoursToCompare, @StartTime)
GROUP BY DoorID, EmployeeID
HAVING COUNT(*) > @FilterCount
HTH,
Jay
Edited by - jpipes on 05/23/2003 06:53:45 AM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply