Using Temporary Tables - 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

General SQL Server Forums

New to SQL Server Programming

Using Temporary Tables

Author

Topic

roxcy
Yak Posting Veteran

58 Posts

Posted - 2007-08-21 : 09:07:31

Hi,
I am trying to join two tables usig two temporary tables.I want the Output as table2/table1 = Output


Select temp2.Value/temp1.Value as val
from 
(
(
select count(*)as Value from [Master] m
inner join [Spares]s on s.SId=m.SID
where  m.Valid_From between '2007-06-01' and '2007-06-30'
)temp1
Union
(
select isnull(sum(convert(numeric(10,0),s.Unit_Price)),'0') as Value
from [Order] h
inner join [Spares] s on s.Number = s.Number
where  h.Valid_Date between '2007-06-01' and '2007-06-30'
))temp2
as t

I could not find the output..
Plz help me..

Thanks..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 09:20:04

[code]Select 1.0 * temp2.Value / temp1.Value as val
from (
select count(*) as Value
from [Master] as m
inner join [Spares] as s on s.SId = m.SID
where m.Valid_From >= '2007-06-01'
m.Valid_From < '2007-07-01'
) AS temp1
cross join (
select sum(isnull(s.Unit_Price, 0)) as Value
from [Order] as h
inner join [Spares] as s on s.Number = h.Number
where h.Valid_Date >= '2007-06-01'
h.Valid_Date < '2007-07-01'
) AS temp2[/code]

E 12°55'05.25"
N 56°04'39.16"

EDIT: JOIN condition spotted by JIMF

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-21 : 09:26:26

watch out for this line
[Spares] as s on s.Number = s.Number
should that be s.number = h.number ?

Jim

roxcy
Yak Posting Veteran

58 Posts

Posted - 2007-08-21 : 09:26:28

Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources