How can I apply increase and percentage reduction over unit value

Question

The script below calculates the consumption value for materials and medicines , grouping by patient.

select
    coalesce( sum (
        (
        (L.QTDE) - coalesce(
            sum(
                (
                    select sum(QTDE)
                    from GECADDEV CD
                        inner join GELANDEV LD on CD.ID = LD.ID_GECADDEV
                    where L.ID_GELANSAI = LD.ID_GELANSAI
                        and LD.CONSOL = 'T'
                )
            ),
            0
        )
    ) *
        -- Nesse trecho que calculo acréscimo ou redução  
        (
            SELECT VALOR1
            FROM SPBRAVEN(5, L.ITEM, current_date, 1)
        ) * (1.10)),0)


from GECADSAI D
    inner join RECADATE A on D.PAC = A.REG
    inner join GELANSAI L on D.DOC = L.DOC
                            and D.ANO = L.ANO
                            and D.MES = L.MES
    inner join GEITENS I on L.ITEM = I.COD
    inner join RICADPAC CP on A.PRONT = CP.PRONT
where L.CONSOL = 'T'
    and D.CONV = e.conv
    and D.PAC = e.reg
    and char_length(D.PAC) > 6
    and A.PEXT = 'S'
    and i.classif in (2) --classificação do item sendo : 2 =  medicamento e 1 material

However, I need to apply a rule that : If the item is classified as material, I apply a reduction of -40% on the unit value.

And if it is medicine, apply an over 10% increase on the value

Anyone has any idea , I tried to use case and iff but could not

Answer 1

Considering only what is requested in the question...

Do a Case on this stretch:

(
    SELECT VALOR1
    FROM SPBRAVEN(5, L.ITEM, current_date, 1)
) * (1.10)),0)

Staying:

(
    SELECT VALOR1
    FROM SPBRAVEN(5, L.ITEM, current_date, 1)
) * (Case when i.classif = 2 then (1.10) when i.classif = 1 then (0.60) else (1) end)),0)

However, you could parameterize this factor by classifying the item, so you wouldn’t need to change the query in the future when there is a change in that rate.

Answer 2

Another alternative would be to use the IIF that nothing else is, than an if ternary, you could put something like:

(
    SELECT VALOR1
    FROM SPBRAVEN(5, L.ITEM, current_date, 1)
) * IIF(i.classif = 2, 1.10, 0.60)

that is, if the classification is = 2, multiplies by 1.10, if not, 0.60, then just adapt your need, I hope to have helped.