CODEWITHSUNDEEP
sqlvb.netchartsreport
H K
H K

Reputation: 57

Getting SQL data for a report with Time Difference count in VB ASP.Net

I have been struggling to get the report to show correctly. What I'm trying to achieve is that I have projects with estimated working hours. Employees log their Timesheet entries, the (start\end) date and time gets saved into an SQL table for this particular project and employee, so I want to report the estimated preset hours of the project against the time really spent by employees, here is the scenario of what I'm doing with code:

The Tables I have:

1-Projects Table---> ProjectID,Estimate Project Estimate Duration(in Hours) 2-ProjectResources Table--->ProjectID, ResourceID 3-Timesheet Entries Table--->Timesheetentry ID, ProjectID, UserID, StartDateTime, EndDateTime

What I have created is as follows:

1-I populate a drop down list with project Names as text and project IDs as value

2-On the list selectedIndexChanged event I query to get all resources of Project as follows:

Dim TimesheetsTotalTime As Decimal = 0 Dim query As String = "SELECT resourceid FROM projectresources where projectID='" & ProjectID & "'" Dim dt As DataTable = GetData(query)

For x As Integer = 0 To dt.Rows.Count - 1 Dim resourceID As Integer resourceID = dt.Rows(x).Item(0) totalTimesheetTime = totalTimesheetTime + GetTotalTimesheetsPerUser(ProjectID, resourceID)

Next

Private Shared Function GetTotalTimesheetsPerUser(ProjectID As Integer, ResourceID As Integer) As Decimal Dim TimesheetsTotalTime As Decimal = 0

    Dim query As String = "SELECT timesheetid FROM timesheet where projectID='" & ProjectID & "' and userid='" & ResourceID & "'"

    Dim dt As DataTable = GetData(query)

    For x As Integer = 0 To dt.Rows.Count - 1

        Dim TimesheetID As Integer
        TimesheetID = dt.Rows(x).Item(0).ToString

        TimesheetsTotalTime = TimesheetsTotalTime + GetTimeDifferenceForTask(TimesheetID)

    Next
    Return TimesheetsTotalTime

End Function

Private Shared Function GetTimeDifferenceForTask(timeSheetID As Integer) As Decimal Dim CountedHours As Decimal

    Dim query As String = "SELECT StartTime, EndTime FROM Timesheet where TimesheetID='" & timeSheetID & "'"
    Dim dt As DataTable = GetData(query)
    If dt.Rows.Count > 0 Then
        Dim startTime As DateTime = dt.Rows(0).Item(0)
        Dim endTime As DateTime = dt.Rows(0).Item(1)
        CountedHours = DateDiff(DateInterval.Hour, startTime, endTime)

        Return CountedHours
    End If

End Function

Whenever I add the final data to the RadHTMLChart the time returned as the total for all timesheet entries for all users is incorrect. I get a total of "13" hours while the number is 13.5 and for another project I get "17" hours while it should be like 29.

Please advise, thanks.

Upvotes: 0

Views: 387

Answers (1)

S. Adam Nissley
S. Adam Nissley

Reputation: 776

You get 13 instead of 13.5 because your DateDiff is calculating in DateInterval.Hour. To get the decimal result you are looking for change it to: 

CountedHours = DateDiff(DateInterval.Minute, startTime, endTime) / 60

As far as getting a result of 17 when 29 is expected...make sure that your inputs are datetime and not just time, and if the result is the same - post the input values for analysis.

Upvotes: 0

Related Questions