SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Simple query (I Hope)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

delfeye
Starting Member

USA
1 Posts

Posted - 02/14/2014 :  13:48:54  Show Profile  Reply with Quote
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.

Edited by - delfeye on 02/14/2014 13:50:18

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 02/18/2014 :  04:04:02  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/18/2014 :  13:36:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000