Querying table2 with results from table1

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 2000 Forums

Transact-SQL (2000)

Querying table2 with results from table1

Author

Topic

miamikk
Starting Member

19 Posts

Posted - 2006-11-08 : 14:27:18

I need help in writing a query.

The query should get top 10 items and their values and %Share from current year and the values for the same items from previous year table.

I was able to write code that gets items their values and %Share from table 1 and equivalent values from table 2 but I am trouble in writing code for calculating %share from table2.

The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.

I am attaching some images below to give more information.

Image of results from my query.
http://www.fiu.edu/%7Eatmakurk/ustrade/7-5.jpg

Image of how the final output should look like.
http://www.fiu.edu/%7Eatmakurk/ustrade/7_2.jpg

The Store Procedure code is:

SELECT @SQuery = 'select top 10 a.commodity1 as HS4, sum(a.all_val_mo) as [Amount1],

(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share1],

(select sum(b.all_val_mo) from '+ @TblName2 +' b where b.commodity1=a.commodity1
and b.stat_month <=' + @Month + ' and b.district=' + @District +') as [Amount2]

--(sum(b.all_val_mo)/(select Sum(b.all_val_mo) FROM ' + @TblName2 + 'b where b.commodity1=a.commodity1 and
--b.stat_month <=' + @Month + ' and b.district=' + @District +'))*100 as [% Share2]

from '+ @TblName1 + 'a where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1
order by [Amount1] desc'

EXEC sp_executesql @SQuery
END

Benholio
Starting Member

4 Posts

Posted - 2006-11-08 : 16:22:27

I think you need to use the query you have as a derived table, and join it with the second table. The basic form of the final query should look like:


SELECT a1.HS4, a1.Description, a1.[Amount], a1.[% Share], 
a2.[Amount], a2.[% Share] 

FROM (SELECT <the original query>) as a1

LEFT OUTER JOIN (SELECT <query to get HS4, [Amount], [% Share] 
from the second table) as a2 ON a1.HS4 = a2.HS4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources