Trying to get total hours [Resolved]

Posted by D-Bar under VB.NET on 11/15/2013 | Points: 10 | Views : 1700 | Status : [Member] | Replies : 5
Hello all. Thanks for reading my post. I developed a "time clock" to keep track of hours worked. I have the hours worked for the day. I need to get the total hours for each employee. I am hitting a wall. I have two calendar controls to select a date range. I am able to display the info. from the database into a gridview which includes daily hours and the employee's name. How can I get the total hours for each employee for the selected date range? They don't have to go into the gridview. I could display names and total hours using labels placed below the gridview. Here is the code I am using to get the data into the gridview.

Protected Sub Calendar332_SelectionChanged(sender As Object, e As EventArgs)
'declare SQL statement that will query the database
Dim sqlstring As String = SELECT firstname, lastname, startdate, starttime, endtime, hours FROM hoursworked WHERE startdate = Calendar332.SelectedDate AND startdate = Calendar333.SelectedDate ORDER By startdate

Dim strSQLconnection As String = Source=SQL Server;SERVER=123abc; Initial Catalog=abc; UID=xxxxxxx;PWD=xxxxxxx;

Dim sqlConnection As New Data.SqlClient.SqlConnection(strSQLconnection)
Dim sqlCommand As New Data.SqlClient.SqlCommand(sqlstring, sqlConnection)
sqlConnection.Open()

Dim reader As Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader()



GridView7.Visible = True
GridView7.DataSource = reader
GridView7.DataBind()
reader.Close()
End Sub


Table structure:
id - auto increment
first name
last name
startdate
starttime
endtime
hours

Data is test data like:
John Doe 2013-11-15 2013-11-15 8:00:00 AM 2013-11-15 5:00:00 PM 9:00

Thank you for any input you might be able to provide.

D-Bar




Responses

Posted by: Bandi on: 11/15/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
SELECT firstname, lastname, sum( hours) hours FROM hoursworked WHERE startdate = Calendar332.SelectedDate AND startdate = Calendar333.SelectedDate group By firstname, lastname,

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

D-Bar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: D-Bar on: 11/21/2013 [Member] Starter | Points: 25

Up
0
Down
Thank you very much Bandi. I was adding the other values to the select statement that I needed and it messed things up and returned the same value I was getting. When I ran your query as is I noticed I was getting a different value. The value I wanted!

D-Bar

D-Bar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,
SELECT firstname, lastname, sum( hours) hours FROM hoursworked WHERE startdate = Calendar332.SelectedDate AND startdate = Calendar333.SelectedDate
group By firstname, lastname -- Here you should mention all non-aggragate columns present in the SELECT statement......


ex:
SELECT col1, firstname, lastname, sum( hours) hours FROM hoursworked WHERE startdate = Calendar332.SelectedDate AND startdate = Calendar333.SelectedDate
group By firstname, lastname, col1

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

D-Bar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If the results are incorrect while adding extra columns into GROUP BY clause...

Use the following query
SELECT T1.*, T2.hours 
FROM hoursworked T1
(SELECT firstname, lastname, sum( hours) hours
FROM hoursworked
WHERE startdate = Calendar332.SelectedDate
AND startdate = Calendar333.SelectedDate
group By firstname, lastname ) T2
ON T1.firstname = T2.firstname AND T1.lastname = T2.lastname
WHERE startdate = Calendar332.SelectedDate AND startdate = Calendar333.SelectedDate


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

D-Bar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Have you checked above solution?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

D-Bar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response