×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Find the first result of a given day
2

Find the first result of a given day

Find the first result of a given day

(OP)
Hi all,

First time poster needed some help with a query.

I have a query which displays the time each of my students logged in on a selected given date, the problem is that sometimes they will change computer and they will have two login times,

such as:

g smith 20/08/2010 11:00
g smith 20/08/2010 1130

I would like returned just the first login time for each user.

For this I thought a simple MIN function would solve the problem but the query still returns both results, here is my try:

SELECT [ADEReport].[dbo].[User].[JID]

,MIN(CONVERT(FLOAT, ChangeTime, 114)) as TimeLoggedIn


FROM [ADEReport].[dbo].[PresenceChange]JOIN [ADEReport].[dbo]. [User]
ON [ADEReport].[dbo].[PresenceChange].[UID] = [ADEReport].[dbo].[User].[UID]

AND CONVERT(CHAR(10),ChangeTime,120) = '2010-08-20'
AND PresenceStatus = 'online'
group by ChangeTime,JID
order by JID asc


I thought converting to a float might help but still no luck.

Any ideas where i'm going wrong?  

RE: Find the first result of a given day

(OP)
I don't have access to my code right now but would'nt your solution return me an aggregate function error if I don't include ChangeTime.

 

RE: Find the first result of a given day

The general group by is: each column reference in the select list must either identify a grouping column or be the argument of a set function.

In your case, the minimum ChangeTime (for a date) is what you're looking for, i.e. do not group by that column!

RE: Find the first result of a given day

(OP)
you're right, I  just hope that is not solution because I played around with this code so often I can't believe I did'nt try this before.

I'll let you know the result.

RE: Find the first result of a given day

(OP)
Dear me.... removing ChangeTime from group by solved all my problems.

Thank you very much guys, i'll try not to ask such a stupid question next time!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close