User defined function based on debit and credit side of columns

Question

Hi: Would it be possible to create a user defined function in sql based on the following criteria? Thanking you in advance for any assistance.

1st Account column: The logic is as follows

= IF(
OR((LEFT($D3,1)*1)=5,(LEFT($D3,1)*1)=4),
(LEFT(TRIM($D3),6)*1),
(LEFT(TRIM($A3),6)*1) )

2nd Cat_1 column: The logic is as follows:
= IF(
OR((LEFT($D3,1)*1)=5,(LEFT($D3,1)*1)=4),
(LEFT(TRIM($F3),7)*1),
(LEFT(TRIM($C3),7)*1) )

2nd Amount column: The logic is as follows:
= IF(
OR((LEFT($D3,1)*1)=5,(LEFT($D3,1)1)=4),
($G3-1),
$G3 )

Answer 1

If you need functions, I think that you can declare three functions. For example:

create or alter function dbo.CalculateAccount
(
    @Account varchar(max),
    @Off_Accnt varchar(max)
)
returns int
begin
    return case when @Off_Accnt like '[45]%' then left(@Off_Accnt, 6) else left(@Account, 6) end
end

go

create or alter function dbo.CalculateCat_1
(
    @Cat_1 varchar(max),
    @Off_Accnt varchar(max),
    @Off_Cat_1 varchar(max)
)
returns int
begin
    return case when @Off_Accnt like '[45]%' then left(@Off_Cat_1, 7) else left(@Cat_1, 7) end
end

go

create or alter function dbo.CalculateAmount
(
    @Off_Accnt varchar(max),
    @Amount money
)
returns money
begin
    return case when @Off_Accnt like '[45]%' then -@Amount else @Amount end
end

go

Usage:

select 
    dbo.CalculateAccount( Account, Off_Accnt),
    dbo.CalculateCat_1( Cat_1, Off_Accnt, Off_Cat_1),
    dbo.CalculateAmount( Off_Accnt, Amount)
from MyTable

Answer 2

I think that it is possible. Try something like this:

select
   case when Off_Accnt like '[45]%' then left(Off_Accnt, 6) else left(Account, 6) end as Account,
   case when Off_Accnt like '[45]%' then left(Off_Cat_1, 7) else left(Cat_1, 7) end as Cat_1,
   case when Off_Accnt like '[45]%' then -Amount else Amount end as Amount
from MyTable

Try it with your real table and show details if it does not work.