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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

pivarCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.