Karen Wilson
asked on
How do I sum data by month/YYYY in VB code in VS2010?
Hello:
I would like to sum the total of several columns by each month based upon a user selected date range.
This is what I have so far:
HERE I GRAB ALL THE DATES AVAILABLE IN THE DATE RANGE
-------------------------- ---------- ---------- ---------- ---------- ---------- ------
Dim dateList = From id In wellData.tblBWgallons _
Where id.InputDate >= CDate(pckrStartDate.Text) _
AndAlso id.InputDate <= CDate(pckrEndDate.Text) _
AndAlso id.Cumulative > 0 _
Order By id.InputDate Ascending _
Select id.InputDate
HERE I MAKE A LIST BY M/YYYY
-------------------------- ---------- ---------- ---------- ------
Dim MonthList As New List(Of String)
For Each m As Date In dateList
Dim mon As String = CStr(m.Month)
Dim YY As String = CStr(m.Year)
Dim DateName As String = mon & "/" & YY
If Not MonthList.Contains(DateNam e) Then
MonthList.Add(DateName)
Else
'do nothing
End If
Next
PROBLEM AREA - I CAN'T FIGURE OUT HOW TO GRAB THE DATA BY THE LIST
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
For Each rec In MonthList
Dim mmyy As String = rec (EXAMPLE mmyy would be 3/2010)
Dim getRecords = Aggregate id In wellData.tblBWgallons _
Where CDate(id.InputDate.Value.T oString("m /yyyy")) = mmyy _
Into Sum(id.Cumulative)
Next
Any help would be most appreciated!
I would like to sum the total of several columns by each month based upon a user selected date range.
This is what I have so far:
HERE I GRAB ALL THE DATES AVAILABLE IN THE DATE RANGE
--------------------------
Dim dateList = From id In wellData.tblBWgallons _
Where id.InputDate >= CDate(pckrStartDate.Text) _
AndAlso id.InputDate <= CDate(pckrEndDate.Text) _
AndAlso id.Cumulative > 0 _
Order By id.InputDate Ascending _
Select id.InputDate
HERE I MAKE A LIST BY M/YYYY
--------------------------
Dim MonthList As New List(Of String)
For Each m As Date In dateList
Dim mon As String = CStr(m.Month)
Dim YY As String = CStr(m.Year)
Dim DateName As String = mon & "/" & YY
If Not MonthList.Contains(DateNam
MonthList.Add(DateName)
Else
'do nothing
End If
Next
PROBLEM AREA - I CAN'T FIGURE OUT HOW TO GRAB THE DATA BY THE LIST
--------------------------
For Each rec In MonthList
Dim mmyy As String = rec (EXAMPLE mmyy would be 3/2010)
Dim getRecords = Aggregate id In wellData.tblBWgallons _
Where CDate(id.InputDate.Value.T
Into Sum(id.Cumulative)
Next
Any help would be most appreciated!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER