none
Ayuda suma acumulativa SQL Server RRS feed

  • Pregunta

  • Hermanos muchas gracias por todo el soporte

    Estoy tratando de desarrollar un analisis de inventario 80/20 en vb.net., pero me encuentro detenido en la sumatoria acumulativa de sqlserver. no se como realizar la consulta necesito : tener la columna que me sume el costo de los productos y colocarlos en una columna ("Acomulativa"), luego sumar el resultado de acumulativa. Mil gracias por todo el soporte.

    


    MB

    lunes, 21 de octubre de 2019 0:04

Respuestas

  • ¿Qué hacer con las líneas repetidas?

    ---

    Si el objetivo es eliminar las líneas repetidas, aquí hay una modificación en su código:

    -- código #2
    with
    Paso0 as (
    SELECT distinct Product, Company, Type, Model, Cost
    from vwMasterMaterials
    ), Paso1 as ( SELECT Product, Company, Type, Model, Cost, sum (Cost) over (order by Cost desc, Product) as Acc_Investment, sum (Cost) over () as Total from Paso0 ),
    Paso2 as ( SELECT Product, Company, Type, Model, Cost, Acc_Investment,
           sum (100.0 * Cost / Total) over (order by Acc_Investment) as [%Accomulated]
      from Paso1
    )
    SELECT Product, Company, Type, Model, Cost,
    format (Acc_Investment, '00.00') as Acc_Investment, format ([%Accomulated], '00.00') as [%Accomulated], iif([%Accomulated]<80, 'A', iif(([%Accomulated]>=80) and ([%Accomulated]< 95),'B', 'C') ) from Paso2 order by Cost Desc;

    Sin embargo, las filas repetidas sólo se borran si todas las columnas tienen el mismo valor.

     


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marcado como respuesta Marcos Bolivar viernes, 25 de octubre de 2019 22:37
    • Editado José Diz viernes, 25 de octubre de 2019 22:47
    viernes, 25 de octubre de 2019 22:15
  • Mil gracias Jose.

    Con esto cierro este tema. El querie me esta funcionando perfectamente.

    Gracias por el apoyo.


    MB

    • Marcado como respuesta Marcos Bolivar viernes, 25 de octubre de 2019 22:37
    viernes, 25 de octubre de 2019 22:37

Todas las respuestas

  • Marcos,

    ¿Puede haber más de una fila para el mismo producto?  En caso afirmativo, ¿deberían sumarse los valores del producto o debería obtenerse la media aritmética?

    Si sólo hay una fila para cada producto, he aqui una sugerencia:

    -- código #1
    with 
    Paso1 as (
    SELECT Fld_IDProduct, Fld_Cost, 
           sum (Fld_Cost) over (order by Fld_Cost desc, Fld_IDProduct) as Acomulativa,
           sum (Fld_Cost) over () as Total
      from tabla
    )
    SELECT Fld_IDProduct, Fld_Cost, Acomulativa, 
           (100.0 * Fld_Cost / Total)  as [Perc item],
           sum (100.0 * Fld_Cost / Total) over (order by Acomulativa) as [Perc Acomulativa]
      from Paso1
      order by Acomulativa;
     

     

    Si esta respuesta te ayudó a resolver tu problema, recuerda marcarla.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz viernes, 25 de octubre de 2019 22:21
    lunes, 21 de octubre de 2019 1:04
  • Gracias Jose.

    Debo hacer algunas modifiaciones al codigo, pero me esta mostrando los resultados esperados. Mil gracias.


    MB

    • Marcado como respuesta Marcos Bolivar lunes, 21 de octubre de 2019 1:28
    • Desmarcado como respuesta Marcos Bolivar viernes, 25 de octubre de 2019 21:26
    lunes, 21 de octubre de 2019 1:28
  • Jose. No habia comparado los numeros pero por alguna razon la sumatoria por excel me da diferente. Te adjunto ambas tablas y el querie para que veas por donde va: 

    with
    Paso1 as (
    SELECT distinct(Product), Company, Type, Model, Cost, 
           sum (Cost) over (order by Cost desc, Product) as Acc_Investment,
           sum (Cost) over () as Total
      from vwMasterMaterials
    )
    SELECT distinct(Product), Company, Type, Model, Cost, FORMAT(Acc_Investment,'00.00') AS Acc_Investment, 
          FORMAT( sum (100.0 * Cost / Total) over (order by Acc_Investment),'00.00') as [%Accomulated], 
      iif(sum (100.0 * Cost / Total) over (order by Acc_Investment)<80,'A',iif((sum (100.0 * Cost / Total) over (order by Acc_Investment)>=80) and (sum (100.0 * Cost / Total) over (order by Acc_Investment)< 95),'B','C') )
      from Paso1
      order by Cost Desc;

    Desde la cuarta fila hacia abajo ya me comienzan a dar diferente valores y eso me altera notablemente los resultados

    Excel 

    470 470 0.184649
    300 770 0.30251
    280 1050 0.412514
    269.35 1319.35 0.518333
    255 1574.35 0.618515
    242.42 1816.77 0.713755
    208.14 2024.91 0.795527
    140.94 2165.85 0.850898
    103.5 2269.35 0.89156
    100 2369.35 0.930847
    100 2469.35 0.970134
    32.64 2501.99 0.982957
    20.69 2522.68 0.991086
    10.44 2533.12 0.995187
    6.99 2540.11 0.997934
    5.26 2545.37 1
      

    SQL Server

    470.00 470.00 16.70
    300.00 770.00 27.36
    280.00 1050.00 37.30
    269.35 1588.70 46.87
    255.00 1843.70 55.93
    242.42 2086.12 64.55
    208.14 2294.26 71.94
    140.94 2435.20 76.95
    103.50 2538.70 80.62
    100.00 2638.70 84.18
    100.00 2738.70 87.73
    32.64 2771.34 88.89
    20.69 2792.03 89.62
    10.44 2802.47 90.00
    6.99   2809.46 90.24
    5.26   2814.72 90.43


    MB



    viernes, 25 de octubre de 2019 21:31
  • Jose. No habia comparado los numeros pero por alguna razon la sumatoria por excel me da diferente. Te adjunto ambas tablas y el querie para que veas por donde va:

    Marcos, hay algunas preguntas en mi primera respuesta que aún no han sido contestadas. A partir de las respuestas es posible definir los cambios a realizar en el código #1.

    El código #1 fue construido considerando que para cada producto hay una sola fila. Sin embargo, al analizar la imagen inicial que usted envió se observa que contiene 2 filas para el producto P00006.

    ---

    No se puede utilizar DISTINCT para borrar repeticiones de una columna, sino siempre en el contenido completo de la fila.

    En este caso,
       SELECT distinct(Product), Company, ...

    es lo mismo que
       SELECT distinct Product, Company, ...

     


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz viernes, 25 de octubre de 2019 22:20
    viernes, 25 de octubre de 2019 21:42
  • Jose.

    Estoy chequeando ahorita con tu codigo original y me da cerca de los valores pero como ese es el maestro de materiales estoy tratando de traer distintos numeros de productos y al hacer eso me da diferente la suma. Please, Necesito tu ayuda tengo una semana para terminar el proyecto y esto es lo unico que me falta :) Mil gracias. Adjunto Tabla con todo los valores: 

    P00011    470.00 470.00 16.697930877671668 16.697930877671668
    P00001    300.00 770.00 10.658253751705320 27.356184629376988
    P00008    280.00 1050.00 9.947703501591632 37.303888130968620
    P00006    269.35 1588.70 9.569335493406093 56.442559117780806
    P00006    269.35 1588.70 9.569335493406093 56.442559117780806
    P00007    255.00 1843.70 9.059515688949522 65.502074806730328
    P00012    242.42 2086.12 8.612579581628012 74.114654388358340
    P00002    208.14 2294.26 7.394696452933151 81.509350841291491
    P00016    140.94 2435.20 5.007247612551159 86.516598453842650
    P00005    103.50 2538.70 3.677097544338335 90.193695998180985
    P00004    100.00 2638.70 3.552751250568440 93.746447248749425
    P00010    100.00 2738.70 3.552751250568440 97.299198499317865
    P00003    32.64 2771.34 1.159618008185538 98.458816507503403
    P00014    20.69 2792.03 0.735064233742610 99.193880741246013
    P00015    10.44 2802.47 0.370907230559345 99.564787971805358
    P00013    6.99         2809.46 0.248337312414733 99.813125284220091
    P00017    5.26         2814.72 0.186874715779899 99.999999999999990


    MB

    viernes, 25 de octubre de 2019 21:52
  • ¿Qué hacer con las líneas repetidas?

    ---

    Si el objetivo es eliminar las líneas repetidas, aquí hay una modificación en su código:

    -- código #2
    with
    Paso0 as (
    SELECT distinct Product, Company, Type, Model, Cost
    from vwMasterMaterials
    ), Paso1 as ( SELECT Product, Company, Type, Model, Cost, sum (Cost) over (order by Cost desc, Product) as Acc_Investment, sum (Cost) over () as Total from Paso0 ),
    Paso2 as ( SELECT Product, Company, Type, Model, Cost, Acc_Investment,
           sum (100.0 * Cost / Total) over (order by Acc_Investment) as [%Accomulated]
      from Paso1
    )
    SELECT Product, Company, Type, Model, Cost,
    format (Acc_Investment, '00.00') as Acc_Investment, format ([%Accomulated], '00.00') as [%Accomulated], iif([%Accomulated]<80, 'A', iif(([%Accomulated]>=80) and ([%Accomulated]< 95),'B', 'C') ) from Paso2 order by Cost Desc;

    Sin embargo, las filas repetidas sólo se borran si todas las columnas tienen el mismo valor.

     


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marcado como respuesta Marcos Bolivar viernes, 25 de octubre de 2019 22:37
    • Editado José Diz viernes, 25 de octubre de 2019 22:47
    viernes, 25 de octubre de 2019 22:15
  • Mil gracias Jose.

    Con esto cierro este tema. El querie me esta funcionando perfectamente.

    Gracias por el apoyo.


    MB

    • Marcado como respuesta Marcos Bolivar viernes, 25 de octubre de 2019 22:37
    viernes, 25 de octubre de 2019 22:37