How do I sort clock in/out times from an SQL server into a datagridview

See more:

VB

sorting

DataGridView

DataTable

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

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