Click here to Skip to main content
15,031,495 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want subtract buyamount from contractno 001 with saleamount from contractno 002 to get amount revenue

Below is my data

REFNO | DEALMASTERKEY | CONTRACTNO | BUYAMOUNT | SALEAMOUNT | RATE | REVENUE
==========================================================
2012110 | 123 | 001 | 1,500,000 | 1,000,000 | 1.5 |
2012110 | 456 | 002 | 1,000,000 | 1,700,000 | 1.7 |

What I have tried:

SQL
SELECT 
dd.REFNO, dd.DEALMASTERKEY, dd.CONTRACTNO, dd.BUYAMOUNT, dd.SALEAMOUNT, dd.RATE
,REVENUE = (SELECT ABS(dl.BUYAMOUNT) from DF_DEALMASTER dl where dl.CONTRACTNO = '001' and dl.DEALMASTERKEY = dd.DEALMASTERKEY) - 
(SELECT ABS(dl.SALEAMOUNT) from DF_DEALMASTER dl where dl.CONTRACTNO = '002' and dl.DEALMASTERKEY = dd.DEALMASTERKEY)

FROM DF_DEALMASTER dd
Posted
Updated 3-Mar-20 15:34pm
v6
Comments
Richard Deeming 3-Mar-20 11:45am
   
The data you've shown has no connection between the two rows.

The query you've tried references columns which aren't in the sample data.

There's no suggestion of how you're identifying which row to subtract from which other row.
Amira Najihah 3-Mar-20 21:22pm
   
contractno 001 is first row
contractno 002 is second row

The query I had tried to get amount revenue.
Revenue = (dl.buyamount where contractno = 001) - (saleamount where contractno = 002)

revenue = 1,500,00 - 1,700,000 (This is how I try to subtract)
Amira Najihah 3-Mar-20 21:32pm
   
Sorry I forgot to put reference no that tie two rows

See the comments from @RichardDeeming. Your first set of errors are
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'KEY'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FROM'.
The first one is caused because KEY is a keyword. When using keywords as column names (avoid if possible) surround the column name in square brackets i.e. [KEY]

After sorting that out you get several errors like
Msg 207, Level 16, State 1, Line 8
Invalid column name 'DEALMASTERKEY'.
- You have not shared the full table schema with us. If I tidy that up I get
SQL
declare @DF_DEALMASTER table ([KEY] int,CONTRACTNO varchar(3), BUYAMOUNT int, SALEAMOUNT int, RATE decimal(15,2))
insert into @DF_DEALMASTER ([KEY],CONTRACTNO, BUYAMOUNT, SALEAMOUNT, RATE) values
(123,'001',1500000, 1000000,  1.5 ),
(456,'002',1000000, 1700000,  1.7 )

SELECT 
dd.[KEY], dd.CONTRACTNO, dd.BUYAMOUNT, dd.SALEAMOUNT, dd.RATE
,REVENUE = (SELECT ABS(dl.BUYAMOUNT) from @DF_DEALMASTER dl where dl.CONTRACTNO = '001') - 
(SELECT ABS(dl.SALEAMOUNT) from @DF_DEALMASTER dl where dl.CONTRACTNO = '002' )

FROM @DF_DEALMASTER dd 
And that returns the results
KEY	CONTRACTNO	BUYAMOUNT	SALEAMOUNT	RATE	REVENUE
123	001	1500000	1000000	1.50	-200000
456	002	1000000	1700000	1.70	-200000
which looks to be correct.

Of course this will only work if you always have contracts '001' and '002' for any given value of DEALMASTERKEY

What I suspect you really want is to subtract values from the previous row - in which case you can use the LAG and LEAD functions of SQL Server ... SQL Server Window Functions LEAD and LAG[^]
   
Comments
Amira Najihah 3-Mar-20 21:39pm
   
declare @DF_DEALMASTER table ([KEY] int,CONTRACTNO varchar(3), BUYAMOUNT int, SALEAMOUNT int, RATE decimal(15,2))
insert into @DF_DEALMASTER ([KEY],CONTRACTNO, BUYAMOUNT, SALEAMOUNT, RATE) values
(123,'001',1500000, 1000000, 1.5 ),
(456,'002',1000000, 1700000, 1.7 )

SELECT
dd.[KEY], dd.CONTRACTNO, dd.BUYAMOUNT, dd.SALEAMOUNT, dd.RATE
,REVENUE = (SELECT ABS(dl.BUYAMOUNT) from @DF_DEALMASTER dl where dl.CONTRACTNO = '001') -
(SELECT ABS(dl.SALEAMOUNT) from @DF_DEALMASTER dl where dl.CONTRACTNO = '002' )

FROM @DF_DEALMASTER dd


When I use this sql i get error

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
CHill60 4-Mar-20 6:49am
   
When I run that query I get the correct results. You clearly have more than one contract number = '001'.
Amira Najihah 3-Mar-20 21:40pm
   
The word key I change to dealmasterkey
I think you have an answer (200,000), looking for a solution.

Perhaps you just want to SUM the BUYAMOUNT and subtract that from the "SUM of the SALESAMOUNT".

Same answer: 200,000.
   
In order to do that, you need to establish some rules, which relate data in one row to data in the other.

If there is a "sortable" field that you can arrange with ORDER BY then you can use LEAD and LAG to reference values in the next or previous row: LEAD (Transact-SQL) - SQL Server | Microsoft Docs[^] LAG (Transact-SQL) - SQL Server | Microsoft Docs[^]

If there is a column which can be used to "tie" two related rows together, you can texh "matching" data using JOIN: SQL Joins[^]

But it's not at all obvious from the trivial data sample you show what could establish the critical relationship - and as Richard has pointed out, your code sample references columns that don't exist ...
   
Comments
Amira Najihah 3-Mar-20 21:40pm
   
Tie tow related rows are refno. I already update in the question

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900