User defined function based on debit and credit side of columns

101 Reputation points
2021-08-19T10:08:57.377+00:00

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 )

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,560 questions
{count} votes

Accepted answer
1. 112.9K Reputation points
2021-08-19T13:40:23.657+00:00

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

1 additional answer

1. 112.9K Reputation points
2021-08-19T12:05:43.64+00:00

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.