I have a query and it returns the following table:
I would like to know if you can add another line (in this case 3) to make the difference between the two years.
I wanted a new line that would give the difference of the two values of that warehouse, which in this case, in the X98 was -721.86. But I'm not sure how to do it: s
The query I have is the following:
SELECT
Armazem
,DatePart(YEAR, Data) as DataAno
,SUM(ISNULL(ADITIVADA,0)) as ADITIVADA
,SUM(ISNULL(X98,0)) as X98
,SUM(ISNULL(X95,0)) as X95
,SUM(ISNULL(XGAS,0)) as XGAS
,SUM(ISNULL(XGPL,0)) as XGPL
,SUM(ISNULL(XAGR,0)) as XAGR
,SUM(ISNULL(MISTURA,0)) as MISTURA
,SUM(ISNULL(XAQ,0)) as XAQ
,SUM(ISNULL(ADIESEL,0)) as ADIESEL
,SUM(ISNULL(ADBLUE,0)) as ADBLUE
,SUM(ISNULL(O95,0)) as O95
,SUM(ISNULL(O98,0)) as O98
FROM(
SELECT
TARMA as Armazem
,DataTotal AS Data
,CASE WHEN TCMB = 1 THEN ISNULL(SUM(TQTD),0) END as 'ADITIVADA'
,CASE WHEN TCMB = 2 THEN ISNULL(SUM(TQTD),0) END as 'X98'
,CASE WHEN TCMB = 3 THEN ISNULL(SUM(TQTD),0) END as 'X95'
,CASE WHEN TCMB = 4 THEN ISNULL(SUM(TQTD),0) END as 'XGAS'
,CASE WHEN TCMB = 5 THEN ISNULL(SUM(TQTD),0) END as 'XGPL'
,CASE WHEN TCMB = 6 THEN ISNULL(SUM(TQTD),0) END as 'XAGR'
,CASE WHEN TCMB = 7 THEN ISNULL(SUM(TQTD),0) END as 'MISTURA'
,CASE WHEN TCMB = 9 THEN ISNULL(SUM(TQTD),0) END as 'XAQ'
,CASE WHEN TCMB = 10 THEN ISNULL(SUM(TQTD),0) END as 'ADIESEL'
,CASE WHEN TCMB = 11 THEN ISNULL(SUM(TQTD),0) END as 'ADBLUE'
,CASE WHEN TCMB = 12 THEN ISNULL(SUM(TQTD),0) END as 'O95'
,CASE WHEN TCMB = 13 THEN ISNULL(SUM(TQTD),0) END as 'O98'
FROM
(
Select
VendasPOS_Cabecalhos.Armazem as TARMA
,ISNULL(SUM(vendaspos_linhas.QTD),0) as TQTD
,VendasPOS_Cabecalhos.FACT_VD as TFactVD
,vendasPOS_Cabecalhos.DATA as DataTotal
,CB_VendasPOS.combustivel as TCMB
from CB_VendasPOS (nolock)
join vendaspos_linhas (nolock) on vendaspos_linhas.autoreg=CB_VendasPOS.autoreg
join VendasPOS_Cabecalhos (nolock) on VendasPOS_Cabecalhos.Prenumero=vendaspos_linhas.Prenumero
join VendasPOS_Turnos (nolock) on VendasPOS_Turnos.CodTurnoAuto=VendasPOS_Cabecalhos.CodTurnoAuto
where (MONTH(VendasPOS_Cabecalhos.data) >= MONTH('2015-09-01 00:00:00') AND MONTH(VendasPOS_Cabecalhos.data) <= MONTH('2015-09-01 00:00:00'))
and (YEAR(VendasPOS_Cabecalhos.data) >= YEAR('2014-09-01 00:00:00') AND YEAR(VendasPOS_Cabecalhos.data) <= YEAR('2015-09-01 00:00:00'))
and VendasPOS_Turnos.armazem='454'
and FACT_VD NOT IN ('A', 'I', 'G', 'M')
group by
vendasPOS_Cabecalhos.DATA
,VendasPOS_Cabecalhos.Armazem
,VendasPOS_Cabecalhos.FACT_VD
,vendaspos_linhas.QTD
,CB_VendasPOS.combustivel
)Res
GROUP BY
DataTotal,TARMA,TFactVD,TCMB
)ResF
Group BY
Armazem, DatePart(YEAR, Data)
ORDER BY
Armazem, DatePart(YEAR, Data) ASC