I need help with a query

Question

I need help with a query

Navigation

#1 by (2 votes)

4

I would like to know if anyone knows how to make the values of an example column add up:

id | Fecha      | Efectivo | TC | TD | Otro | Total | Obtenido | Resta | Acomulado

1  | 18/07/2016 | 5861     | 0  | 0  | 0    | 5861  | 650      | 5211  | 5211

3  | 20/07/2016 | 521      | 0  |0   | 0    | 521   | 0        | 521   | RES = 5211 + 521 = 5732

4  | 20/07/2016 | 20       | 0  | 0  | 0    | 20    | 10       | 10    | RES = 5732 + 10 = 5742

I do not know if you can understand the table but explained with words, from my SQL query I have those headers that are shown above and what I want to do is that in acomulated the previous row is added to the next row, Subtract column, it would be something like this: valor coluna anterior + valor columna actual = acomulado and so on.

I do not know if the query I use is of any use to them, but I also leave it:

SELECT C.iidCorte 'Folio de corte'
    ,CONVERT(VARCHAR(10), C.dfechaIn, 103) Fecha
    ,(
        SELECT ISNULL(SUM(fTotal), 0) efectivo
        FROM catVentas
        WHERE iidMetodopago = 1
            AND iidCorte = C.iidCorte
        ) Efectivo
    ,(
        SELECT ISNULL(SUM(fTotal), 0) 'Tarjeda de credito'
        FROM catVentas
        WHERE iidMetodopago = 2
            AND iidCorte = C.iidCorte
        ) 'Tarjeda de credito'
    ,(
        SELECT ISNULL(SUM(fTotal), 0) 'Tarjeda de debito'
        FROM catVentas
        WHERE iidMetodopago = 3
            AND iidCorte = C.iidCorte
        ) 'Tarjeda de debito'
    ,(
        SELECT ISNULL(SUM(fTotal), 0) 'Otro'
        FROM catVentas
        WHERE iidMetodopago = 8
            AND iidCorte = C.iidCorte
        ) 'Otro'
    ,SUM(V.fTotal) Total
    ,(SUM(V.fTotal) - SUM(V.fResta)) Obtenido
    ,SUM(V.fResta) Resta
FROM catCortes C(NOLOCK)
    ,catVentas V(NOLOCK)
    ,catMetodosPago M
WHERE C.iidCorte = V.iidCorte
    AND V.iidMetodoPago = M.iidMetodopago
    AND C.dfechaUp BETWEEN '2016-01-01T00:00:00'
        AND '2017-05-17T23:59:59'
GROUP BY C.iidCorte
    ,CONVERT(VARCHAR(10), C.dfechaIn, 103)

sql-server query

asked by Luis García 18.05.2017 в 18:21

source

1 answer

save an IP in a database Android - How to access the content of a phone that does not work?

score 2 · Accepted Answer

That I know there are two ways, I show you with an example:

DECLARE @Tabla TABLE(
    id      int,
    Monto   NUMERIC(15,2)
)

INSERT INTO @Tabla (id, Monto)
SELECT 1,310 UNION
SELECT 2,112 UNION
SELECT 3,24  UNION
SELECT 4,43  UNION
SELECT 5,55 


-- 1 por join
SELECT  t1.id, 
    t1.Monto, 
    SUM(t2.Monto) as Acumulado
    FROM @Tabla t1
    INNER JOIN @Tabla t2 ON t1.id >= t2.id
    GROUP BY t1.id, t1.Monto
    ORDER BY t1.id

-- 2 usando PARTITION a partir de SQL 2012
SELECT  t.id,
    t.Monto,
    SUM(t.Monto) OVER(PARTITION BY id ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Acumulado
    FROM @Tabla t
    ORDER BY t.id

I hope you serve