Home / Questions / 321814
Accepted
raintrooper
Asked: 2020-01-15 11:55:48 +0800 CST 2020-01-15 11:55:48 +0800 CST

Cumulative Sum in Vista Sql Server 2008

  • 772

I am trying to create a view in sql server 2008 to be able to obtain the kilos of a product that several shippers deliver, grouping them by week, year and type of shipper, as well as the number of shippers that were in charge of making the transfer.

To this I must add a column whose value is obtained by dividing the kilos between the number of chargers, an assistance column, and an accumulated column of the kilos obtained.

What I need and I can't do it, is the accumulated column and improve the performance of the view, since being a view I can't use temporary tables for example.

As I currently have the query:

1

How the query should look like:

two

Example if there were more days, how the accumulated is calculated: 33

If you could help me please, I leave the following example:

edit: Modified the example to make it more readable

   declare @VMovimientos table(
Anio int,
TipoCargador int,
Semana int,
Kilos decimal(12,2),
Vendedor int,
FechaVenta datetime
)

insert into @VMovimientos(Anio,TipoCargador,Semana,Kilos,Vendedor,FechaVenta)values(2019,1,1,100,1,'01/01/2019')
insert into @VMovimientos(Anio,TipoCargador,Semana,Kilos,Vendedor,FechaVenta)values(2019,2,1,100,1,'01/01/2019')
insert into @VMovimientos(Anio,TipoCargador,Semana,Kilos,Vendedor,FechaVenta)values(2019,1,1,100,2,'03/01/2019')
insert into @VMovimientos(Anio,TipoCargador,Semana,Kilos,Vendedor,FechaVenta)values(2019,2,1,100,3,'04/01/2019')

insert into @VMovimientos(Anio,TipoCargador,Semana,Kilos,Vendedor,FechaVenta)values(2019,1,2,100,1,'05/01/2019')
insert into @VMovimientos(Anio,TipoCargador,Semana,Kilos,Vendedor,FechaVenta)values(2019,2,2,100,1,'07/01/2019')
insert into @VMovimientos(Anio,TipoCargador,Semana,Kilos,Vendedor,FechaVenta)values(2019,1,2,100,2,'05/01/2019')
insert into @VMovimientos(Anio,TipoCargador,Semana,Kilos,Vendedor,FechaVenta)values(2019,2,2,100,3,'05/01/2019')

--select * from @VMovimientos

select a.Anio,a.Semana,b.Asistencia,a.TipoCargador,a.Kilos/b.Asistencia as KHSemana,a.Kilos

from (
    select sum(Kilos) Kilos,TipoCargador,a.Semana,a.Anio
    from @VMovimientos a
    where a.Anio=2019
    group by a.Anio,a.Semana,a.TipoCargador
)a 
inner join (select sum(a.Asistencia) Asistencia,a.Anio,a.Semana,a.TipoCargador
            from (
                select count(distinct(Vendedor)) Asistencia,a.Anio,a.Semana,a.TipoCargador
                from @VMovimientos a
                where a.Anio=2019
                group by a.Anio,a.Semana,a.FechaVenta,a.TipoCargador)a
            group by a.Anio,a.Semana,a.TipoCargador)b on a.Anio=b.Anio and a.Semana=b.Semana and a.TipoCargador=b.TipoCargador
inner join (
    select sum(Kilos) Toneladas,TipoCargador,a.Semana,a.Anio
    from @VMovimientos a
    where a.Anio=2019
    group by a.Anio,a.Semana,a.TipoCargador
)d on a.Anio=d.Anio and a.Semana=d.Semana and a.TipoCargador=d.TipoCargador
order by  a.Anio,a.TipoCargador,a.Semana asc
sql

1 Answers

  1. Best Answer

    You can try the following:

    WITH CTE AS
(
    SELECT  Anio,
            Semana,
            COUNT(DISTINCT Vendedor) Asistencia,
            TipoCargador,
            SUM(Kilos) Kilos
    FROM @VMovimientos
    GROUP BY Anio,
             Semana,
             TipoCargador
)
SELECT  A.Anio,
        A.Semana,
        A.Asistencia,
        A.TipoCargador,
        A.Kilos/A.Asistencia KHSemana,
        A.Kilos,
        B.KilosAcumulados
FROM CTE A
OUTER APPLY (SELECT SUM(Kilos) KilosAcumulados
             FROM CTE
             WHERE Anio = A.Anio
             AND Semana <= A.Semana
             AND TipoCargador = A.TipoCargador) B
ORDER BY A.Anio,
         A.TipoCargador,
         A.Semana
;
    • 1