June 25, 2010 at 1:10 am
can i set a alias or assign to a variable
of a column so that i can use the same in the other column where i dont have to write all the logic again.
For the clarity i need like this.
select NetQty=(SellQty-BuyQty),
NetPrice=(SellQty-BuyQty) * Rate
from TransactionTable
select NetQty=(sellQty-BuyQty),
NetPrice=(sellQty-BuyQty) * Rate
-- i need like this NetPrice=NetQty*Rate
From TransactionTable
although my formula is more complex than this 🙂
June 25, 2010 at 1:27 am
I don't quite get the question.
Can you post an example? Basically I would like to see:
* A CREATE TABLE script
* Some sample data, scripted as INSERT into your test table
* The desired output, based on the sample data
If in doubt, take a look at the article linked in my signature line.
-- Gianluca Sartori
June 25, 2010 at 1:38 am
You can make use of a Derived Table like below
SELECTNetQty, NetPrice = NetQty * Rate
FROM(
SELECT NetQty = ( sellQty - BuyQty )
FROM TransactionTable
) T
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 25, 2010 at 1:40 am
Probably be easiest to create a view if you need to use the calculation in multiple statements
June 25, 2010 at 1:59 am
hi Gianluca,
here is an example
create table #TransactionTable
(
ClientCode varchar(50),
buyqty bigint,
sellqty bigint,
rate decimal
)
insert into #TransactionTable values('1001',20,30,10)
insert into #TransactionTable values('1001',10,30,8)
insert into #TransactionTable values('1001',80,30,9)
insert into #TransactionTable values('1002',2,30,20)
insert into #TransactionTable values('1002',1,30,21)
insert into #TransactionTable values('1002',8,30,22)
select NetQty=(SellQty-BuyQty),
NetPrice=(SellQty-BuyQty) * Rate
from #TransactionTable
can i use this query some way like this
select NetQty=(sellQty-BuyQty),
NetPrice=Netqty * Rate
-- i need like this NetPrice=NetQty*Rate
From #TransactionTable
June 25, 2010 at 2:02 am
Then you can use a derived table as Kingston suggested, or a CTE
June 25, 2010 at 2:07 am
hi kingstom,dev
thank for u ur quick reply.
derived/nested query is ok. but we will have a performance issue with this.
i thought there would be some other way around if i want to do it in the same query.
may be i take help of a local varaible
becuase my calculations are on the same session
June 25, 2010 at 2:10 am
Dave Ballantyne (6/25/2010)
Then you can use a derived table as Kingston suggested, or a CTE
Agreed. Thanks for posting DDL and sample data.
What Dave is calling a CTE is something like this:
;WITH IntermediateQuery
AS ( SELECT NetQty = ( sellQty - BuyQty ), *
FROM #TransactionTable
)
SELECT NetQty,
NetPrice = NetQty * Rate
FROM IntermediateQuery
-- Gianluca Sartori
June 25, 2010 at 2:12 am
virender.singh (6/25/2010)
derived/nested query is ok. but we will have a performance issue with this.
What kind of performance problem? The way of accessing the table suggested by Kingston isn't any different from accessing the table directly.
-- Gianluca Sartori
June 25, 2010 at 3:57 am
Other options:
CREATE TABLE #TransactionTable
(
clientcode VARCHAR(50) NOT NULL,
buyqty BIGINT,
sellqty BIGINT,
rate DECIMAL(5,2)
);
INSERT #TransactionTable VALUES('1001', 20, 30, 10);
INSERT #TransactionTable VALUES('1001', 10, 30, 8);
INSERT #TransactionTable VALUES('1001', 80, 30, 9);
INSERT #TransactionTable VALUES('1002', 2, 30, 20);
INSERT #TransactionTable VALUES('1002', 1, 30, 21);
INSERT #TransactionTable VALUES('1002', 8, 30, 22);
SELECT iTVF.NetQty,
NetPrice = iTVF.NetQty * Rate
FROM #TransactionTable T
CROSS
APPLY (SELECT SellQty - BuyQty) iTVF (NetQty);
-- OR
-- Computed column (persisted and/or indexed as required)
ALTER TABLE #TransactionTable
ADD
NetQty AS SellQty - BuyQty
PERSISTED NOT NULL;
SELECT T.NetQty,
NetPrice = NetQty * Rate
FROM #TransactionTable T;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 3:59 am
Gianluca Sartori (6/25/2010)
virender.singh (6/25/2010)
derived/nested query is ok. but we will have a performance issue with this.What kind of performance problem? The way of accessing the table suggested by Kingston isn't any different from accessing the table directly.
I agree with Gianluca. I don't think there will be any performance impact as the table will be accessed only once and not twice as you might be thinking.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 25, 2010 at 7:56 am
thank you paul
🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply