Aggregate SQL query grouping by month

Question

I have a database of Transactions (Access 2007) that are recorded in hourly, daily and monthly intervals. I would like to view them in a meaningful way (instead of hour-by-hour energy usage, which is how it's stored, I want all usage for the month of January, 2011). The tables that I want to operate on have this general format:

CustID|CustomerCode|TransactionDate|(Transaction Hour depending on table)|Usage

So If I want to take a table like that and make a view that looked something like this

BillingPeriod|Usage(mWh)
1/2011     |500
2/2011     |600
3/2011     |700
etc

How would I go about doing that? The transaction dates can be any date, and the transaction hours can be 1-24. The query itself doesn't seem that hard, something along the lines of:

SELECT TransactionDate, SUM(Usage)
FROM UsageTable
Where (TransactionDate Between [Some Start Date] AND[Some End Date])
GROUP BY TransactionDate;

The problem is formatting. I obviously can't group by transactiondate for my desired results, I just wrote it so the query was semantically correct. Maybe I could do something like

SELECT Mid(TransactionDate,0,2) + Mid(TransactionDate, 6, 4)?

Any help would be appreciated

Answer 1

It seems that you would need to group by both the month and the year. Otherwise, you'll have January 2010 and January 2011 combined:

SELECT YEAR(TransactionDate), MONTH(TransactionDate), SUM(Usage)
FROM YourTable
WHERE (TransactionDate Between [Some Start Date] AND[Some End Date])
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
ORDER BY YEAR(Created), MONTH(Created)

I don't know if your version of SQL has the MONTH and YEAR functions, so you may have to use DATEPART.

Answer 2

So as to Avoid conversion to strings, concatenations and conversion back to dates, use DATEADD() and DATEDIFF().

SELECT
  DATEADD("m", DATEDIFF("m", 0, TransactionDate), 0) AS TransactionMonth,
  SUM(Usage)                                         AS TotalUsage
FROM
  yourTable
WHERE
  TransactionDate BETWEEN <startDate> AND <endDate>
GROUP BY
  DATEADD("m", DATEDIFF("m", 0, TransactionDate), 0)
ORDER BY
  DATEADD("m", DATEDIFF("m", 0, TransactionDate), 0)

Answer 3

I generally use Format([TransactionDate], "yyyy-mm") because it's simple and sorts well.
As another option, you could use [TransactionDate]-Day([TransactionDate])+1, which will move every date to the first of its month. THe advantage is that you can still easily format that any way you want, or group that by quarter or year afterwards.

Answer 4

SELECT MONTH(TransactionDate),YEAR(TransactionDate), SUM(Usage) 
FROM UsageTable 
Where (TransactionDate Between [Some Start Date] AND[Some End Date]) 
GROUP BY MONTH(TransactionDate),YEAR(TransactionDate);