Calculations - 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

General SQL Server Forums

New to SQL Server Programming

Calculations

Author

Topic

Les.61
Starting Member

49 Posts

Posted - 2009-12-15 : 19:48:49

I am trying to create a view where the amount is changed to a negative if that transaction has a certain code. At the moment I can create a view and then do a 2nd query to get the amounts changed to positive and negative. Is there a way to do this in one go.

My code so far is

use Reporting
go
alter view AcctransCrDr
as
select a.*, t.balance,
trantype = case
when Transcode like '5%' then '-1'
when Transcode like '6%' then '-1'
when Transcode like '7%' then '-1'
when Transcode like 'cc' then '-1'
when Transcode like 'tf' then '-1'
when Transcode like 'dt' then '-1'
when Transcode like '80' then '-1'
when Transcode like '81' then '-1'
when Transcode like '82' then '-1'
when Transcode like '83' then '-1'
when Transcode like '84' then '-1'
when Transcode like '85' then '-1'
when Transcode like '86' then '-1'
when Transcode like '1%' then '1'
when Transcode like '2%' then '1'
when Transcode like '2%' then '1'
when Transcode like '3%' then '1'
when Transcode like '4%' then '1'
when Transcode like '87' then '1'
when Transcode like 'rc' then '1'
when Transcode like 'ct' then '1'
end
from dbo.AccTran as a
inner join tranaccount as t
on a.id = t.id
I then use a query with (trantype * tranamount) as TRANDRCR to get the positive and negative figures.

Les.61
Starting Member

49 Posts

Posted - 2009-12-16 : 00:28:23

Found a solution

when Transcode like '5%' then transamount*-1
etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources