User defined function based on debit and credit side of columns

MA 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 )

124691-image.png

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. Viorel 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
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 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.

    0 comments No comments