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
is a keyword. When using keywords as column names (avoid if possible) surround the column name in square brackets i.e.
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
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 )
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
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