var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Year to date by Month
I have a table with 2 fields: DateEntered and Amount.
Simply enough I want to create a report based on the total amount per month and have it display:
Jan $total of [amount]
Feb $total of [amount]
March $total of [amount]
Current Year Total [total of column]
I would also like to show the previous year in the next column laid out in the same format.
What is the best way to go about it.
The closest I have is a running query (see below)
SumOfAmount Expr2 Expr1
$520.00 2002 1
$615.00 2002 9
$665.00 2003 1
$50.50 2003 2
$207.00 2003 3
$333.00 2003 4
$333.00 2003 5
$216.69 2003 6
$105.00 2003 7
$98.00 2003 8
$5,572.09 2003 9
$946.00 2003 10
$44.00 2003 12
I'd like to extract this info into a report with the above fromat
editted because I misread the question.. I'll work on it.
Ok, the first issue you're looking at is creating an aggregate value by month. You can accomplish this using a combination of a grouping clause by month, and a subselect for previous year(s) values:
SELECT month(yourDate), SUM(yourCash), (SELECT month(yourDate), SUM(yourCash) FROM yourTable WHERE year(yourDate) = year(date - 1)
WHERE year(yourDate) = year(date);