SQL Server - COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

   
Vista:

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Angel (4 intervenciones) el 12/05/2015 13:13:57
Hola,

Trabajo con SQL Server 2008 R2. Quisiera consultar que función sql o herramienta de T-SQL podria usar para hacer lo siguiente:

Tengo una tabla tal que así:

Numero Nombre Calibre ---------- ---------- Acumulado

7 ---------- Alvaro ---------- 30

9 ---------- Juan ---------- 45

11 ---------- Alvaro ---------- 61

2 ---------- Luis ---------- 28

3 ---------- Juan ---------- 41

1 ---------- Jose ---------- 64

Necesito insertar en el campo Acumulado el valor del campo calibre mas el valor del campo acumulado con numero de orden inmediatamente anteriora para ese mismo Nombre.

Es decir, en la fila con orden 11 con nombre Alvaro insertariamos el valor de 61 + el valor del campo acumulado de la fila con Numero 7 y nombre también Alvaro que es la inmediatamente anterior por número de orden.


¿alguna idea de clausula o función sobre la que investigar para esto?

Gracias

Angel
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Isaias

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Isaias (3186 intervenciones) el 12/05/2015 20:10:16
Dejame entender, ¿Quieres sumar y acumular por NOMBRE?

ID NOMBRE CALIBRE
0 Alvaro 91
0 Jose 64
0 Juan 86
0 Luis 28
1 Jose 64
2 Luis 28
3 Juan 41
7 Alvaro 30
9 Juan 45
11 Alvaro 61

En este caso, los ID 0, son las sumas acumuladas por NOMBRE
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por ANGEL (4 intervenciones) el 15/05/2015 16:47:50
Gracias por la respuesta. Creo que lo acabo de conseguir de la siguiente manera:

Select Numero, Nombre, Calibre,
Calibre+LAG(Calibre,1,0) over (order by Numero) as Acumulado
from laTabla

No obstante, ahora el problema que tengo es que no sé como hacer para grabar el resultado del campo Acumulado en un campo del mismo nombre vacio que he creado en la tabla a la cual he hecho el select.

Gracias de nuevo.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Isaias (3186 intervenciones) el 15/05/2015 18:54:49
Simple, haciendo un JOIN de tu tabla donde el campo esta vacio y el arreglo de datos que acabas de resolver.

1
2
3
4
5
6
UPDATE tutbla SET tucolumnavacia = x.valor
FROM tutabla inner join (
 Select Numero, Nombre, Calibre,
 Calibre+LAG(Calibre,1,0) over (order by Numero) as Acumulado
 from laTabla) as x
on tutabla.id = x.id
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Angel (4 intervenciones) el 15/05/2015 19:46:15
Gracias por tu respuesta Isaías.

En la imagen adjunta puedes ver la aplicación que hemos dado a tu sentencia propuesta.

Básicamente mi propósito es que cada fila sume al valor stock de la fila inmediatamente anterior según el campo Idorden para ese mismo Idarticulo a la cantidad de la fila en cuestión.

El problema me viene en que misteriosamente lo hace bien hasta la fila 9 momento en que no sé por qué empieza a hacer mal los cálculos... quizás debido a algún problema de recursividad o algo similar supongo.

A un nivel mas amplio la aplicación que estoy desarrollando necesitará de muchísimo de estos procesos que te comento. Es decir, modificar y actualizar campos en función de campos anteriores.

¿se te ocurre alguna forma de cómo podría llevarse a cabo? Lo realmente curioso es que si lo hacemos via select el calculo sale perfecto, es al hacer el update en la tabla donde falla.


FEB4DC8912893B95FAF7F8484109D6A527884919CE4539DC39pimgpsh_fullsize_distr
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Isaias (3186 intervenciones) el 15/05/2015 21:16:31
Puedes incluir la creación de la tabla LINEA_STOCK_MODIFICADA?? (para hacer un ejercicio)

Supongo que MOVIMIENTO, es el tipo de movimiento, si INGRESA o SALE, ¿cierto?

O explica que contiene cada campo

Supongo que es SQL Server 2012

Mira, acabo de hacer un ejemplo, a la "antigüita", checa
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
drop table #Tmp
Create Table #Tmp(
Fecha_Hora smalldatetime,
No_Comprobante int,
No_Linea smallint,
Lote int,
Pcb smallint,
Pedido smallint,
Ingreso smallint,
Egreso smallint,
Saldo smallint
)
go
 
Insert into #Tmp values('2005-05-02 01:15:00',4,1,21,6,334,167,0,0)
Insert into #Tmp values('2005-05-03 15:07:10',5,1,29,6,167,167,0,0)
Insert into #Tmp values('2005-07-01 12:25:15',6,1,12,6,200,50,0,0)
Insert into #Tmp values('2005-07-01 21:05:52',25,1,1,6,200,0,140,0)
Insert into #Tmp values('2005-07-02 21:05:52',25,1,1,6,200,9,0,0)
Insert into #Tmp values('2005-07-03 21:05:52',25,1,1,6,200,0,140,0)
Insert into #Tmp values('2005-07-04 21:05:52',25,1,1,6,200,100,0,0)
Insert into #Tmp values('2005-07-05 21:05:52',25,1,1,6,200,0,150,0)
Insert into #Tmp values('2005-07-06 21:05:52',25,1,1,6,200,20,0,0)
Insert into #Tmp values('2005-07-07 21:05:52',25,1,1,6,200,0,20,0)
Insert into #Tmp values('2005-07-08 21:05:52',25,1,1,6,200,100,0,0)
Insert into #Tmp values('2005-07-09 21:05:52',25,1,1,6,200,0,20,0)
go
 
select
        a.fecha_hora,
        a.ingreso,
        a.egreso,
        sum(b.ingreso - b.egreso) as saldo
from
        #Tmp as a
        inner join
        #Tmp as b
        on b.Fecha_Hora <= a.Fecha_Hora
group by
        a.fecha_hora,
        a.ingreso,
        a.egreso
order by
        1 asc
go
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Isaias (3186 intervenciones) el 15/05/2015 21:58:38
Buscando, me encontré algo mucho mas laborioso, este esta calculando incluso, el UNITCOST y EXTENDEDCOST dependiendo del QTYONHAND (SALDO).

Mis respetos para rc_stone_1, quien fue el desarrollador, es para 2005 y superior

Tal vez te pueda servir de ejemplo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
CREATE TABLE #WACTran(
	[TranOrder]		INT PRIMARY KEY CLUSTERED,
	[TranType]		VARCHAR(3) NULL,
	[Qty]			INT NULL,
	[UnitCost]		DECIMAL(14, 4) NULL,
	[ExtendedCost]	DECIMAL(14, 4) NULL,
	[QtyOnHand]		INT
)
 
INSERT INTO [#WACTran](TranOrder, TranType, Qty, UnitCost, ExtendedCost)
VALUES
 (0	,'BB',100	,1.5000,	150.0000 )
,(1	,'S',-50	,0.0000,	0.0000	 )
,(2	,'RCT',15	,0.0000,	0.0000	 )
,(3	,'S',-5		,0.0000,	0.0000	 )
,(4	,'VC',15	,1.6000,	0.0000	 )
,(5	,'S',-5		,0.0000,	0.0000	 )
,(6	,'TO',-4	,0.0000,	0.0000	 )
,(7	,'TI',2		,0.0000,	0.0000	 )
,(8	,'VTI',2	,1.6500,	0.0000	 )
,(9	,'RCT',10	,0.0000,	0.0000	 )
,(10,'S',-5		,0.0000,	0.0000	 )
,(11,'VC',10	,1.7500,	0.0000	 )
,(12,'S',-5		,0.0000,	0.0000	 )
,(13,'TO',-4	,0.0000,	0.0000	 )
,(14,'TI',2		,0.0000,	0.0000	 );
 
 
 
--===== Declare some obviously named variables
DECLARE @PrevTranOrder		INT,
        @PrevQtyOnHand		INT,
		@PrevUnitCost		DECIMAL(14,4),
		@PrevExtendedCost	DECIMAL(14,4)
--===== Do the "quirky" update using a very high speed pseudo-cursor,
     -- This is very similar to what you would do in a language like "C" except the
     -- "Read a row/Write a row" is built into the update.
 UPDATE #WACTran
    SET @PrevQtyOnHand = QtyOnHand = CASE
										WHEN TranType = 'BB'
										THEN Qty
										WHEN TranType IN ('VTI','VC')
										THEN @PrevQtyOnHand
										ELSE @PrevQtyOnHand + Qty
									END,
		@PrevUnitCost = UnitCost = CASE
										WHEN TranType = 'BB'
										THEN UnitCost
										WHEN TranType IN ('VTI','VC')
										THEN (((@PrevQtyOnHand - Qty)*@PrevUnitCost)+(Qty*UnitCost))/@PrevQtyOnHand
										ELSE @PrevUnitCost
									END,
		@PrevExtendedCost = ExtendedCost = CASE
										WHEN TranType = 'BB'
										THEN ExtendedCost
										ELSE @PrevUnitCost*@PrevQtyOnHand
									END,
								@PrevTranOrder   = TranOrder --Just an "anchor", but gotta have it to guarantee things.
						   FROM [#WACTran] WITH (INDEX(0), TABLOCKX)
 
--===== Display the results
 SELECT *
   FROM [#WACTran]
  ORDER BY TranOrder
 
DROP TABLE #WACTran;
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Isaias (3186 intervenciones) el 15/05/2015 22:23:24
Algo mas simple

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table #t 
create table #t ( ord int primary key, total int, running_total int)
 
insert #t(ord,total, running_total)  values (2,20,5)
-- notice the malicious re-ordering
insert #t(ord,total, running_total) values (1,10, 10)
insert #t(ord,total, running_total)  values (3,10, -15)
insert #t(ord,total, running_total)  values (7,1, 20)
insert #t(ord,total, running_total)  values (9,1, -10)
insert #t(ord,total, running_total)  values (11,1, -5)
insert #t(ord,total, running_total)  values (10,1, 20)
--select * from #t
select ord,
       total,
running_total,
       sum(running_total) over(order by ord rows unbounded preceding) as runningtotal
from #t
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Angel (4 intervenciones) el 17/05/2015 18:35:45
Hola Isaias.

Gracias por tu respuesta.


He hecho un cambio en los signos de los movimientos (cantidades) y junto a una sentencia similar a la que me indicabas: Select ... SUM(Cantidad) Over (Partition by IdArticulo Order By IdOrden Rows between Unbounded Preceding and Current Row) as Acumulado, ... he conseguido que funcione correctamente.

También como me indicas lo que he hecho ha sido insertar los valores de este select a una tabla temporal y desde ahi inserto los datos a la tabla LINEAS_STOCKS_MODIFICADA, que es la que realmente me interesa trabajar.

Si me gustaría consultarte de cara a salvar el problema existente en el que el optimizador de SQL no realiza la modificación y lectura de las tablas de forma secuencial si es posible en SQL crear por ejemplo un array de valores mediante un orden y ejecutar un procedimiento a cada uno de dichos valores es su correspondiente orden. Es eso posible?

El motivo por el que te planteo esta cuestión es porque a continuación, partiendo de que ya tengo calculado el acumulado de cantidad de cada fila sin que me afecte el orden en que estas han sido procesadas por SQL, necesito calcular el precio medio de compra según van habiendo compras y ventas. En este caso creo que no podemos recurrir a una suma acumulativa como en el caso de los stocks sino que para el calculo por ejemplo del precio medio tras hacer una compra he de tener calculado el precio medio de la fila inmediatamente anterior para ese Idarticulo.

Pongo una imagen ilustrativa de lo pretendo. Como verás se trata de una tabla en la que tenemos diferentes idarticulos ordenados mediante un campo tipo identitity. El resto se trata de movimiento (c= compra, v=venta), cantidad es la cantidad comprada o vendida, precio, stock resultante tras la operación, precio medio del stock restante y beneficio de la operación.Como he comentado en este caso es vital contar con el cálculo de los registros grabados inmediatamente anteriores para utilizarlos en el calculo de los campos de la operación que introducimos, tanto de compra como de venta.

He leido varias veces las sentencias que me propones pero no tengo nivel para saber como engarzarlas con lo que necesito y tengo ahora mismo.

¿alguna idea o planteamiento sobre el cual investigar como afrontar este problema?

Mil gracias


17-05-2015-17-54-49
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Isaias (3186 intervenciones) el 18/05/2015 21:42:05
Eso ya es algo muy especial sobre tu funcionamiento. No tengo la menor idea de como hacerlo
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Hugo QN (18 intervenciones) el 21/05/2015 19:14:50
Hola Angel, lo que quieres es recalcular el Precio Medio Ponderado del producto.

para ello debes partir desde el primero stock valorizado o compra que tienes. el calculo es la siguiente.

suponiendo hacemos la primera compra el dia 31/03/2015 cantidad 100 a un precio $. 30.00 cada uno total $ 3000.00
entonces el Precio Medio Ponderado de cada producto será $. 30.00
hacemos otra compra el dia 10/04/2015 cantidad 200 a un precio $. 35.00 cada uno total $ 3500.00, a partir de esta fecha el precio medio se altera. su nombre dice precio medio es como calcular el promedio.

entnces:
PMP = Precio Medio Ponderado
C1 = Cantidad en stock
P1 = PMP del stock
C2 = Cantidad de nueva compra
P2 = Precio unitario de nueva compra.

PMP nueva = ((C1 * P1) + (C2 * P2)) / (C1 + C2)

PMP nueva = ((100 * 30) + (200 * 35)) / (100 + 200)

PMP = $. 33.33333

nuevo Precio Medio Ponderado es $ 33.33333
cuando hay una nueva compra se tomará este nuevo precio y se calculará de la misma forma.

en el sistema cada final del dia se debe valorizar si hay compras ese dia, si ya tienes compras que no se velorizaron en su debido momento tendrás que crear un proceso con parametros de fecha desde cuando quieres revalorizar.

comentanos scomo te fue

saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

COMO INSERTAR UN SUMATORIO DE FILAS ORDENADAS POR UN CAMPO

Publicado por Isaias (3186 intervenciones) el 21/05/2015 19:33:38
Pues tal vez sirva de algo:

http://sapb1hub.blogspot.mx/2010/08/calculo-de-la-rotacion-del-inventario-y.html
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar