asked on
How to dynamically aggregate columns based on date range
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
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
Microsoft SQL Server 2005Programming TheorySQL
Last Comment
Scarlett20
ASKER
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
-Scarlett20
From your table VisitProjections, how do you determine the year?
-G
-G
ASKER
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..
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..
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Excellent - everything worked great!
Thanks for your help.
Thanks for your help.
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
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
ASKER
Just added the unique constraint - thanks!
-G