I Need to Calculate The Following in GridView.
How to calculate SUM Total Number of Target Requests Achieved and Missed for a Group of Departments.
Target Days are predefined in Database and Depending on the Dateandtime IN and OUT Achieved or Missed is Declared...
I Have Table Named :
TblDept - Fields:
DeptID, Dept Name, TargetDays
TblProjects - Fields:
DEPTID, Request No,DateandTimeIN, DateandTimeOUT, Status
i need to calculate:
How many requests achieved target and how many missed
GROUP BY Clause will be used for Department
it is like
DEPARTMENT || ACHIEVED REQUESTS || MISSED REQUESTS || TOTAL REQUESTS
====================================================================
AAA || 10 || 05 || 15
BBB || 08 || 03 || 11
CCC || 09 || 10 || 19
====================================================================
TOTAL || 27 || 18 || 45
...Anyone ?
Here is my code:
Protected Function BusinessDaysExcludeWeekendsAndHolidays(ByVal StartDate As Date, ByVal StopDate As Date) As Double
con As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\projectManagement\Database.accdb")
con.Open()
Dim SelectStr As String = "Select [Holiday] from Holidays"
Dim dap As OleDbDataAdapter = New OleDbDataAdapter(SelectStr, con)
Dim ds As New DataSet dap.Fill(ds, "x")
Dim ActualDays As Integer
Dim BusinessDays As Integer
Dim NextDate As Date
Dim x As Double
If StopDate < StartDate Then Return 0
ActualDays = DateDiff(DateInterval.Day, StartDate, StopDate, Microsoft.VisualBasic.FirstDayOfWeek.Sunday, FirstWeekOfYear.Jan1) + 2
BusinessDays = ActualDays
Dim counter As Integer = 0
For i As Integer = 0 To ActualDays - 1
NextDate = DateAdd(DateInterval.Day, i, StartDate)
If NextDate.DayOfWeek = DayOfWeek.Friday Or _ NextDate.DayOfWeek = DayOfWeek.Saturday Then
BusinessDays = BusinessDays - 1
End If
For counter = counter To ds.Tables("X").Rows.Count - 1
If ds.Tables("x").Rows(counter).Item(0) = NextDate Then
BusinessDays = BusinessDays - 1
End If
Next
counter = 0
Next
Return BusinessDays
End Function