Table of contents
ALM
TOC
Collapse the table of content
Expand the table of content

Generación de Datos Acumulados por Grupos

Microsoft Community Publishing Service|Última actualización: 26/05/2017
|
1 Colaborador

Por FREDDY LEANDRO ANGARITA C.

SqlServer MVPPerfil MVP / freddy_angarita@hotmail.com / http://geeks.ms/blogs/fangarita/default.aspx

Llenado de Espacio en Datos (Generación de Datos Acumulados) por Grupos

Normalmente los productos de software llevan un registro de los hechos que se enfrentan en el negocio, ventas, desembolsos, cobros, intereses, etc. Pero por supuesto no todos los días se vende, se desembolsa, se cobra intereses, cómo podemos resolver de una eficiente la necesidad ver los datos acumulados, este escenario, por lo menos de manera parcial, fue resuelto en un artículo anterior [Code] Cómo calcular un total acumulado Rápido SIN Cursores. Este artículo complementa esa solución presentando cómo se haría lo mismo para generar datos acumulados (o llenar espacios) teniendo en cuenta diferentes grupos dentro de los datos

También se presentará una aplicación de otro artículo llamado [Code] Cómo generar N Filas Rápido SQL Server el cual se usará para generar los datos necesarios para llenar los espacios de la información.

Datos de ejemplo

declare @hechos table (id int, fecha date, producto int, venta decimal)

insert into @hechos values (1, '2011-09-01', 1, 10)
insert into @hechos values (2, '2011-09-03', 1, 20)
insert into @hechos values (3, '2011-09-04', 1, 2)
insert into @hechos values (4, '2011-09-08', 1, 1)
insert into @hechos values (5, '2011-09-10', 1, 3)
insert into @hechos values (6, '2011-09-12', 1, 4)
insert into @hechos values (7, '2011-09-01', 2, 3)
insert into @hechos values (8, '2011-09-03', 2, 2)
insert into @hechos values (9, '2011-09-04', 2, 1)
insert into @hechos values (10, '2011-09-08', 2, 5)
insert into @hechos values (11, '2011-09-10', 2, 6)
insert into @hechos values (12, '2011-09-12', 2, 7)

Los datos en formato tabla:

IdFechaProductoVenta
12011-09-01110
22011-09-03120
32011-09-0412
42011-09-0811
52011-09-1013
62011-09-1214
72011-09-0123
82011-09-0322
92011-09-0421
102011-09-0825
112011-09-1026
122011-09-1227

Observemos que las fechas de venta para cada producto no suceden en días consecutivos, se tienen 2 diferentes productos a los que se quiere calcularle sus ventas acumuladas por día y una cantidad de ventas

Lo primero que haremos es calcular el número de días de diferencia entre la mayor y menos fecha de toda la tabla (para todos los productos)

declare @FechaMin date, @FechaMax Date, @Diferencia int

select @FechaMin = min(Fecha), @FechaMax = MAX(Fecha) from @hechos

set @Diferencia = DATEDIFF(dd,@FechaMin, @FechaMax)

Alternativamente podríamos crear una tabla que tenga la fecha mínima y máxima por cada producto, pero se presentará así para mantener simple el ejemplo.

Pasos:

  • Se genera un CTE con una tabla que tiene todas las fechas posibles (incluso las que no están en la tabla @hechos), llamado Fechas, El código de GenTable se encuentra en [Code] Cómo generar N Filas Rápido SQL Server

  • También se declara una tabla @Acumulado que tendrá el resultado final con los valores acumulados por producto

  • En el CTE ProductoFecha aplicamos una función de conjunto cross join para aplicar cada fecha generada a cada una de los productos de @hechos, lo que nos da como resultado todas las fechas posibles para cada producto

  • Luego, se inserta en @Acumulado el resultado de hacer left join entre ProductoFecha (que tiene todas las fechas) y @Hechos lo que nos da, cada producto y cada fecha posible, en las fechas que no se realizaron ventas se agrega cero (0)


declare @Acumulado table (fecha date, producto int, venta decimal, VentaAcumulada decimal)

;With Fechas (Fecha) as
(
select DATEADD(dd,GT.i,@FechaMin) Fecha from dbo.GenTable(0,@Diferencia,1) GT
)
, ProductoFecha (Producto, Fecha) as (
    select distinct Producto, Fechas.Fecha from @hechos cross join Fechas
)
insert into @Acumulado (fecha, producto, venta, VentaAcumulada)

Select PF.Fecha, PF.Producto, isnull(H.Venta,0) as Ventas, 0 from
ProductoFecha PF left join @hechos H ON PF.Producto = H.producto and
PF.Fecha = h.fecha

Ahora se procede a calcular el total acumulado por grupos:

declare @runningtotal decimal , @ProductoActual int
set @runningtotal = 0
select @ProductoActual = 0

--Anterior
update @Acumulado
set @runningtotal = VentaAcumulada = case when not @ProductoActual = Producto then venta 
else
case when Venta = 0 then @runningtotal else venta end
end
,@ProductoActual = Producto

La parte clave es donde se define el Producto actual por que permite la detección de cambio de grupos para generar así su acumulado, esta técnica puede ser aplicada a múltiples grupos, por ejemplo, ciudad - producto solo agregando otro grupo (para el caso del ejemplo sería @ProductoActual) en el orden que se necesite y así generar todos los grupos necesarios

Los resultados:

fechaproductoventaVentaAcumulada
2011-09-0111010
2011-09-021010
2011-09-0312020
2011-09-04122
2011-09-05102
2011-09-06102
2011-09-07102
2011-09-08111
2011-09-09101
2011-09-10133
2011-09-11103
2011-09-01233
2011-09-02203
2011-09-03222
2011-09-04211
2011-09-05201
2011-09-06201
2011-09-07201
2011-09-08255
2011-09-09205
2011-09-10266
2011-09-11206

Para que ésta solución funcione, los datos deben estar previamente ordenados (por producto y fecha)

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS

SQL Server MVP

© 2018 Microsoft