试着获得总时数 [英] Trying to get total hours

问题描述

大家好。感谢您阅读我的帖子。我开发了一个时钟来跟踪工作时间。我有一天的工作时间。我需要得到每个员工的总小时数。我正在撞墙。我有两个日历控件来选择日期范围。我能够显示信息。从数据库到gridview，其中包括每日营业时间和员工姓名。如何获得所选日期范围内每位员工的总小时数？他们不必进入gridview。我可以使用gridview下面的标签显示名称和总小时数。这是我用来将数据导入gridview的代码。

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

感谢您提供的任何输入。



D-Bar

< br $>


表结构：

id - 自动增量

名字

姓氏

startdate

starttime

endtime

小时



数据是测试数据，如：

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


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

推荐答案

替换：

Replace:

Dim sqlstring As String = SELECT firstname, lastname, startdate, starttime, endtime, hours FROM hoursworked WHERE startdate =  Calendar332.SelectedDate  AND startdate = Calendar333.SelectedDate   ORDER By startdate

with：

with:

Dim sqlstring As String = "SELECT firstname, lastname, startdate, starttime, endtime, hours" & vbcr & _
                           "FROM hoursworked" & vbcr & _
                           "WHERE startdate BETWEEN  '" & Calendar332.SelectedDate & "' AND '" & Calendar333.SelectedDate "'" & vbcr & _
                           "ORDER By startdate"

有关详细信息，请参阅：字符串数据类型（ Visual Basic） [ ^ ]

For further information, please see: String Data Type (Visual Basic)[^]

SELECT firstname + ' ' +  lastname AS [Employee], SUM(DATEDIFF(day, endtime, starttime))
FROM hoursworked
WHERE startdate BETWEEN @firstdate AND @seconddate
GROUP BY firstname + ' ' +  lastname
ORDER BY firstname + ' ' +  lastname

[/ EDIT]

[/EDIT]

< br>

它会有所帮助



结帐示例1和2

it will help

checkout the example 1 and 2

这篇关于试着获得总时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！