Solved

How do I sum data by month/YYYY in VB code in VS2010?

Posted on 2011-03-01
2
221 Views
Last Modified: 2012-05-11
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!  
0
Comment
Question by:Karen Wilson
  • 2
2 Comments
 

Accepted Solution

by:
Karen Wilson earned 0 total points
ID: 35008924
I got it...  Had to flip each record in the MonthList back to a date.

For Each rec In MonthList

            Dim mmyy As Date = rec
            Dim m As String = mmyy.Month
            Dim y As String = mmyy.Year


            Dim getRecords = Aggregate id In wellData.tblBWgallons _
                             Where CDate(id.InputDate.Value).Month = m _
                             And CDate(id.InputDate.Value).Year = y _
                             Into Sum(id.Cumulative)

        Next

0
 

Author Closing Comment

by:Karen Wilson
ID: 35008935
I figured it out myself....  after reading my own question!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vb.Net Date Formatting Assistance 4 35
vb.net checkbox 7 41
Advice in Xamarin 21 47
Variable Event ? 3 19
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now