none
SUMATORIA ACUMULADA RRS feed

  • Pregunta

  • Estoy intenado la siguiente consulta con mis tablas de Entradas y Salidas, mi idea es ir sumando las cantidades para tener un acumulado por cada codigo de barras , para que asi se refleje cuanto va quedando despues de cada movimiento ya sea una salida o entrada , EJEMPLO EN EL COD 12345 SERIA 100 -3 = 97 - 2 = 95 Y ASI CON CADA CODIGO, de antemano gracias y cualquier ayuda es bien recibida 


    domingo, 17 de febrero de 2019 0:44

Todas las respuestas

  • Hola El_Vena:

    Es importante poner como son tus tablas para saber realmente lo que quieres, como se puede implementar.

    De tu texto, deduzco que este es el escenario planteado:

    create table entradas (codigo int, cantidad int, fecha datetime)
    go
    create table salidas (codigo int, cantidad int, fecha datetime)
    go
    insert into entradas (codigo, cantidad, fecha)
    values
    (1,1,'20190210 08:59'),
    (12345,100,'20190101 08:00'),
    (12345,25,'20190115 23:59'),
    (21,1,'20190210 08:59'),
    (12345,15,'20190201 12:00'),
    (21,1,'20190210'),
    (1,1,'20190210'),
    (12345,100,'20190215 15:00')
    go
    insert into salidas (codigo,cantidad, fecha)
    values
    (1,1,'20190217'),
    (12345,2,'20190111 11:00'),
    (12345,3,'20190114 12:00'),
    (12345,50,'20190117 13:00'),
    (12345,31,'20190119 14:00'),
    (12345,60,'20190206 15:00');
    go
    

    Una solución, es utilizar una tabla derivada para obtener los movimientos por fecha de cada una de las dos tablas, con un union all, y luego en la tabla exterior hacer el acumulado de ambas.

    Para ayudarnos contamos en SQL Server con las funciones de ventana.

    SELECT o.codigo, 
           o.cant, 
           o.tipoMov, 
           SUM(o.cant) OVER(PARTITION BY o.codigo
           ORDER BY o.fecha) AS acumulado, 
           o.fecha
    FROM
    (
        SELECT codigo, 
               'entrada' AS tipoMov, 
               SUM(cantidad) OVER(PARTITION BY codigo
               ORDER BY codigo, 
                        fecha) AS cant, 
               fecha
        FROM entradas
        UNION ALL
        SELECT codigo, 
               'salida', 
               SUM(cantidad * -1) OVER(PARTITION BY codigo
               ORDER BY codigo, 
                        fecha) AS cant, 
               fecha
        FROM salidas
    ) AS o
    ORDER BY codigo desc , fecha;
    

    Salida

    Explicación:

    Si observas el código de la select de la tabla Entradas, no hay nada más que codigo, un literal para que el resultdo sea más visible, la función sum para la cantidad, pero utilizando la cláusula over, que nos ayuda a reiniciar la sumatoria por cada código definido en parition by, y la hace ordenada por codigo y fecha.

    Luego juntamos los resultados con el operador de conjuntos UNION, realizando la misma operativa para las salidas.

    Todo el conjunto lo encerramos en una tabla derivada llamada o y resolvemos su salida, con otra suma para el acumulado, con la misma técnica.

    OVER

    https://docs.microsoft.com/es-es/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

    UNION

    https://docs.microsoft.com/es-es/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-2017

    domingo, 17 de febrero de 2019 6:21
  • Hola de nuevo:

    No me aparecía la imagen por eso te puse que era importante ver como son tus tablas.

    No obstante, el ejemplo que te he puesto, creo que es idéntico a tu solicitud, al menos en los campos más representativos.

    Espero te ayude, sino comentas.

    • Propuesto como respuesta Pedro Alfaro lunes, 18 de febrero de 2019 16:31
    domingo, 17 de febrero de 2019 6:23
  • Estimado

    estoy utilizando el codigo que me proporciono, solo que remplce la columna codigo por COD BARRAS, que es como la tengo nombrada yo, tambien recalcal que tengo una primera columna llamada ID de tipo identidad y la columna FECHA es de tipo DATE y no DATETIME

    SELECT o.[COD BARRAS], 
           o.cant, 
           o.tipoMov, 
           SUM(o.cant) OVER(PARTITION BY o.[COD BARRAS]
           ORDER BY o.[COD BARRAS]) AS acumulado, 
           o.fecha
    FROM
    (
        SELECT [COD BARRAS], 
               'entrada' AS tipoMov, 
               SUM(cantidad) OVER(PARTITION BY [COD BARRAS]
               ORDER BY [COD BARRAS]) AS cant, 
               fecha
        FROM entradas
        UNION ALL
        SELECT [COD BARRAS], 
               'salida', 
               SUM(cantidad * -1) OVER(PARTITION BY [COD BARRAS]
               ORDER BY [COD BARRAS]) AS cant, 
               fecha
        FROM salidas
    ) AS o
    ORDER BY [COD BARRAS] DESC , fecha

    Quedando el resultado como se muestra en la imagen y no como usted me lo proporciona, como se ve el acumulado no lo va realizando por cada registro, que estare haciendo mal ? 

    domingo, 17 de febrero de 2019 19:01
  • Hola:

    Tienes que matizar los "ordenes".

    Según tu escenario:

    drop table if exists entradas;
    drop table if exists salidas;
    
    create table entradas (id int identity(1,1), codBarras int, cantidad int, fecha date)
    go
    create table salidas (id int identity(1,1), codBarras int, cantidad int, fecha date)
    go
    insert into entradas (codBarras, cantidad, fecha)
    values
    (223344,100,'20190214'),
    (54321,60,'20190127'),
    (54321,160,'20190214'),
    (12345,100,'20190127'),
    (12345,160,'20190216'),
    (12345,160,'20190216');
    go
    insert into salidas (codBarras,cantidad, fecha)
    values
    (223344,3,'20190216'),
    (223344,3,'20190216'),
    (223344,3,'20190216'),
    (54321,1,'20190216'),
    (12345,8,'20190216');
    go
    

    La solución, puede ser la misma, solo cambia los ordenamientos.

    SELECT o.codBarras, 
           o.cant, 
           o.tipoMov, 
           SUM(o.cant) OVER(PARTITION BY o.codbarras
           ORDER BY o.codbarras, o.fecha,o.id, o.tipoMov) AS acumulado, 
           o.fecha
    FROM
    (
        SELECT id,
    		   codBarras, 
               'entrada' AS tipoMov, 
               SUM(cantidad) OVER(PARTITION BY codbarras
               ORDER BY codbarras, fecha, id) AS cant, 
               fecha
        FROM entradas
        UNION ALL
        SELECT id,
    			codBarras, 
               'salida', 
               SUM(cantidad * -1) OVER(PARTITION BY codbarras
               ORDER BY codbarras, fecha, id) AS cant, 
               fecha
        FROM salidas
    ) AS o
    ORDER BY codBarras DESC , fecha

    Salida


    • Propuesto como respuesta Pedro Alfaro lunes, 18 de febrero de 2019 16:31
    domingo, 17 de febrero de 2019 20:19
  • La particion indica el grupo mientras que el orden dicta la secuencia dentro de cada grupo.  Si dos movimientos tienen la misma fecha entonces deberas romper el empate para que tu solucion sea determinista (obtener mismo resultado si lo volvemos a ejecutar con la misma data) y una buena condicion seria (fecha, tipo_movimiento, id).

    SELECT
        [COD BARRAS],
        fecha,
        tipoMov,
        ID,
        cantidad,  
        SUM(cantidad * CASE WHEN tipoMov = 'entrada' THEN 1 ELSE -1 end) OVER(
        PARTITION BY o.[COD BARRAS]
        ORDER BY fecha, tipoMov, ID
        ROWS UNBOUNDED PRECEDING
        ) AS acumulado 
    FROM
    (
        SELECT [COD BARRAS], ID,
               'entrada' AS tipoMov, 
               cantidad, 
               fecha
        FROM entradas
    
        UNION ALL
        
        SELECT [COD BARRAS], ID, 
               'salida', 
               cantidad, 
               fecha
        FROM salidas
    ) AS o
    ORDER BY 
        [COD BARRAS], 
        fecha,
        tipoMov,
        ID;

    Nota que adicione el marco (frame - rows ...) porque SQL Server usa por defecto RANGE y no ROWS lo cual resultaria en el uso de tablas de trabajo temporales lo cual afecta el desempenio.

    T-SQL Window Functions and Performance


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 19 de febrero de 2019 16:55