Solved

How to dynamically aggregate columns based on date range

Posted on 2010-11-15
9
676 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


0
Comment
Question by:Scarlett20
  • 5
  • 4
9 Comments
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34140208
Do you want quarterly aggregate data or is that just a coincidence?

-G
0
 

Author Comment

by:Scarlett20
ID: 34140278
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
0
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34140350
From your table VisitProjections, how do you determine the year?
-G
0
 

Author Comment

by:Scarlett20
ID: 34140428
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..
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34140664
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

0
 
LVL 10

Accepted Solution

by:
GDG_DBA earned 500 total points
ID: 34140780
Once you have the sample above you can try this query which presents the data the way you showed in your samples.  This can be turned into a view to present the data this way if you wish.

-G
select SiteName, year, SUM(jan) 'Jan' 

, SUM(Feb) 'Feb' 

, SUM(Mar) 'Mar' 

, SUM(Apr) 'Apr' 

, SUM(May) 'May' 

, SUM(Jun) 'Jun' 

, SUM(Jul) 'Jul' 

, SUM(Aug) 'Aug' 

, SUM(Sep) 'Sep' 

, SUM(Oct) 'Oct' 

, SUM(Nov) 'Nov' 

, SUM(Dec) 'Dec' 

from 

(select siteName 

, DATENAME(year, monthdate) 'Year'

, case when DATEPART(month, monthdate) = 1 then ProjectedVisits else 0 end as 'January'

, case when DATEPART(month, monthdate) = 2 then ProjectedVisits else 0 end as 'February'

, case when DATEPART(month, monthdate) = 3 then ProjectedVisits else 0 end as 'March'

, case when DATEPART(month, monthdate) = 4 then ProjectedVisits else 0 end as 'April'

, case when DATEPART(month, monthdate) = 5 then ProjectedVisits else 0 end as 'May'

, case when DATEPART(month, monthdate) = 6 then ProjectedVisits else 0 end as 'June'

, case when DATEPART(month, monthdate) = 7 then ProjectedVisits else 0 end as 'July'

, case when DATEPART(month, monthdate) = 8 then ProjectedVisits else 0 end as 'August'

, case when DATEPART(month, monthdate) = 9 then ProjectedVisits else 0 end as 'September'

, case when DATEPART(month, monthdate) = 10 then ProjectedVisits else 0 end as 'October'

, case when DATEPART(month, monthdate) = 11 then ProjectedVisits else 0 end as 'November'

, case when DATEPART(month, monthdate) = 12 then ProjectedVisits else 0 end as 'December'

from #VisitProjections) VP (SiteName, [year], Jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])

group by SiteName, year

Open in new window

0
 

Author Closing Comment

by:Scarlett20
ID: 34145633
Excellent - everything worked great!

Thanks for your help.
0
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34147431
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
0
 

Author Comment

by:Scarlett20
ID: 34148483
Just added the unique constraint - thanks!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article discusses the Chain of Responsibility pattern, explaining What it is;Why it is; andHow it is At the end of this article, I hope you will be able to describe the use and benefits of Chain of Responsibility.  Backgrou…
Dependencies in Software Design In software development, the idea of dependencies (http://en.wikipedia.org/wiki/Coupling_%28computer_programming%29) is an issue of some importance. This article seeks to explain what dependencies are and where they …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now