Command in SQL already with correct calculation

Question

We have a differential in the payment of ICMS and I would like to export a spreadsheet which I have already created the command, also create what amount we will pay from ICMS.

In case it would be the following.:

Where we highlight 10% ICMS, we pay only 4% effectively.
Where we highlight 4% of the ICMS, we pay effectively 1.4%

The Current Command:

SELECT DISTINCT nf.codloja, 
                nf.dtemissao, 
                nf.numnota, 
                ni.icms, 
                Sum(ni.subtoticms) AS toticms 
FROM   notafiscal nf 
       JOIN nfitens ni 
         ON ni.codnf = nf.codnf 
WHERE  dtemissao BETWEEN '06/01/2019' AND '06/30/2019' 
       AND COALESCE(nf.anulada, 0) = 0 
       AND COALESCE(nf.cancelada, 0) = 0 
       AND codloja = 2 
       AND COALESCE(nf.protocolonfe, '') <> '' 
GROUP  BY 1, 
          2, 
          3, 
          4 

Answer 1

Assuming that in your tables you do not have the note value for recalculation of the ICMS, and therefore you need to recalculate from the ICMS value you have, and that the icms column contains a string with the percentage, try:

SELECT DISTINCT nf.codloja, 
                nf.dtemissao, 
                nf.numnota, 
                ni.icms, 
                Sum(CASE WHEN ni.icms = '4%' THEN (ni.subtoticms * 0.014) / 0.04
                         WHEN ni.icms = '10%' THEN (ni.subtoticms * 0.04) / 0.1
                         ELSE ni.subtoticms
                    END) AS toticms 
FROM   notafiscal nf 
       JOIN nfitens ni 
         ON ni.codnf = nf.codnf 
WHERE  dtemissao BETWEEN '06/01/2019' AND '06/30/2019' 
       AND nf.anulada IS NOT NULL 
       AND nf.cancelada IS NOT NULL 
       AND codloja = 2 
       AND nf.protocolonfe <> '' 
GROUP  BY 1, 
          2, 
          3, 
          4 

If not, explain how your data is organized.