SQL join query

Hi,

I have a database table with 4 columns, username, loginDate, loginTime, logoutTime.  This table get populated nightly from a text file.  I need to draw from this table the username, the date, the min(loginTime) and the max(logoutTime) for that username and date, and also return the total hours.  I know this can be done using an inner join of some sort but beyond that I really don't know how to start on it.  I could easily do it using multiple queries but since I am pulling it into an ASP.NET Datasource control it has to be condensed into a single query.

Any suggestions would be greatly appreciated.
Thanks,
Geoff
LVL 10
GeoffSuttonAsked:
Who is Participating?
 
Auric1983Connect With a Mentor Commented:
select username,loginDate, min(loginTime) as LoginTime, max(Logouttime) as Logouttime from table group by username,Logindate

0
 
chapmandewCommented:
what values do the logintime and logouttime hold?  are they datetime fields?
0
 
SvenTech Lead Web-DevelopmentCommented:
SELECT loginDate, username, MIN(loginTime), MAX(logoutTime) FROM tbl GROUP BY loginDate, username

Hours could be calculated within .NET code.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
pivarConnect With a Mentor Commented:
Hi,

You could calculate hours with DATEDIFF(hh, min(loginTime), max(Logouttime))


/pter
0
 
GeoffSuttonAuthor Commented:
LoginTime and Logout time are both stored as nvarchar.  
@DarthSonic - No, hours cannot be calculated in the .net Code.  I am using a datasource and don't want to be having to individually parsing each row to calculate the hours.  I am reasonably certain SQL server can do that for me.
Geoff
0
 
GeoffSuttonAuthor Commented:
Thanks Peter.  I'll try that.
0
 
pivarCommented:
Depending on dateformat, you may have to use some sort of conversion. How does your format look like?
0
 
GeoffSuttonAuthor Commented:
@pter - The DateDiff doesn't work because the columns are nvarchar.  I tried casting them with no success.  Will try converting.
Geoff
0
 
pivarCommented:
You were right the first time. My name is Peter. My fingers were faster than my brain. ;-)

What format do you have for the dates?
0
 
GeoffSuttonAuthor Commented:
It was nvarchar.  I solved it(with a lot of help from you guys) with:
SELECT     username, loginDate, MIN(loginTime) AS LoginTime, MAX(logoutTime) AS Logouttime, DATEDIFF(hh, MIN(CONVERT(DateTime, loginTime)),
                      MAX(CONVERT(DateTime, logoutTime))) AS hours
FROM         tblAttendanceScript
GROUP BY username, loginDate
I was sure I would have to use a join of some sort as i have had to use in the past.  I suppose I was just being lazy this morning, wanting to let someone else do my thinking for me :)  The only thing I have left is to ensure the data in the time columns is the correct type, and I can do that on the insert.
Thanks to all for the help.
Geoff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.