Add a line at the end of the query in MS-SQL

-4

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
    
asked by anonymous 27.10.2015 / 17:08

2 answers

0

Yes, I already use this, see an example: Case: I have 2 employees and I need their salary added and presented as you described:

SELECT CODIGO, SALARIO_TRABALHO FROM CLIENTES WHERE CODIGO in (55685, 447878)
UNION ALL
SELECT Null, SUM(SALARIO_TRABALHO) FROM CLIENTES WHERE CODIGO in (55685, 447878)

Now just adapt your SQL

    
27.10.2015 / 17:30
0

Unfortunately the query below will only work in SQL 2005 or higher.

It works for multiple stores, and will compare the first year in history as the most recent.

WITH CTE_Res AS (
    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
), CTE_ResF AS (
    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 CTE_Res as Res
    GROUP BY
        DataTotal,
        TARMA,
        TFactVD,
        TCMB
), CTE_Estoque_Group AS (
    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 CTE_ResF as ResF
    Group BY
        Armazem, 
        DatePart(YEAR, Data) 
), CTE_Estoque AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY Armazem ORDER BY DataAno) as OrdemAsc,
        ROW_NUMBER() OVER (PARTITION BY Armazem ORDER BY DataAno Desc) as OrdemDesc,
        Armazem,
        DataAno,
        ADITIVADA,
        X98,
        X95,
        XGAS,
        XGPL,
        XAGR,
        MISTURA,
        XAQ,
        ADIESEL,
        ADBLUE,
        O95,
        O98
    FROM CTE_Estoque_Aux as ResF
), CTE_Estoque_Com_Footer AS (
    SELECT 
        Estoque.Armazem,
        Estoque.DataAno,
        Estoque.ADITIVADA,
        Estoque.X98,
        Estoque.X95,
        Estoque.XGAS,
        Estoque.XGPL,
        Estoque.XAGR,
        Estoque.MISTURA,
        Estoque.XAQ,
        Estoque.ADIESEL,
        Estoque.ADBLUE,
        Estoque.O95,
        Estoque.O98
    FROM CTE_Estoque AS Estoque

    UNION ALL

    SELECT 
        EstoqueAnterior.Armazem as Armazem,
        null as DataAno,
        EstoqueAnterior.ADITIVADA - EstoqueAtual.ADITIVADA AS ADITIVADA,
        EstoqueAnterior.X98 - EstoqueAtual.X98 AS X98,
        EstoqueAnterior.X95 - EstoqueAtual.X95 AS X95,
        EstoqueAnterior.XGAS - EstoqueAtual.XGAS AS XGAS,
        EstoqueAnterior.XGPL - EstoqueAtual.XGPL AS XGPL,
        EstoqueAnterior.XAGR - EstoqueAtual.XAGR AS XAGR,
        EstoqueAnterior.MISTURA - EstoqueAtual.MISTURA AS MISTURA,
        EstoqueAnterior.XAQ - EstoqueAtual.XAQ AS XAQ,
        EstoqueAnterior.ADIESEL - EstoqueAtual.ADIESEL AS ADIESEL,
        EstoqueAnterior.ADBLUE - EstoqueAtual.ADBLUE AS ADBLUE,
        EstoqueAnterior.O95 - EstoqueAtual.O95 AS O95,
        EstoqueAnterior.O98 - EstoqueAtual.O98 AS O98   
    FROM CTE_Estoque AS EstoqueAnterior
    JOIN CTE_Estoque AS EstoqueAtual ON EstoqueAnterior.Armazem = EstoqueAtual.Armazem AND EstoqueAnterior.OrdemAsc = EstoqueAtual.OrdemDesc
    WHERE EstoqueAnterior.OrdemAsc = 1
);

SELECT * FROM CTE_Estoque_Com_Footer
ORDER BY Armazem, DataAno * -1 DESC -- hack para colocar a data NULL no final.

I used CTE to help organize your query, so much so that the first three CTEs are a transcript of your query, if you perform the query below you will get the same result from your original query:

SELECT * FROM CTE_Estoque_Group 

CTE CTE_Estoque is used to add a column with ordem ascendente (OrdemAsc) and another with ordem descendente (OrdemDesc) referring to Column Armazem in relation to column DataAno . This way I can identify the first year ( OrdemAsc = 1 ) and the last year ( OrdemDesc = 1 ) and cross these two information

The CTE CTE_Estoque_Com_Footer serves to display your query and adds additional lines with the difference between the first year and the last year of each store (if you have more than one).

P.S:

I would have used a PIVOT instead of these CASE s with GROUP BY using in query CTE_ResF and CTE_Estoque_Group

    
27.10.2015 / 18:28