Use LINQ to convert multiple dates into ranges

Question

I need help applying settings across a calendar year. My users are entering numerical targets into a database according to the season in a calendar. Think, apple cider will sell higher during the fall (September through November) and less during the rest of the year (December through August). However, apple juice sells evenly throughout the year.

To simplify data entry for my users, they enter a target sales number along with the month that number will start for each product.

For example, the data in the database will look like this (There are a max of two targets for any product):

Apple Cider - September - 5,000 gallons
Apple Cider - December - 1,000 gallons
Apple Juice - September - 3,000 gallons

I also have our fiscal calendar in a database table, so I know what fiscal year, month, number of weeks in the month, etc. are associated with any day.

I need to apply this data to a report, but I need data for every month in the year, not just what the user has entered.

I'm having trouble using LINQ to join to my fiscal calendar to create an exploded view of the data? How can I start with a list of all months in the year, for each product, place the targets in the calendar, then fill in the blanks. For example for Apple Cider using the example above

January
February
March
April 
May 
June 
July 
August 
September - 5,000 gallonws
October
November 
December - 1,000 gallons


Apple Cider - September - 5,000 gallons
Apple Cider - October - 5,000 gallons
Apple Cider - November - 5,000 gallons
Apple Cider - December - 1,000 gallons
Apple Cider - January - 1,000 gallons
Apple Cider - February- 1,000 gallons
Apple Cider - March - 1,000 gallons
Apple Cider - April- 1,000 gallons
Apple Cider - May - 1,000 gallons
Apple Cider - June - 1,000 gallons
Apple Cider - July - 1,000 gallons
Apple Cider - August - 1,000 gallons

Apple Juice - September - 3,000 gallons
Apple Juice - October - 3,000 gallons
Apple Juice - November - 3,000 gallons
Apple Juice - December - 3,000 gallons
Apple Juice - January - 3,000 gallons
Apple Juice - February - 3,000 gallons
Apple Juice - March - 3,000 gallons
Apple Juice - April - 3,000 gallons
Apple Juice - May - 3,000 gallons
Apple Juice - June - 3,000 gallons
Apple Juice - July - 3,000 gallons
Apple Juice - August - 3,000 gallons

Thank you for revising my question Thomas. It should make it easier for others to read my post. — Glen Ciborowski, Mar 30 '14 at 23:48

score 0 · Answer 1 · answered Mar 31 '14 at 17:09

I'm not sure how much help LINQ would be in this scenario.

You can achieve the required transformation relatively simply with a for loop:

' Sample data using anonymous types
Dim entries() =
    {New With {.Name = "Apple Cider", .Year = 2012, .Month = 9, .Quantity = 5000},
     New With {.Name = "Apple Cider", .Year = 2012, .Month = 12, .Quantity = 1000},
     New With {.Name = "Apple Juice", .Year = 2013, .Month = 9, .Quantity = 3000}}
' Dictionary keyed by date
Dim dict = entries.ToDictionary( _
    Function(entry) New DateTime(entry.Year, entry.Month, 1))
' Current is first entry
Dim currentEntry = entries(0)
Dim epoch = New DateTime(currentEntry.Year, currentEntry.Month, 1)
' Iterate over 24 months
For i = 0 To 23            
    Dim currentMonth = epoch.AddMonths(i)
    ' Update current based on current month
    If dict.ContainsKey(currentMonth) Then
        currentEntry = dict(currentMonth)
    End If
    ' Write data
    Console.WriteLine("{0} - {1} - {2} gallons",
                      currentEntry.Name,
                      MonthName(currentMonth.Month),
                      currentEntry.Quantity)
Next

Hogan · Answer 2 · 2014-04-02T03:49:58.987

To do this with Linq use Range() (in cases where something can be done in a loop often range can be substituted.)

Here is an example using Enumerable.Range and @PhillipTrelford's example data:

Sub Main
  Dim entries() =  {New With {.Name = "Apple Cider", .Year = 2012, .Month = 9, .Quantity = 5000}, _
                    New With {.Name = "Apple Cider", .Year = 2012, .Month = 12, .Quantity = 1000}, _
                    New With {.Name = "Apple Juice", .Year = 2013, .Month = 9, .Quantity = 3000}}

  Dim dict = entries.ToDictionary( Function(entry) New DateTime(entry.Year, entry.Month, 1))

  Dim startDate as DateTime = New DateTime(2012,9,1)

  Dim curDefault = New With {.Name = "", .Quantity = 0}
  Dim result = Enumerable.Range(0, 24).Select(Function(x)
    Dim thisdate = startDate.AddMonths(x)
    If dict.ContainsKey(thisdate) Then
      curDefault.Name = dict(thisdate).Name
      curDefault.Quantity = dict(thisdate).Quantity
    End If
    Return String.Format("{0} - {1} - {2} gallons",curDefault.Name,thisdate.ToString("MMMM"),curDefault.Quantity)

  End Function)

  result.Dump
End Sub

This code runs just fine and give expected results using linqpad (see linqpad.com)

Apple Cider - September - 5000 gallons 
Apple Cider - October - 5000 gallons 
Apple Cider - November - 5000 gallons 
Apple Cider - December - 1000 gallons 
Apple Cider - January - 1000 gallons 
Apple Cider - February - 1000 gallons 
Apple Cider - March - 1000 gallons 
Apple Cider - April - 1000 gallons 
Apple Cider - May - 1000 gallons 
Apple Cider - June - 1000 gallons 
Apple Cider - July - 1000 gallons 
Apple Cider - August - 1000 gallons 
Apple Juice - September - 3000 gallons 
Apple Juice - October - 3000 gallons 
Apple Juice - November - 3000 gallons 
Apple Juice - December - 3000 gallons 
Apple Juice - January - 3000 gallons 
Apple Juice - February - 3000 gallons 
Apple Juice - March - 3000 gallons 
Apple Juice - April - 3000 gallons 
Apple Juice - May - 3000 gallons 
Apple Juice - June - 3000 gallons 
Apple Juice - July - 3000 gallons 
Apple Juice - August - 3000 gallons

Thank you for your response Hogan. Your code snippet does give me the output that I am looking for but in both your response and Phillip's I noticed that you added a year attribute to the entries which I currently do not have. However, I see the importance of year to roll from one calendar year to the next. (Going from December represented as 12 to January represented as 1 won't work if you're doing +1). When making the dictionary I could use current year instead of entry.year. Instead of a start date of 2012, I could also use current year. Correct? — Glen Ciborowski, Apr 03 '14 at 05:02
@GlenCiborowski - Sure just make sure the rest of the code is not expecting a certain year. — Hogan, Apr 03 '14 at 15:39

Use LINQ to convert multiple dates into ranges

2 Answers2