Need Help with Correlated SubQuery

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)

Need Help with Correlated SubQuery

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2004-02-18 : 07:38:08

hello,

can some one help me with the following query. It cuurrently does not return any results.

select max(icalendarweek) as icalendarweek ,
max(TotalSpacePreviousYear) as TotalSpacePreviousYear,
max(TotalSemaineVasPreviousYear) as TotalSemaineVasPreviousYear ,
chshowcd,
ishowyear

--into #TotalLASTYEAR
from(
select
'TotalSpacePreviousYear' = round (iSoldSFBooth,0) ,
'TotalSemaineVasPreviousYear' = isnull( round((PYr.mSoldVAS / 1000),1),1)+ round( (mHoldVAS / 1000),0),
twk.chshowcd,
twk.ishowyear,
pyr.icalendaryear,
pyr.icalendarweek

from #Weekly2 Twk inner JOIN rptweeklysales PYr On (PYr.chShowCd = TWK.chShowCd)
where
pyr.iShowYear = (select max(iShowYear)
from rptweeklysales
where chShowCd =twk.chShowCD
and iShowYear <twk.iShowYear)
and pyr.iCalendarYear = (select max(iCalendarYear)
from RptweeklySales r1
where chshowcd = twk.chshowcd and
ishowyear = twk.ishowyear)
) a
group by chshowcd,
ishowyear

However if I remove the following correlated subquery a resultset is returned.

and pyr.iCalendarYear = (select max(iCalendarYear)
from RptweeklySales r1
where chshowcd = twk.chshowcd and
ishowyear = twk.ishowyear)

But I cant remove this altogether as i do need the maximum calendar year. Can anyone help me with this correlated subquery?

Thanks In advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources