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?
