Data Aggregation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Data Aggregation

Author

Topic

bmsra79
Starting Member

24 Posts

Posted - 2012-10-31 : 14:50:16

I have 3 Views:

View1: Country, State, Category1, Category2, Year, Quarter, Total_Sales
View2: Country, City, Category1, Category2, Year, Quarter, Total_Pop
View3: City, State, Country

Since the Views by themselves are aggregated queries, I want the most efficient way to combine all the 3 views.

Required o/p:
Country, State, Category1, Category2, Year, Quarter, Total_Sales, Total_Pop

bmsra79
Starting Member

24 Posts

Posted - 2012-10-31 : 15:01:28

Sample Values:
View1: United States, MA, Books, Publications, 2012, 2, 1500
View2: United States, Boston, Books, Publications, 2012, 2, 200000
View3: MA, Boston, United States

Issues:
- Few STATE entries in View1 not having corresponding CITY records in View2
- Few CITY records in View2 not having entries for STATE in View1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-31 : 15:50:54

It sounds like it mught be easier to just ignore the views and write a new query. I beleive it is going to be difficult/impossible to get the results you want based on the inability to join the views at the lowet grain (city) you want. But, perhaps I do not undersant the data well enough. It might help if you supply sample data and expected output, but I took a rough stab at a query that might help you:

SELECT
    View3.Country, 
    View3.State, 
    View2.Category1, 
    View2.Category2, 
    View2.Year, 
    View2.Quarter, 
    View.1Total_Sales, 
    View2.Total_Pop
FROM
    View3
INNER JOIN
    View2
    ON View3.Country = View2.Country
    AND View3.City = View2.City
INNER JOIN
    View1
    ON View3.Country = View1.Country
    AND View3.City = View1.State
    AND View2.Category1 = View1.Category1
    AND View2.Category2 = View1.Category2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources