Saldo de articulos query

[Former Member]

Hola a todos, generamos un query similar al Informe de autitoria de stock nativo de SAP, el cual lee dos tablas de usuario que generamos, llamadas Color y Talla, el query jala bien pero no hemos logrado que traiga el dato del saldo de cada articulo, es decir actualemnte podemos traer el saldo inicial segun la fecha que seleccione el usuario y todos los movimientos que ha tenido dicho articulo a partir de esa fecha, pero el usuaio desea que se pueda agregar una linea con el saldo que queda de dicho articulo despues de que el query realiza las sumas y restas de los movimientos obtenidos.

Les paso el query

Edited by: Yessic on May 10, 2011 7:34 PM

[former_member188440 · Accepted solution]

En la tabla OINM hay una columna llamada balance, esa te da el saldo no?

[Former Member · Accepted solution]

si pero no toma en cuanta la separacion de color y talla que necesitamos....

no se que pasa con el foro no puedo pegar el query como codigo creo q es porq es muy grande .... lo pegare por partes

[Former Member · Accepted solution]

union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'EP ' as  mov, OPDN.docNUM, PDN1.Quantity ,OPDN.docdate as ddate, DocTime from PDN1, OPDN where 

OPDN.DocDate >= @fechaIni  and OPDN.DocDate <= @fechaFin and PDN1.DocEntry = OPDN.DocEntry  
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'EM ' as  mov, OIGN.docNUM, IGN1.Quantity ,OIGN.docdate as ddate, DocTime from IGN1, OIGN where 

OIGN.DocDate >= @fechaIni  and OIGN.DocDate <= @fechaFin and IGN1.DocEntry = OIGN.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'DV ' as  mov, ORDN.docNUM, RDN1.Quantity ,ORDN.docdate as ddate, DocTime from RDN1, ORDN where 

ORDN.DocDate >= @fechaIni  and ORDN.DocDate <= @fechaFin and RDN1.DocEntry = ORDN.DocEntry
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'RC ' as  mov, ORIN.docNUM, RIN1.Quantity ,ORIN.docdate as ddate, DocTime from RIN1, ORIN where 

ORIN.DocDate >= @fechaIni  and ORIN.DocDate <= @fechaFin and basetype <> 16  and RIN1.DocEntry = ORIN.DocEntry
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'TR ' as  mov, OWTR.docNUM, WTR1.Quantity ,OWTR.docdate as ddate, DocTime from WTR1, OWTR where 

OWTR.DocDate >= @fechaIni  and OWTR.DocDate <= @fechaFin and WTR1.DocEntry = OWTR.DocEntry      
      union SELECT ItemCode, U_COLOR, U_TALLA, filler as WhsCode, 'TR ' as  mov, OWTR.docNUM, (-1*WTR1.Quantity) as quantity ,OWTR.docdate as ddate, DocTime from 

WTR1 left join OWTR on WTR1.docentry = OWTR.docentry where OWTR.DocDate >=@fechaIni  and OWTR.DocDate <= @fechaFin
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'DM ' as  mov, ORPD.docNUM, (-1*RPD1.Quantity) as quantity ,ORPD.docdate as ddate, DocTime from RPD1, 

ORPD where ORPD.DocDate >= @fechaIni  and ORPD.DocDate <= @fechaFin and RPD1.DocEntry = ORPD.DocEntry
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'SM ' as  mov, OIGE.docNUM, (-1*IGE1.Quantity) as Quantity ,OIGE.docdate as ddate, DocTime from IGE1, OIGE 

where OIGE.DocDate >= @fechaIni  and OIGE.DocDate <= @fechaFin and IGE1.DocEntry = OIGE.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'NE ' as  mov, ODLN.docNUM, (-1*DLN1.Quantity) as Quantity ,ODLN.docdate as ddate, DocTime from DLN1, ODLN 

where ODLN.DocDate >= @fechaIni  and ODLN.DocDate <= @fechaFin and DLN1.DocEntry = ODLN.DocEntry
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'AC ' as  mov, ORPC.docNUM, (-1*RPC1.Quantity) as Quantity ,ORPC.docdate as ddate, DocTime from RPC1, 

ORPC where ORPC.DocDate >= @fechaIni  and ORPC.DocDate <= @fechaFin and RPC1.DocEntry = ORPC.DocEntry
) as kardex left join [@TALLA] T on kardex.u_talla = t.code LEFT JOIN OITM ON KARDEX.ITEMCODE = OITM.ITEMCODE 


WHERE KARDEX.ITEMCODE >= @ITEMCODE AND KARDEX.ITEMCODE <= @ITEMCODE1
AND WHSCODE LIKE @ALMACEN

order by ddate,doctime,KARDEX.itemcode,u_color,u_talla,whscode

[Former Member · Accepted solution]

el resultado que esperan es algo asi como la columna llamada SUBTOTAL

COD ART	NOMBRE	COLOR	TALLA	ALMACEN	TIPO MOVIMIENTO	NUM DOCTO	CANTIDAD	FECHA	HORA	SUBTOTAL
ATEX-0013	MANDIL MANTA 	MU	U	SF	Inicial		591	01/01/2011		
ATEX-0013	MANDIL MANTA 	MU	U	SF	TR	696	-15	06/01/2011	1745	
ATEX-0013	MANDIL MANTA 	MU	U	SF	TR	912	-15	03/02/2011	1132	
ATEX-0013	MANDIL MANTA 	MU	U	SF	TR	945	-50	08/02/2011	1748	511
ATEX-0015	SET DE DONAS 	MU	U	SF	Inicial		590	01/01/2011		
ATEX-0015	SET DE DONAS 	MU	U	SF	TR	759	-60	10/01/2011	1650	530

[felipe_loyolarodriguez · Accepted solution]

Que tal asi

-

---

Bueno, parece que no soporta tantos caracteres

Desde aqui puedes descargar el [Query|http://www.sendspace.com/file/nimmm0]

Espero te sirva, ya que el calculo lo hice en base a una suposicion, pero la forma de hacerlo es tomar el mismo calculo del inicial y cambiarle el rango de fechas

Ahora bien, en vez de agregar una columna, te sale mucho mas facil insertar el subtotal de la misma forma que el calculo inicial

Saludos

Felipe Loyola

Edited by: Floyola on May 10, 2011 9:48 PM

[Former Member · Accepted solution]

corri el query y si sale el subtotal pero el saldo no es correcto, supongo yo q es por las formulas, pero pues ahirita lo editamos. La idea de meter subtotales como lineas es genial, fijate que siempre habiamos batallado con poder logra eso.

Cierro la nota y si tengho mas dudas te aviso.

Gracias

[Former Member · Accepted solution]

Alcantara Casillas,

             Mucho gusto quisiera saber si me puedes reenviar el archivo con el query que te envio Felipe a mi tambien me seria de gran ayuda.

Saludos,

Alfredo rodriguez

[Former Member · Accepted solution]

Felipe,

         Buen día, de casualidad podrias volver a publicar este documento con el query me seria de mucha ayuda, mil Gracias por tu ayuda.

Saludos,

Alfredo Rodríguez

[Former Member]

declare @fechaini as datetime
declare @fechafin as datetime
declare @ITEMCODE as varchar(12)
declare @ITEMCODE1 as varchar(12)
declare @ALMACEN as varchar(10)

set @fechaIni = (Select top 1 R0.docDate from [dbo].[OINV] R0 where R0.docDate = '[%0]')
set @fechaIni = '[%0]'
set @fechaFin = (Select top 1 R1.docDate from [dbo].[OINV] R1 where R1.docDate = '[%1]')
set @fechaFin = '[%1]'
set @ITEMCODE = (Select top 1 R2.itemcode from [dbo].[OItm] R2 where R2.itemcode = '[%2]')
set @ITEMCODE = '[%2]'
set @ITEMCODE1 = (Select top 1 R3.itemcode from [dbo].[OItm] R3 where R3.itemcode = '[%3]')
set @ITEMCODE1 = '[%3]'
set @ALMACEN = (Select top 1 R4.whscode from [dbo].[OWHS] R4 where R4.WHSCODE = '[%4]')
set @ALMACEN = '[%4]'


select  kardex.ItemCode AS "COD ART", OITM.ITEMNAME AS "NOMBRE",kardex.U_COLOR AS COLOR, T.U_CODIGO AS TALLA, kardex.WhsCode AS ALMACEN, kardex.mov 

AS "TIPO MOVIMIENTO",KARDEX.docNUM AS "NUM DOCTO", kardex.quantity AS CANTIDAD,kardex.ddate AS "FECHA", doctime AS HORA from
(
    select
      ItemCode, U_COLOR, U_TALLA, WhsCode, mov,docnum, sum(Quantity) as quantity,ddate,doctime 
    from ( 
      SELECT ItemCode, U_COLOR, U_TALLA, WhsCode,'Inicial' as  mov,  '' as docNUM, Quantity  , @fechaIni as ddate, 0 as DocTime  from PDN1,OPDN where OPDN.DocDate < 

@fechaIni AND PDN1.DocEntry = OPDN.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, Quantity ,@fechaIni as ddate,0 as DocTime  from IGN1,OIGN where OIGN.DocDate < 

@fechaIni  AND IGN1.DocEntry = OIGN.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, Quantity ,@fechaIni as ddate,0 as DocTime  from RDN1,ORDN where ORDN.DocDate 

< @fechaIni AND RDN1.DocEntry = ORDN.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, Quantity ,@fechaIni as ddate,0 as DocTime  from RIN1,ORIN where ORIN.DocDate < 

@fechaIni and basetype <> 16 AND RIN1.DocEntry = ORIN.DocEntry      
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, Quantity ,@fechaIni as ddate,0 as DocTime  from WTR1,OWTR where 

OWTR.DocDate < @fechaIni AND WTR1.DocEntry = OWTR.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, (-1*Quantity) as quantity ,@fechaIni as ddate,0 as DocTime  from WTR1 left join 

OWTR on WTR1.docentry = OWTR.docentry where OWTR.DocDate < @fechaIni      
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, (-1*Quantity) as quantity ,@fechaIni as ddate,0 as DocTime  from RPD1,ORPD where 

ORPD.DocDate < @fechaIni AND RPD1.DocEntry = ORPD.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, (-1*Quantity) as Quantity ,@fechaIni as ddate,0 as DocTime  from IGE1,OIGE where 

OIGE.DocDate < @fechaIni AND IGE1.DocEntry = OIGE.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, (-1*Quantity) as Quantity ,@fechaIni as ddate,0 as DocTime  from DLN1,ODLN where 

ODLN.DocDate < @fechaIni AND DLN1.DocEntry = ODLN.DocEntry 
      union SELECT ItemCode, U_COLOR, U_TALLA, WhsCode, 'Inicial' as  mov, '' as docNUM, (-1*Quantity) as Quantity ,@fechaIni as ddate,0 as DocTime  from RPC1,ORPC where 

ORPC.DocDate < @fechaIni AND RPC1.DocEntry = ORPC.DocEntry 
    )  as inicial group by docnum,ItemCode, U_COLOR, U_TALLA, WhsCode, mov, ddate,doctime