How to subtract the results of a query in SQL?

Question

How to subtract the results of a query in SQL?

Navigation

#1 by (1 votes)

2

I have the following query:

SELECT ID,PLACA,SUM(GALONES) AS GALONES,SUM(MONTO) as MONTO,MONTH(FECHA1) MES,YEAR(FECHA2) AÑO
FROM [db_stransportacion].[dbo].[v_combustible_Asignado] c
INNER JOIN [db_stransportacion].[dbo].[t_Vehiculo] v ON c.ID=v.veh_id
WHERE MONTH(FECHA1) between 6 and 12 and c.ID=10 
  and YEAR(FECHA2)=2015
GROUP BY MONTH(FECHA1),YEAR(FECHA2),ID,PLACA

That the following information is returned to me:

How could I add the amount difference in the query?

That is, subtract 12,400-6,099-8,974-10,900?

sql sql-server query

asked by Jose Angel 09.03.2018 в 16:34

source

1 answer

Delete the first line of an array and store result (php) Help Json Objects browse and display DOM JavaScript

score 1 · Answer 1

Yours seems to be a problem of accumulated sum or rather of accumulated subtraction. There are several ways to solve it, I suggest you look for cumulative sum + sql server on the site in English, you will see many other alternatives. The one I propose is relatively simple to implement, the idea is to generate a numerator per row and per group using ROW_NUMBER() to then use it with a JOIN so that for example in row # 3 add the value of the amount of the row # 1 and subtract the one from rows # 2 and # 3.

First, I'm going to put together an example, I'm going to create a single table to make it simple, in your case you have two, it should not be complicated to adapt what I propose.

CREATE TABLE TablaTest (
  ID INT,
  PLACA VARCHAR(15),
  GALONES INT,
  MONTO INT,
  FECHA1 DATETIME,
  FECHA2 DATETIME
);

INSERT INTO TablaTest(ID, PLACA, GALONES, MONTO, FECHA1, FECHA2)
SELECT 10, 'EG00616', 56, 12400, '20150701', '20150701' UNION
SELECT 10, 'EG00616', 35, 6099, '20150801', '20150801' UNION
SELECT 10, 'EG00616', 5556, 8974, '20150901', '20150901' UNION
SELECT 10, 'EG00616', 2022, 10900, '20151001', '20151001' UNION
SELECT 10, 'EG00617', 56, 12400, '20150701', '20150701' UNION
SELECT 10, 'EG00617', 35, 6099, '20150801', '20150801';

As you will see:

The data from your two tables will be handled in one ( TablaTest ) to make it easier to understand.
I understand that the accumulated should be by ID and PLACA , otherwise it is not complicated to adjust this.
I added another example of a% d_co% dummy, simply to evaluate that it works properly

The query:

;WITH CTE AS(
    SELECT rn = ROW_NUMBER() OVER(PARTITION BY ID, PLACA ORDER BY ID, PLACA, YEAR(FECHA2), MONTH(FECHA1)
    ), 
    ID,
    PLACA,
    SUM(GALONES) AS GALONES,
    SUM(MONTO) AS MONTO,
    MONTH(FECHA1) AS MES,
    YEAR(FECHA2) AS AÑO  
    FROM TablaTest
    WHERE MONTH(FECHA1) BETWEEN 6 AND 12 
             AND ID=10 
             AND YEAR(FECHA2)=2015
    GROUP BY MONTH(FECHA1),
             YEAR(FECHA2),
             ID,
             PLACA
)
SELECT  T1.ID,
        T1.PLACA,
        T1.GALONES,
        T1.MONTO,
        T1.MES,
        T1.AÑO,
        SUM(t2.MONTO*(CASE WHEN T2.rn>1 THEN -1 ELSE 1 END)) AS ACUMULADO
        FROM cte T1
        LEFT JOIN cte T2
            ON T1.rn >= T2.rn
            AND T1.ID = T2.ID
            AND T1.PLACA = T2.PLACA
        GROUP BY T1.rn,
                 T1.ID,
                 T1.PLACA,
                 T1.GALONES,
                 T1.MONTO,
                 T1.MES,
                 T1.AÑO;

The exit:

╔════╦═════════╦═════════╦═══════╦═════╦══════╦═══════════╗
║ ID ║ PLACA   ║ GALONES ║ MONTO ║ MES ║ AÑO  ║ ACUMULADO ║
╠════╬═════════╬═════════╬═══════╬═════╬══════╬═══════════╣
║ 10 ║ EG00616 ║ 56      ║ 12400 ║ 7   ║ 2015 ║ 12400     ║
╠════╬═════════╬═════════╬═══════╬═════╬══════╬═══════════╣
║ 10 ║ EG00616 ║ 35      ║ 6099  ║ 8   ║ 2015 ║ 6301      ║
╠════╬═════════╬═════════╬═══════╬═════╬══════╬═══════════╣
║ 10 ║ EG00616 ║ 5556    ║ 8974  ║ 9   ║ 2015 ║ -2673     ║
╠════╬═════════╬═════════╬═══════╬═════╬══════╬═══════════╣
║ 10 ║ EG00616 ║ 2022    ║ 10900 ║ 10  ║ 2015 ║ -13573    ║
╠════╬═════════╬═════════╬═══════╬═════╬══════╬═══════════╣
║ 10 ║ EG00617 ║ 56      ║ 12400 ║ 7   ║ 2015 ║ 12400     ║
╠════╬═════════╬═════════╬═══════╬═════╬══════╬═══════════╣
║ 10 ║ EG00617 ║ 35      ║ 6099  ║ 8   ║ 2015 ║ 6301      ║
╚════╩═════════╩═════════╩═══════╩═════╩══════╩═══════════╝

See SQLfiddle

First we created a CTE with our main query and generating an additional column with the row number by PLACA and ID ( PLACA )
Then we simply use the CTE recursively to do what I mentioned above.