Eliminate cross apply - 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

Other SQL Server 2008 Topics

Eliminate cross apply

Author

Topic

Codesearcher
Starting Member

8 Posts

Posted - 2011-11-05 : 05:35:59

Can i anyways eliminate this cross apply in the below query and achieve the same result?

DECLARE @PRMDONO VARCHAR(20)
DECLARE @PRMCNTAREANO VARCHAR(20)
DECLARE @CONTRIBID VARCHAR(20)

SET @PRMDONO='8800026'
SET @PRMCNTAREANO=''
SET @CONTRIBID = '00000089'

select T.* ,round(rt.runningvalue/rt.runningVolume,2) as cumulative_price from
(SELECT DISTINCT
A.SALES_MONTH,
SUM(A.TAKE_VOL)AS VOLUME,
ISNULL(SUM(A.PMNT_AMT),0) AS VALUE,
ROUND(ISNULL(SUM(A.PMNT_AMT),0) /
SUM(A.TAKE_VOL),2) AS AVERAGE_PRICE,
A.DO_NO,A.CONT_AREA_NO,A.CONTRIB_WI_OWNER_ACCT_NO
FROM tblGbsTransaction A
WHERE
CONTRIB_WI_OWNER_ACCT_NO = @CONTRIBID
AND
(ISNULL(@PRMCNTAREANO, N'') = N'' OR A.CONT_AREA_NO in (@PRMCNTAREANO)) AND
(ISNULL(@PRMDONO, N'') = N'' OR A.DO_NO in(@PRMDONO)) AND
SALES_MONTH >= '200101'
group by A.SALES_MONTH,A.CONTRIB_WI_OWNER_ACCT_NO,A.DO_NO,A.CONT_AREA_NO

) t
cross apply
(select sum(VOLUME) as runningVolume,SUM(value) as runningvalue from
(SELECT DISTINCT A.SALES_MONTH,
SUM(A.TAKE_VOL)AS VOLUME,
ISNULL(SUM(A.PMNT_AMT),0) AS VALUE,
ROUND(ISNULL(SUM(A.PMNT_AMT),0) / SUM(A.TAKE_VOL),2) AS AVERAGE_PRICE,
A.DO_NO,A.CONT_AREA_NO,A.CONTRIB_WI_OWNER_ACCT_NO
FROM tblGbsTransaction A
WHERE
CONTRIB_WI_OWNER_ACCT_NO = @CONTRIBID
AND
(ISNULL(@PRMCNTAREANO, N'') = N'' OR A.CONT_AREA_NO in (@PRMCNTAREANO)) AND
(ISNULL(@PRMDONO, N'') = N'' OR A.DO_NO in(@PRMDONO)) AND
SALES_MONTH >= '200101'
group by A.SALES_MONTH,A.CONTRIB_WI_OWNER_ACCT_NO,A.DO_NO,A.CONT_AREA_NO
)A where A.SALES_MONTH <= t.SALES_MONTH) as rt order by t.SALES_MONTH desc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-05 : 08:53:53

any particular reason why you can't use CROSS APPLY ?

KH
[spoiler]Time is always against us[/spoiler]

Codesearcher
Starting Member

8 Posts

Posted - 2011-11-08 : 02:27:13

Performance of the query is poor by appying Cross apply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 03:17:37

quote:
Originally posted by Codesearcher

Performance of the query is poor by appying Cross apply

depends on how you use it and also presence of proper indexes etc
perhaps you can give us an idea of what you're trying to do inside apply

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources