SQL join query

Geoff Sutton
Geoff Sutton used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008

Commented:
what values do the logintime and logouttime hold?  are they datetime fields?
select username,loginDate, min(loginTime) as LoginTime, max(Logouttime) as Logouttime from table group by username,Logindate

SvenTech Lead Web-Development

Commented:
SELECT loginDate, username, MIN(loginTime), MAX(logoutTime) FROM tbl GROUP BY loginDate, username

Hours could be calculated within .NET code.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Hi,

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


/pter
Geoff SuttonSr. Software Developer

Author

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
Geoff SuttonSr. Software Developer

Author

Commented:
Thanks Peter.  I'll try that.

Commented:
Depending on dateformat, you may have to use some sort of conversion. How does your format look like?
Geoff SuttonSr. Software Developer

Author

Commented:
@pter - The DateDiff doesn't work because the columns are nvarchar.  I tried casting them with no success.  Will try converting.
Geoff

Commented:
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?
Geoff SuttonSr. Software Developer

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial