Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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
0
GeoffSutton
Asked:
GeoffSutton
2 Solutions
 
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
 
SvenCommented:
SELECT loginDate, username, MIN(loginTime), MAX(logoutTime) FROM tbl GROUP BY loginDate, username

Hours could be calculated within .NET code.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now