How to dynamically aggregate columns based on date range
Posted on 2010-11-15
I want to aggregate total columns (ie. april, may etc.) based on parameter date range, and then update another table with those aggregates.
The table containing the totals is named VisitProjections and looks like this (FY starting in april):
Site april may june july aug sept oct nov dec jan feb mar
A 25 35 40 20 15 22 44 33 22 21 23 46
B 21 31 33 23 12 20 40 36 21 20 25 35
This is a very small table and so far I only have data for 1 FY, but every year I'll be given 12 new months. Let's day the date range is 2010-04 thru 2010-06. For site A I'd want to add 25 + 35 + 40. The lowest granular level is month - and the start and end dates may be the same (1 month). I have a lot of flexibility with this table so I can rename columns in any fashion.
The table to be updated is named tempZ and looks like this using the above date range:
Site Projected Visits
If I were to hard code just one date range my code would look like this:
set ProjectedVisits = (b.april + b.may + b.june)
from tempZ a inner join VisitProjections b
on a.groupno = b.site
What's the best way to design the VisitProjections table and create dynamic code to update table tempZ?
Thanks for your help in advance, Scarlett20