建立过去12个月的月度总数

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.

这是我目前必须建立一个月的总花费的代码。我希望在过去12个月内将其扩展为每月检索总数。

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()

我看到这样做的方法是向后循环通过CurrentMonth值,如果CurrentMonth值达到0,则将CurrentYear的值回滚1.使用循环变量(i)构建一个包含12个值的数组:MTotal()

What do you guys think?

你们有什么感想?

4 个解决方案

#1

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

Josh的DATEDIFF是比我原来的TOP和ORDER BY更好的解决方案

#2

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.

可以将[MonthDate]格式化为适当地显示月份/日期,或者在单独的列中显示。

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

可以对WHERE子句进行参数化,以提供要包含的合适范围的记录

#3

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.

唯一的问题是,我要求过去12个月中的每一个月的总数,而不是过去12个月的总数。否则我看到如何改进SQL而不是使用vb6代码oculd是一个更好的选择。

#4

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.

我相信这应该按预期工作。但是我仍然期待看到你们可以提出什么解决方案,或者是否有人发现ym修复问题。

#1