Want to optimize query... - 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 2005 Forums

Transact-SQL (2005)

Want to optimize query...

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-11-04 : 06:01:45

Hello All,

Can any one tell me what is the alternate way to write below query?

Select (IsNull(X.Amount,0) - IsNull(Y.Amount,0)) * -1
From
(
Select 'Amount' = Round((Sum(Notional) / 1000000),0)
From Trade T
Where Version = (Select Max(Version) From Trade
Where Deal = T.Deal and Instance = T.Instance
)
And Product_Code = 2600
And SessionId = 1185
And Maturity_Date = '09/20/2013'
And IsNull(Internal_Comment,'NA') like 'ROLLS'
And Trade_Vision In (
Select Distinct TradeVisionId
From DBO.TradeVision_Rolls T
Where TradeVision_Version = ( Select max(TradeVision_Version)
From DBO.TradeVision_Rolls
Where TradeVisionId = T.TradeVisionId
And SetId = T.SetId
)
AND ISNULL(DONE,0) = 1
AND SetId = 115
AND EntityId = 2704
AND (FarDate = '09/20/2013' )
)
) X,

(
Select 'Amount' = Round((Sum(Notional) / 1000000),0)
From Trade T
Where Version = (Select Max(Version) From Trade
Where Deal = T.Deal and Instance = T.Instance
)
And Product_Code = 2600
And SessionId = 1185
And Maturity_Date = '09/20/2013'
And IsNull(Internal_Comment,'NA') like 'ROLLS'
And Trade_Vision In ( Select Distinct TradeVisionId
From DBO.TradeVision_Rolls T
Where TradeVision_Version = ( Select max(TradeVision_Version)
From DBO.TradeVision_Rolls
Where TradeVisionId = T.TradeVisionId
And SetId = T.SetId
)
AND ISNULL(DONE,0) = 1
AND SetId = 115
AND EntityId = 2704
AND (FarDate = '09/20/2013' )
)
) Y

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 11:12:53

it would be much easier if you illustrate what you're looking for with some sample data.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-04 : 19:37:01

Here's an alternative way


select 0 * -1

:)

From what I can tell your query is written so y.Amount is always = to x.Amount. So if you subtract one from the other followed by multiplying that amount by -1. (i.e. (10-10) * -1)

It in essance will always be 0 * -1

First provide a sample data set, then provide your desired results. Someone can help you out at that point. There is a much more efficiant way to accomplish, but right now we don't even have a starting block to go off of, so no one is really going to be able to point you in the correct direction.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources