Solved

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

Posted on 2011-03-01
2
220 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
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 gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

18 Experts available now in Live!

Get 1:1 Help Now