How to get the cumulative total value of a month in SQL

Question

How to get the cumulative total value of a month in SQL

Navigation

#1 by (0 votes)

0

I have the following question with SQL: I want to take out the maximum month of purchase of a certain article.

With this consultation, the purchase of the item accumulated in THE LAST 12 MONTHS:

SELECT ARTICULO.ARTICULO, ARTICULO.DESCRIPCION,
       sum(LINEA.cantidad * LINEA.multiplicador_ev) AS MexMAX
FROM factura_linea LINEA (NOLOCK)
     INNER JOIN LICASA.factura FACTURA (NOLOCK) ON LINEA.factura = FACTURA.factura
     INNER JOIN licasa.ARTICULO ON LINEA.articulo = ARTICULO.articulo
WHERE FACTURA.FECHA BETWEEN FORMAT((Dateadd(month,-12,GETDATE())),'yyyy-MM-01 00:00:00.000')
                        AND FORMAT(EOMONTH(Dateadd(month,-1,GETDATE())),'yyyy-MM-dd 23:59:59.999')
  AND LINEA.ANULADA='N' and ARTICULO.ARTICULO ='TR900'
GROUP BY ARTICULO.ARTICULO, ARTICULO.DESCRIPCION, month(factura.FECHA)

This is the result:

What I want is another consultation, or if it can be done in another way, that I take these values and take out the maximum accumulated purchase in the last 12 months.

Thanks in advance! Greetings

sql sql-server

asked by Marcos Chavez 21.02.2018 в 16:56

source

1 answer

Bootstrap-Select needs double click to show options How can I make a SUBMIT to the GravityForms plugin?

score 0 · Answer 1

You can use the Max () function to get the highest value of a column:

    SELECT MAX(MexMAX) AS masVecesVendido 
    FROM (SELECT ARTICULO.ARTICULO, ARTICULO.DESCRIPCION,
           sum(LINEA.cantidad * LINEA.multiplicador_ev) AS MexMAX
    FROM factura_linea LINEA (NOLOCK)
         INNER JOIN LICASA.factura FACTURA (NOLOCK) ON LINEA.factura = FACTURA.factura
         INNER JOIN licasa.ARTICULO ON LINEA.articulo = ARTICULO.articulo
    WHERE FACTURA.FECHA BETWEEN FORMAT((Dateadd(month,-12,GETDATE())),'yyyy-MM-01 00:00:00.000')
                            AND FORMAT(EOMONTH(Dateadd(month,-1,GETDATE())),'yyyy-MM-dd 23:59:59.999')
      AND LINEA.ANULADA='N' and ARTICULO.ARTICULO ='TR900'
    GROUP BY ARTICULO.ARTICULO, ARTICULO.DESCRIPCION, month(factura.FECHA)) resMeses