Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB ASP.NET
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
Posted 29-Sep-12 20:49pm
Edited 21-Oct-12 5:50am
Maciej Los136.1K
v6
Comments
Sandeep Mewara at 30-Sep-12 2:52am
   
And what have you tried so far?
saqibghulam at 30-Sep-12 3:07am
   
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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

if u want to calculate total in grid footer then u will have write the code in row databound event .
 
can u tell what u want ?
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Getting Total at theFooter is no problem.
 
I Need to to know THE QUERY or Solution
How to apply this function that it will get me Summary of Each Departments Performance
 

 
I need only to know how TOTAL of requests have been Acheived and how many not Achieved
 
The Gridview I am using right now is as Follows:
Now this is the Detailed Report i need only Summary
 
<asp:gridview id="GridView1" runat="server" autogeneratecolumns="False" xmlns:asp="#unknown">
BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px"
CellPadding="3" CssClass="GridViewStyle" DataSourceID="SqlDataSource1"
GridLines="Vertical" AllowPaging="True" DataKeyNames="ProjectID" PageSize="5">


<asp:boundfield datafield="ProjectName" headertext="Topic" sortexpression="ProjectName">


<asp:boundfield datafield="DateRecieved" headertext="Date IN">
SortExpression="DateRecieved" DataFormatString="{0:dd-MM-yyyy}" >


<asp:boundfield datafield="AssignedTo" headertext="Emp Name">
SortExpression="AssignedTo" >


<asp:boundfield datafield="TimeRecieved" headertext="Time IN">
SortExpression="TimeRecieved" >


<asp:boundfield datafield="ApprovalDepartment" headertext="DEPT">
SortExpression="ApprovalDepartment" >


<asp:boundfield datafield="ProjectID" headertext="PID">
InsertVisible="False" ReadOnly="True" SortExpression="ProjectID" >


<asp:boundfield datafield="ProjectApprovedStatus">
HeaderText="Status" SortExpression="ProjectApprovedStatus" >


<asp:boundfield datafield="DateSentForApproval">
DataFormatString="{0:dd-MM-yyyy}" HeaderText="Date OUT"
SortExpression="DateSentForApproval" >



<asp:templatefield headertext="DaysPast">

<%#(Convert.ToDouble(BusinessDaysExcludeWeekendsAndHolidays(Eval("DateRecieved"), Eval("DateSentForApproval"))))%>


<asp:boundfield datafield="TargetDays" headertext="TargetDays">
SortExpression="TargetDays" />



<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />

  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 310
1 Sergey Alexandrovich Kryukov 176
2 PhilLenoir 164
3 Magic Wonder 162
4 Gihan Liyanage 119
0 Sergey Alexandrovich Kryukov 6,140
1 OriginalGriff 5,195
2 CPallini 2,473
3 Richard MacCutchan 1,607
4 Abhinav S 1,505


Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 21 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100