Building up a monthly total from past 12 months

CurrentMonth = Month(CurrentDate)
CurrentYear = Year(CurrentDate)

    SQL = "SELECT Spent, MONTH(Date) AS InvMonth, YEAR(Date) As InvYear FROM Invoices WHERE YEAR(Date) = '" & CurrentYear & "' AND MONTH(Date) = '" & CurrentMonth & "'"
    RecordSet.Open SQL, Connection, adOpenStatic, adLockOptimistic, adCmdText
    Do Until RecordSet.EOF
        MTotal(i) = MTotal(i) + RecordSet.Fields("Spent")
        RecordSet.MoveNext
    Loop
    RecordSet.Close

This is the code I currently have to build up a total spent for a given month. I wish to expand this to retrieve the totals per month, for the past 12 months.

The way I see to do this would be to loop backwards through the CurrentMonth value, and if CurrentMonth value reaches 0 roll the value of CurrentYear back 1. Using the loop variable (i) to build up an array of 12 values: MTotal()

What do you guys think?

From stackoverflow

• A group by should get you on the way.

  SELECT TOP 12
    SUM(Spent) AS Spent
    , MONTH(Date) AS InvMonth
    , YEAR(Date) AS InvYear
  FROM
    Invoices
  GROUP BY
    YEAR(Date), MONTH(Date)
  WHERE DATEDIFF(mm, Date, GETDATE(()) < 12
  

  ---

  Josh's DATEDIFF is a better solution than my original TOP and ORDER BY

  Tom H. : You probably don't want to use TOP 12. A better way would be to check for a date range. Also, avoid things like YEAR(date) = x. That prevents SQL Server from using any indexes on "date". Calculate the earliest and latest dates and do something like "date BETWEEN x AND y"

  Lieven : I Agree. The query has been updated.

• The only problem with this is that I require a monthly total, for each of the past 12 months rather then the total for the past 12 months. Otherwise I see how improving the SQL rather then using vb6 code oculd be a better option.

• I would tackle this by "rounding" the date to the Month, and then Grouping by that month-date, and totalling the Spent amount:

   SELECT SUM(Spent) AS [TotalSpent],
          DATEADD(Month, DATEDIFF(Month, 0, [Date]), 0) AS [MonthDate]
   FROM   Invoices 
   WHERE      [Date] >= '20080301'
          AND [Date] <  '20090301'
   GROUP BY DATEADD(Month, DATEDIFF(Month, 0, [Date]), 0)
   ORDER BY [MonthDate]
  

  The [MonthDate] can be formatted to show Month / Date appropraitely, or in separate columns.

  The WHERE clause can be parameterised to provide a suitable range of records to be included

  Kristen : P.S. Please ask if you would like an example of the "formatting" I refer to

  orip : +1, good solution, I ended up doing this. I also think you meant "truncating", not "rounding".

• The solution I came up with would be :

  For i = 0 To 11
      If CurrentMonth = 0 Then
          CurrentMonth = 12
          CurrentYear = CurrentYear - 1
      End If
  
      SQL = "SELECT Spent, MONTH(Date) AS InvMonth, YEAR(Date) As InvYear FROM Invoices WHERE YEAR(Date) = '" & CurrentYear & "' AND MONTH(Date) = '" & CurrentMonth & "'"
      RecordSet.Open SQL, Connection, adOpenStatic, adLockOptimistic, adCmdText
      Do Until RecordSet.EOF
          MTotal(i) = MTotal(i) + RecordSet.Fields("Spent").Value
          RecordSet.MoveNext
      Loop
      RecordSet.Close
  
      CurrentMonth = CurrentMonth - 1
  Next
  

  I believe this should work as expected. However I still look forward to seeing what solutions you guys can come up, or if anyone spots an issue with ym fix.