Link to home
Start Free TrialLog in
Avatar of Karen Wilson
Karen WilsonFlag for United States of America

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(DateName) 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.ToString("m/yyyy")) = mmyy _
                             Into Sum(id.Cumulative)

        Next

Any help would be most appreciated!  
ASKER CERTIFIED SOLUTION
Avatar of Karen Wilson
Karen Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Karen Wilson

ASKER

I figured it out myself....  after reading my own question!!