One Query Result as another Query Column Filed

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

SQL Server Development (2000)

One Query Result as another Query Column Filed

Author

Topic

prakash731
Starting Member

1 Post

Posted - 2009-04-16 : 05:49:35

Hi,

I need a Query which helps me to generate the reports.

I am giving the table structure also

I have two tables tbl_Budject and tbl_Actual

tbl_Budject:

Slno Jan Feb Mar Year
1 200 300 400 2009
2 100 200 300 2008

tbl_Actual:

Slno Mon Amount Year
1 Jan 100 2009
2 Feb 140 2009
3 Mar 150 2009
4 Jan 90 2008
5 Feb 80 2008
6 Mar 70 2008

I need a Query which gets the value from tbl_Actual table and I want that value as column filed of tbl_Budject

ex:-

"Select Jan from tbl_Budject"

Result:- 200

"Select amount from tbl_Actual where Mon='Jan'

Result:- 100

Now I nedd the Query like as

Seleect sum(*** + amount) from tbl_Actual where where Mon='Jan'

In *** values I want to place "Select Jan from tbl_Budject"

Seleect sum((Select Jan from tbl_Budject) + amount) from tbl_Actual where where Mon='Jan'

But I don't want to change every time in both places which i Placed Jan

I only give the month name in where condition then it should replace in sub query also.
ex:-

Seleect sum((Select Feb from tbl_Budject) + amount) from tbl_Actual where where Mon='Feb';

Seleect sum((Select Mar from tbl_Budject) + amount) from tbl_Actual where where Mon='Mar';

----

The main aim is, I want to use as a colum fileds from the result of another table.

Please let me know.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 06:42:38

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

dsindo
Starting Member

45 Posts

Posted - 2009-04-16 : 18:10:46

declare @mon varchar(10), @str1 varchar(1000)
set @mon = 'Jan'

set @str1

='
select mon, amount+budget as total
from
(

select mon, sum(amount) as amount, sum(b.budget) as budget from #tbl_Actual
cross join
(
select sum('+@mon+') as budget from #tbl_Budject
) b
where mon='''+@mon+'''
group by mon

) m'

exec (@str1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources