How To Calculate Business Working Days and Hours inside GRIDVIEW

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

Sandeep Mewara - 30 Sep '12 - 2:52

And what have you tried so far?

saqibghulam - 30 Sep '12 - 3:07

i have made a function using Vb and applied it and it is giving me detailed View of Startdate and EndDate but i need to Segregate it into day and Time Summary. Function is: 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

0	OriginalGriff	233
1	Sergey Alexandrovich Kryukov	208
2	Rohan Leuva	195
3	Abhinav S	168
4	Mahesh Bailwal	165

0	Sergey Alexandrovich Kryukov	8,474
1	OriginalGriff	6,714
2	CPallini	3,603
3	Rohan Leuva	2,853
4	Maciej Los	2,234

How To Calculate Business Working Days and Hours inside GRIDVIEW

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

		9,881,908 members (53,642 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using

Interested
Ignored

How To Calculate Business Working Days and Hours inside GRIDVIEW

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us