Having trouble getting the average for duration in (mm:ss)-CodePudding

For all who assist with this, thank you. My goal is to get the duration of chat conversations for my employees and then calculate their year average. The data is being inserted into a database as short text in the format of (mm:ss or 18:30). I have tried converting and parsing the data several different ways and I have looked through numerous explanations and solutions on SO.com so far with nothing working the way I would like. The below is my display procedure to bring the data into the form. I know I could have done this in an easier way, but I am fairly new to VB.net and coding in general.

    Private Sub DisplayJanMetrics()
    'Open a connection to the database and then assign the values from the appropriate metric columns to the appropriate labels.
    Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CoachingDB.accdb"
    Dim conn As New OleDbConnection(str)

    Try
        conn.Open()
        Dim sql As String = "SELECT Duration, CSAT, Away, Quality, Development FROM January WHERE Employee =" & "'" & cmbEmployee.SelectedItem.ToString & "'"
        Dim cmd As New OleDbCommand(sql, conn)
        Dim myReader As OleDbDataReader = cmd.ExecuteReader()

        While myReader.Read
            lblJanDuration.Text = myReader("Duration").ToString
            lblJanCSAT.Text = myReader("CSAT").ToString
            lblJanAway.Text = myReader("Away").ToString
            lblJanQual.Text = myReader("Quality").ToString
            lblJanDev.Text = myReader("Development").ToString
        End While

    Catch ex As OleDbException
        MsgBox(ex.ToString)
    Finally
        conn.Close()
    End Try
End Sub

Once the data has been loaded to the correct labels I have a button and click event to calculate the average from the labels - the other ones I was able to do easily because I could parse them to doubles and then do the calculation from there. Here is an image of what the form looks like, I think it will help all of you get an idea of what I am trying to accomplish.

This is what the form layout looks like

CodePudding user response：

It is a good idea to separate your user interface code from your database code. You can use the same data retrieval function for any month by passing the month as TableName.

Database objects Connection, Command, and DataReader all need to be disposed and closed so they are placed in Using blocks. You don't want to hold the connection open while you update the user interface. Just return a DataTable and update the UI with that.

CalculateAverage first creates an array of the labels you want to include in the average. You can include all 12 but the average will not include empty labels. (IsNullOrEmpty)

Separate the string into minutes and seconds. Get the total seconds and add to the list.

Get the average number of seconds by calling Average on List(Of Integer).

Finally, turn the average seconds back into minutes and seconds and format a string to display.

Private Sub DisplayJanMetrics()
    Dim dt As DataTable
    Try
        dt = GetEmployeeData(cmbEmployee.SelectedItem.ToString, "January")
    Catch ex As Exception
        MsgBox(ex.Message)
        Exit Sub
    End Try
    If dt.Rows.Count > 0 Then
        lblJanDuration.Text = dt(0)("Duration").ToString
        lblJanCSAT.Text = dt(0)("CSAT").ToString
        lblJanAway.Text = dt(0)("Away").ToString
        lblJanQual.Text = dt(0)("Quality").ToString
        lblJanDev.Text = dt(0)("Development").ToString
    End If
End Sub

Private Function GetEmployeeData(Employee As String, TableName As String) As DataTable
    Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CoachingDB.accdb"
    Dim sql As String = $"SELECT Duration, CSAT, Away, Quality, Development FROM {TableName} WHERE Employee = @Employee;"
    Dim dt As New DataTable
    Using conn As New OleDbConnection(str),
            cmd As New OleDbCommand(sql, conn)
        cmd.Parameters.Add("@Employee", OleDbType.VarChar).Value = Employee
        conn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

Private Function CalculateAverage() As String
    Dim lst As New List(Of Integer)
    Dim labels() = {Label1, Label2, Label3, Label4} 'an array of 1 thru 12
    For Each label In labels
        If Not String.IsNullOrEmpty(label.Text) Then
            Dim fields = label.Text.Split(":"c)
            Dim mins = CInt(fields(0))
            Dim secs = CInt(fields(1))
            lst.Add(mins * 60   secs)
        End If
    Next
    Dim avg = lst.Average
    Dim avgMins = Math.Truncate(avg / 60)
    Dim remainderSec = avg - avgMins * 60
    Return $"{avgMins}:{remainderSec}"
End Function