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:
x
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?
Advertisement
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