We help IT Professionals succeed at work.

How to dynamically aggregate columns based on date range

722 Views
Last Modified: 2012-05-10
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


Comment
Watch Question

G GodwinDatabase Administrator
CERTIFIED EXPERT

Commented:
Do you want quarterly aggregate data or is that just a coincidence?

-G

Author

Commented:
just a coindence.  It can be one or more months and also go past FY boundaries (ie.  for FY starting in april --> the range might be 2011-03  thru 2011-04)

-Scarlett20
G GodwinDatabase Administrator
CERTIFIED EXPERT

Commented:
From your table VisitProjections, how do you determine the year?
-G

Author

Commented:
So far, I don't determine the year since I only have 12 months of data so far.  But  this is how I'd add the year column:

Site   Year    jan   feb     mar   april   may   june   july   aug   sept   oct   nov   dec  
A       2010      0     0       0       25    35     40      20     15     22      44    33    22    B       2010      0     0       0       21    31     33      23     12     20      40    36    21    etc etc..
G GodwinDatabase Administrator
CERTIFIED EXPERT

Commented:
Play with this design a bit and see what you think...

Inclueded is a temp table, with some options on how the month gets stored, and some sample queries you can mess around with.

-G
create table #VisitProjections (SiteID int, SiteName varchar(25), monthDate datetime, YearNum int, MonthNum int, ProjectedVisits int)


insert into #VisitProjections values (1, 'A', '20100401', 2010, 4, 25)
insert into #VisitProjections values (1, 'A', '20100501', 2010, 5, 35)
insert into #VisitProjections values (1, 'A', '20100601', 2010, 6, 40)
insert into #VisitProjections values (1, 'A', '20100701', 2010, 7, 20)
insert into #VisitProjections values (1, 'A', '20100801', 2010, 8, 15)
insert into #VisitProjections values (1, 'A', '20100901', 2010, 9, 22)
insert into #VisitProjections values (1, 'A', '20101001', 2010, 10, 44)
insert into #VisitProjections values (1, 'A', '20101101', 2010, 11, 33)
insert into #VisitProjections values (1, 'A', '20101201', 2010, 12, 22)
insert into #VisitProjections values (1, 'A', '20110101', 2011, 1, 21)
insert into #VisitProjections values (1, 'A', '20110201', 2011, 2, 23)
insert into #VisitProjections values (1, 'A', '20110301', 2011, 3, 46)



insert into #VisitProjections values (1, 'B', '20100401', 2010, 4, 21)
insert into #VisitProjections values (1, 'B', '20100501', 2010, 5, 31)
insert into #VisitProjections values (1, 'B', '20100601', 2010, 6, 33)
insert into #VisitProjections values (1, 'B', '20100701', 2010, 7, 23)
insert into #VisitProjections values (1, 'B', '20100801', 2010, 8, 12)
insert into #VisitProjections values (1, 'B', '20100901', 2010, 9, 20)
insert into #VisitProjections values (1, 'B', '20101001', 2010, 10, 40)
insert into #VisitProjections values (1, 'B', '20101101', 2010, 11, 36)
insert into #VisitProjections values (1, 'B', '20101201', 2010, 12, 21)
insert into #VisitProjections values (1, 'B', '20110101', 2011, 1, 20)
insert into #VisitProjections values (1, 'B', '20110201', 2011, 2, 25)
insert into #VisitProjections values (1, 'B', '20110301', 2011, 3, 35)

select * 
from #VisitProjections

select SUM(projectedvisits) from #VisitProjections 
where SiteName = 'A' and YearNum  = 2010 and MonthNum  between 4 and 6

select siteName, SUM(projectedvisits) from #VisitProjections 
where YearNum  = 2010 and MonthNum  between 4 and 6
group by SiteName 

select siteName , SUM(projectedvisits) 
from #VisitProjections 
where monthDate between '20101201' and '20110228'
group by SiteName

Open in new window

Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Excellent - everything worked great!

Thanks for your help.
G GodwinDatabase Administrator
CERTIFIED EXPERT

Commented:
With this design, you will need to create a unique constraint so that each site can only have one value for projectedVisits per month.

You can also work with different ways to store and calculate the date values.  I might recommend computed columns for year and for month with a unique constraint on site, year and month.  

How ever you do it, you have options.

-G

Author

Commented:
Just added the unique constraint - thanks!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.