Producing Monthly Totals with SQL
Posted on 2011-09-15
I've never tried this with SQL, but I'm sure it's possible.
I have data prepared nicely with the following self-explanatory fields:
ID, DAILY_AMOUNT, TERM_START, TERM_END
I want to turn this into a report that yields total DAILY_AMOUNT where the selection criteria specify a start and end date.
So, for example, this could be used to answer the question, "How much did I spend on widgets in July of 2010?".
So If I spent $5 every day on widgets from, say, 2010/6/12 to 2010/7/5, the answer would be $25 ($5 x 5 Days in July).
What's the smart way to do this?