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?
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
group by ChangeTime,JID
with this:
group by JID
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Find the first result of a given day
RE: Find the first result of a given day
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
I'll let you know the result.
RE: Find the first result of a given day
Thank you very much guys, i'll try not to ask such a stupid question next time!