troubleshooting Question

How to dynamically aggregate columns based on date range

Avatar of Scarlett20
Scarlett20 asked on
Microsoft SQL Server 2005Programming TheorySQL
9 Comments1 Solution728 ViewsLast Modified:
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
etc etc..

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
A       100
B         85

If I were to hard code just one date range my code would look like this:
update tempZ                  
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


ASKER CERTIFIED SOLUTION
G Godwin
Database Administrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros