SQL Select Adding a new row with difference between numbers

I have this query that give the sum between years but I want to add a new row at the end of each TARMA that give the differences between the years.

Here is the query:

 Select  
     VPC.Armazem as TARMA
    ,YEAR(VPC.DATA) as DataTotal
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 1 THEN VPL.QTD ELSE 0 END) as ADITIVADA
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 2 THEN VPL.QTD ELSE 0 END) as X98
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 3 THEN VPL.QTD ELSE 0 END)as X95
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 4 THEN VPL.QTD ELSE 0 END) as XGAS
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 5 THEN VPL.QTD ELSE 0 END) as XGPL
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 6 THEN VPL.QTD ELSE 0 END) as XAGR
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 7 THEN VPL.QTD ELSE 0 END) as MISTURA
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 9 THEN VPL.QTD ELSE 0 END) as XAQ
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 10 THEN VPL.QTD ELSE 0 END) as ADIESEL
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 11 THEN VPL.QTD ELSE 0 END) as ADBLUE
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 12 THEN VPL.QTD ELSE 0 END) as O95
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 13 THEN VPL.QTD ELSE 0 END) as O98

WHERE 
    (MONTH(VPC.DATA) >= MONTH('2015-09-01') AND MONTH(VPC.DATA) <= MONTH('2015-09-30'))
    and (YEAR(VPC.DATA) >= YEAR('2014-09-01') AND YEAR(VPC.DATA) <= YEAR('2015-09-30'))
    and VPT.armazem IN ('454','457')
    and FACT_VD NOT IN ('A', 'I', 'G', 'M')
GROUP BY
    YEAR(VPC.DATA)
    ,VPC.Armazem
ORDER BY
    VPC.Armazem
    ,YEAR(VPC.DATA)

And here is the result without the difference: [Result][1]

For example:

TARMA: 454 for X98

2014: 1849.14077

2015: 2571.47750

Difference: -722,33673

I’m using MS SQL. Is it with a UNION?

How can I get the difference?

Answer

I got it:

;with dados as (

    Select  
     VPC.Armazem as TARMA
    ,YEAR(VPC.DATA) as DataTotal1
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 1 THEN VPL.QTD ELSE 0 END) as SomaADITIVADA
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 2 THEN VPL.QTD ELSE 0 END) as SomaX98
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 3 THEN VPL.QTD ELSE 0 END)as SomaX95
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 4 THEN VPL.QTD ELSE 0 END) as SomaXGAS
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 5 THEN VPL.QTD ELSE 0 END) as SomaXGPL
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 6 THEN VPL.QTD ELSE 0 END) as SomaXAGR
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 7 THEN VPL.QTD ELSE 0 END) as SomaMISTURA
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 9 THEN VPL.QTD ELSE 0 END) as SomaXAQ
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 10 THEN VPL.QTD ELSE 0 END) as SomaADIESEL
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 11 THEN VPL.QTD ELSE 0 END) as SomaADBLUE
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 12 THEN VPL.QTD ELSE 0 END) as SomaO95
    ,SUM(CASE WHEN VP.COMBUSTIVEL = 13 THEN VPL.QTD ELSE 0 END) as SomaO98
    ,row_number() over (partition by VPC.Armazem order by YEAR(VPC.DATA) ASC) as NAno

WHERE 
    (MONTH(VPC.DATA) >= MONTH('2015-09-01') AND MONTH(VPC.DATA) <= MONTH('2015-09-30'))
    and (YEAR(VPC.DATA) >= YEAR('2014-09-01') AND YEAR(VPC.DATA) <= YEAR('2015-09-30'))
    and VPT.armazem IN ('454','457')
    and FACT_VD NOT IN ('A', 'I', 'G', 'M')
GROUP BY
    YEAR(VPC.DATA)
    ,VPC.Armazem

)

SELECT
      anosDetalhados.TARMA as TARMA
     ,anosDetalhados.DataTotal1 as DataTotal
     ,SUM(anosDetalhados.SomaADITIVADA) as ADITIVADA 
     ,SUM(anosDetalhados.SomaX98) as X98
     ,SUM(anosDetalhados.SomaX95) as X95
     ,SUM(anosDetalhados.SomaXGAS) as XGAS
     ,SUM(anosDetalhados.SomaXGPL) as XGPL
     ,SUM(anosDetalhados.SomaXAGR) as XAGR
     ,SUM(anosDetalhados.SomaMISTURA) as MISTURA
     ,SUM(anosDetalhados.SomaXAQ) as XAQ
     ,SUM(anosDetalhados.SomaADIESEL) as ADIESEL
     ,SUM(anosDetalhados.SomaADBLUE) as ADBLUE
     ,SUM(anosDetalhados.SomaO95) as O95
     ,SUM(anosDetalhados.SomaO98) as O98
FROM dados as anosDetalhados (nolock)
GROUP BY
    anosDetalhados.DataTotal1
    ,anosDetalhados.TARMA

UNION ALL

SELECT
     ano1.TARMA as TARMA
    ,NULL  as DataTotal
    ,SUM(coalesce(ano1.SomaADITIVADA-ano2.SomaADITIVADA, 0)) as ADITIVADA 
    ,SUM(coalesce(ano1.SomaX98-ano2.SomaX98, 0)) as X98
    ,SUM(coalesce(ano1.SomaX95-ano2.SomaX95, 0)) as X95
    ,SUM(coalesce(ano1.SomaXGAS-ano2.SomaXGAS, 0)) as XGAS
    ,SUM(coalesce(ano1.SomaXGPL-ano2.SomaXGPL, 0)) as XGPL
    ,SUM(coalesce(ano1.SomaXAGR-ano2.SomaXAGR, 0)) as XAGR
    ,SUM(coalesce(ano1.SomaMISTURA-ano2.SomaMISTURA, 0)) as MISTURA
    ,SUM(coalesce(ano1.SomaXAQ-ano2.SomaXAQ, 0)) as XAQ
    ,SUM(coalesce(ano1.SomaADIESEL-ano2.SomaADIESEL, 0)) as ADIESEL
    ,SUM(coalesce(ano1.SomaADBLUE-ano2.SomaADBLUE, 0)) as ADBLUE
    ,SUM(coalesce(ano1.SomaO95-ano2.SomaO95, 0)) as O95
    ,SUM(coalesce(ano1.SomaO98-ano2.SomaO98, 0)) as O98
FROM dados as ano1 (nolock)
LEFT JOIN dados as ano2 on ano1.TARMA=ano2.TARMA and ano1.NAno > ano2.NAno
GROUP BY
    ano1.TARMA
ORDER BY
    TARMA
   ,anosDetalhados.DataTotal1 ASC