如何将时钟输入/输出时间从SQL服务器排序到数据网格视图中 [英] How do I sort clock in/out times from an SQL server into a datagridview

问题描述

我在SQL中有一个表，其中包含从一个时钟输入点收集的时钟时间。时间表如下所示：

I have a table in SQL that contains clock times gathered from the one clock-in point. The times appear in the table like so:

pkClockingTime dtDateTime               fkEmployee
1              2017-02-06, 07:59:00.000 49
2              2017-02-06, 08:00:00.000 50
3              2017-02-06, 12:00:00.000 49
32             2017-02-06, 12:01:00.000 50
33             2017-02-06, 13:00:00.000 50
34             2017-02-06, 13:04:00.000 49
35             2017-02-06, 17:30:00.000 50
36             2017-02-06, 17:32:00.000 49

等等;这只是典型日两个人的时钟时间的示例，其中时钟输入为8，午餐时钟为12，时钟输入为1，然后在5:30返回。



如果有人可以提供帮助或提供任何令人惊叹的建议！



我尝试了什么：



目前，我通过从TableAdapter创建DataTable并将其附加到绑定到DataGridView的BindingSource，按照Employee和date的顺序显示时间，但它在DataGridView中每个员工有2行，每个时钟输入/输出一个，我希望在一行上全部4次。



我希望它可以修改为在1行的同一天从Employee输出所有4次



到目前为止我创建的DataTable代码输出2行员工如下：

and so on; this is just an example of two peoples clock times on a typical day with clock-in at 8, clock-out for lunch at 12, clock back in at 1, then going home at 5:30.

If anyone can help or offer any advice that would be amazing!

What I have tried:

At the moment I have it displaying the times in order of Employee and date by creating a DataTable from the TableAdapter and attaching that to a BindingSource which is bound to the DataGridView, but it has 2 lines per employee in the DataGridView, one for each Clock In/Out where as i would like all 4 times on one line.

I was hoping it can be modified to output all 4 times from the Employee on the same date on 1 line

The code i have so far for creating the DataTable which outputs 2 lines per Employee is as follows:

Private Function FormatedClockingTimes() As DataTable
    Dim dtResults As New DataTable
    dtResults.Columns.Add("EmployeeID", GetType(Integer))
    dtResults.Columns.Add("Date", GetType(Date))
    dtResults.Columns.Add("ClockIn", GetType(DateTime))
    dtResults.Columns.Add("ClockOut", GetType(DateTime))

    Dim intLastEmployee As Integer = 0
    Dim intClockCounter As Integer = 0
    Dim drLastRow As DataRow
    Dim dtCurrentDate As Date
    Dim dtPreviousDate As Date

    For Each drSourceRow As DataRow In TblAttendanceTimesTableAdapter.GetData() 'Get the raw clocking times and loop through each row
        'increment the counter by 1
        intClockCounter += 1
        'Store the date from the row as CurrentDate
        dtCurrentDate = drSourceRow("dtDateTime").ToString
        'If previous date has not been set yet (its the first loop), then set the PreviousDate as the current date
        If (dtPreviousDate = Nothing) Then
            dtPreviousDate = dtCurrentDate
        End If
        'Check that the employee from this row is equal to the employee from previous row, unless the employee from last row is 0 (its the firs row in the loop)
        'Also, check the date from this row is equal to the date from previous row, as the PreviousDate was set on the first lopp to match curerent date, the first row of the loop will always onsert a new row in to the results table
        If ((drSourceRow("fkEmployee").ToString() = intLastEmployee Or intLastEmployee = 0) And dtCurrentDate.ToShortDateString = dtPreviousDate.ToShortDateString) Then
            If (intClockCounter = 1) Then 'If the counter is still 1, the add a new record.
                'Insert new row into the results table based on the current row in the loop of the raw clocking data
                dtResults.Rows.Add(drSourceRow("fkEmployee").ToString(), dtCurrentDate, drSourceRow("dtDateTime").ToString(), Nothing) 'Nothing is added last as it is the clock out time, which wont be set as this part of the loop always sets the clock in data
            Else
                'Update last row (newly inserted row in the results table) as clock out time
                drLastRow = dtResults.Rows(dtResults.Rows.Count - 1) 'this gets the last row index
                drLastRow("ClockOut") = drSourceRow("dtDateTime").ToString() 'now update the ClockOut on the last results row with the current row in the loop for the raw data 
                intClockCounter = 0 ' now that the clock out time has been set, we need to make the next loop insert a new row into the results data table again for the next clock in row, so we set the counter to 0
            End If
        Else
            'if the previous employee or date did not match, then assume its a clock in again for either a new employee or a new date
            dtResults.Rows.Add(drSourceRow("fkEmployee").ToString(), dtCurrentDate.ToShortDateString, drSourceRow("dtDateTime").ToString(), Nothing)
            'set the counter to 1 so that the next loop will update and not insert
            intClockCounter = 1
        End If
        'store the  employee from the current raw data row as last employee so that on the next lopp it can check last employee against its current employee
        intLastEmployee = drSourceRow("fkEmployee").ToString()
        'do same for date
        dtPreviousDate = dtCurrentDate
    Next
    'return the results data table to the function
    Return dtResults

推荐答案

当您使用SQL从数据库获取数据时，SQL关键字ORDER BY将起作用。以下是关键字的相关信息： SQL ORDER BY关键字 [ ^ ]

As you are getting your data from a DB by using SQL, the SQL keyword ORDER BY will do the trick. Here is information on the keyword: SQL ORDER BY Keyword[^]

这篇关于如何将时钟输入/输出时间从SQL服务器排序到数据网格视图中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！