Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Simple query (I Hope)

Author  Topic 

delfeye
Starting Member

1 Post

Posted - 2014-02-14 : 13:48:54
I don't work in SQL typically (ever), but I need to modify a query to only return the first instance of EventTime for IdNumber.

SELECT [Id], [EventTime], [Location], [Event], [Details], [Dept], [IdNumber]
FROM [Audit]
WHERE EventTime >='[today]'
AND [Event] = 'Valid access';

The query was already built in to the physical Access Control application we support, but my customer wants to know the first time an employee badges in. I looked around on the site, but if I found the right solution, it was above my total lack of SQL skills. The application uses SQL 2005 Express.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2014-02-18 : 04:04:02
SELECT TOP 1 [Id], [EventTime], [Location], [Event], [Details], [Dept], [IdNumber]
FROM [Audit]
WHERE EventTime >='2014-02-18'
AND [Event] = 'Valid access'
ORDER BY EventTime
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-18 : 13:36:43
return the first instance of EventTime for IdNumber.
assuming you want it for a group of id this

SELECT [Id], [EventTime], [Location], [Event], [Details], [Dept], [IdNumber]
FROM
(
SELECT [Id], [EventTime], [Location], [Event], [Details], [Dept], [IdNumber],
ROW_NUMBER() OVER (PARTITION BY [IdNumber] ORDER BY [EventTime]) AS Seq
FROM [Audit]
WHERE EventTime >='[today]'
AND [Event] = 'Valid access'
)t
WHERE Seq=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -