Calculate user formula from from account number

Question

I have two tables, the first is dbo.formula

Id | Poste  | Calculation | Montant
---+--------+-------------+--------
 1 | Achats | 3801%+381%  | .....
 2 | Tva    | 446%-445%   | ....
 3 | Tiers  | 411%+401%   | ....

The second table is dbo.value

Compte  | Solde
--------+-------
 380000 | 400.00
 380100 | 500.00
 381100 | 200.00
 381200 | 100.00
4456600 | 100.00
4455400 | 150.00

I need to be the result in the first table in field Montant like

 1 | Achats | 3801%+381%   |..... = 500.00 + 200.00 + 100.00 = 800.00
 2 | Tva    | 446%-445%   |.... = 100.00 - 150.00 = -50.00

I tried a request but it works only the 3 beginning numbers of account, and it works with + only.

select f.ID, f.Poste, sum(v.Solde) total
from formula f
inner join value v
    on charindex('+' + left(v.Compte, 3) + '%', '+' + f.Calculation) > 0
group by f.ID, f.Poste

I want a request to work from 1 to 8 digits of number account, and also work with +, -, *, /.

Why is the total for `Tva` -50 instead of -250? Is there a typo on the `dbo.value.Compte` value of `4456600` and it should instead begin with `446`? Otherwise, it looks like both the 100 and 150 should be negative in this instance and so the total for `Tva` should be -250? — 3N1GM4, Jan 23 '20 at 11:22
@Mohammed FETTAH . . . I would want more information before even trying to solve this. Explain how the two tables are linked. Do you want the result as a number or a string? Why is `%` being used this way, when it has meaning in financial formulas? — Gordon Linoff, Jan 23 '20 at 11:42
I make a mistake in compte number is 446600 for 100.00 in dbo.value, for % is all number after like in statement select from where 445% — Mohammed FETTAH, Jan 23 '20 at 19:41

score 1 · Answer 1 · answered Jan 23 '20 at 11:44

Personally, I'd start by creating a new table to store your calculation logic in a more structured manner instead of a big varchar:

create table calcs
(
    FormulaId int,
    operator varchar(1),
    pattern varchar(10)
)

insert into calcs
    select
        Id as FormulaId,
        case when left([value],1) like '[^0-9]' then left([value],1) else '+' end as operator,
        case when left([value],1) like '[^0-9]' then right([value],len([value])-1) else [value] end + '%' as pattern
    from formula
    cross apply string_split(Calculation, '%')
    where [value] <> ''

select * from calcs

Results:
/--------------------------------\
| FormulaId | operator | pattern |
|-----------|----------|---------|
|     1     |     +    | 3801%   |
|     1     |     +    | 381%    |
|     2     |     +    | 446%    |
|     2     |     -    | 445%    |
|     3     |     +    | 411%    |
|     3     |     +    | 401%    |
\--------------------------------/

Now, you can use this table to assist in building the complete formula string you want:

select
    f.Id,
    f.Poste,
    f.Calculation,
    (
        SELECT c.operator + convert(varchar(20),v.Solde)
        from calcs c
        join [value] v on v.Compte like c.pattern
        where c.FormulaId = f.Id
        for xml path('')
    )
    + ' = ' + convert(varchar(20),
                  sum(case when c2.operator = '+' then v2.Solde else 0 end) 
                - sum(case when c2.operator = '-' then v2.Solde else 0 end)) as Montant
from formula f
left join calcs c2 on c2.FormulaId = f.Id
left join [value] v2 on v2.Compte like c2.pattern
group by f.Id, f.Poste, f.Calculation

Results:
/------------------------------------------------------------\
| Id | Poste  | Calculation | Montant                        |
|----|--------|-------------|--------------------------------|
| 1  | Achats | 3801%+381%  | +500.00+200.00+100.00 = 800.00 |
| 2  | Tva    | 446%-445%   | -100.00-150.00 = -250.00       |
| 3  | Tiers  | 411%+401%   | NULL                           |
\------------------------------------------------------------/

If you wanted to tidy up the leading + symbols where the first value is positive, you could wrap the whole thing in an additional SELECT, or use a CTE:

with newFormula (Id, Poste, Calculation, Montant) as
(
    select
        f.Id,
        f.Poste,
        f.Calculation,
        (
            SELECT c.operator + convert(varchar(20),v.Solde)
            from calcs c
            join [value] v on v.Compte like c.pattern
            where c.FormulaId = f.Id
            for xml path('')
        )
        + ' = ' + convert(varchar(20),
                      sum(case when c2.operator = '+' then v2.Solde else 0 end) 
                    - sum(case when c2.operator = '-' then v2.Solde else 0 end)) as Montant
    from formula f
    left join calcs c2 on c2.FormulaId = f.Id
    left join [value] v2 on v2.Compte like c2.pattern
    group by f.Id, f.Poste, f.Calculation
)
select
    Id,
    Poste,
    Calculation,
    case when left(Montant,1) = '+'
        then right(Montant,len(Montant)-1)
        else Montant
    end as Montant
from newFormula

Results:
/------------------------------------------------------------\
| Id | Poste  | Calculation | Montant                        |
|----|--------|-------------|--------------------------------|
| 1  | Achats | 3801%+381%  | 500.00+200.00+100.00 = 800.00  |
| 2  | Tva    | 446%-445%   | -100.00-150.00 = -250.00       |
| 3  | Tiers  | 411%+401%   | NULL                           |
\------------------------------------------------------------/

thank you, it work but i need only the result not all in field Montant, for exemple i need 800.00 only — Mohammed FETTAH, Jan 23 '20 at 20:44

Calculate user formula from from account number

1 Answers1