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

Asked

Viewed 1,849 times

-4

I have a query and it returns the following table:

I wonder if it is possible to add one more line (in this case to 3) to make the difference between the two years.

I wanted a new line that would give the difference between the two values of that warehouse, which in this case, on the X98 was -721.86. But I don’t quite know how to do :

The query I have is as follows::

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
sql sql-server
  • 2

    Yes it is possible.

  • It’s through the Union?

  • 1

    That depends on how you want to do it and how you have your query. You already have an answer. But you should edit your question and for more information.

  • 1

    You’re right, I’ve already edited the question

2 answers

0

Yes, I already use this, see an example: Case: I have 2 employees and need their salary summed 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

0

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

It works for multiple warehouses, and will compare the first year in the history as the latest.

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 made use of CTE to help organize your query, so much so that the first three Ctes are a transcription of your query, if you perform the query below you will get the same result of your original query:

SELECT * FROM CTE_Estoque_Group

THE CTE CTE_Estoque serves 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-reference these two pieces of 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 warehouse (if you have more than one).

P.S:

I would have used a PIVOT instead of these CASEs with the GROUP BY using in consultation CTE_ResF and CTE_Estoque_Group

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.