on 08-25-2015 11:24 PM
Saludos
por favor necesito una consulta donde me arroje la existencia de los artículos por almacén y lo separe por grupo de articulo
agradezco el apoyo brindado
César, intenta con este query:
DECLARE @Cad VARCHAR(MAX), @Query NVARCHAR(MAX)
SET @Cad = ''
SELECT @Cad = '[' + REPLACE((SELECT Whs AS [data()] FROM
(SELECT distinct WhsCode Whs FROM OWHS T0) T order by 1 FOR XML PATH ('')), ' ', '],[') +']'
SET @Query= N'
SELECT * FROM
(SELECT T2.ItemCode, T2.ItemName, T3.ItmsGrpNam, T2.OnHand [Empresa], T1.WhsCode [Almacén], T1.OnHand
FROM OITW T1
inner join OITM T2 on T1.ItemCode = T2.ItemCode
inner join OITB T3 on T2.ItmsGrpCod = T3.ItmsGrpCod
) [T0]
PIVOT (
sum(T0.OnHand) FOR T0.[Almacén] IN (' + @Cad + ')
)AS [Count] order by 1
'
EXECUTE SP_EXECUTESQL @Query
Nos comentas si es lo que necesitas.
Saludos y suerte,
JC.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Muchas gracias por su apoyo
Agustin Marcos CividanesJuan Carlos Huerta Esquivel
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola
prueba la siguiente consulta:
select T0.ItemCode, T0.ItemName, T3.ItmsGrpNam, T2.WhsName, T1.OnHand from OITM T0 inner join OITW T1 on T0.itemcode = T1.itemcode inner join OWHS T2 on T1.whscode = T2.WhsCode inner join OITB T3 on T0.itmsgrpcod = T3.ItmsGrpCod order by t0.ItmsGrpCod
Un saludo
Agustín Marcos Cividanes
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Gracias Agustin Marcos Cividanes !
es posible que la columna stock este separada por almacén, es decir que cada almacén este en una columna con su respectiva existencia
Gracias!!
Hola
Creo que lo que buscas:
select S0.ItemCode, MAX(S0.[Stock01])AS 'Almacén 01', MAX(S0.[Stock02]) AS 'Almacén 02' from ( select T0.ItemCode, T0.ItemName, T3.ItmsGrpNam, isnull(max(T1.OnHand), 0) AS 'Stock01', -1 AS'STock02' from OITM T0 inner join OITW T1 on T0.itemcode = T1.itemcode AND T1.WhsCode = '01' inner join OWHS T2 on T1.whscode = T2.WhsCode inner join OITB T3 on T0.itmsgrpcod = T3.ItmsGrpCod group by T0.ItemCode, T0.ItemName, T3.ItmsGrpNam, T2.whscode union all select T0.ItemCode, T0.ItemName, T3.ItmsGrpNam, -1 AS stock01, isnull(max(T1.OnHand), 0) AS 'Stock02' from OITM T0 inner join OITW T1 on T0.itemcode = T1.itemcode AND T1.WhsCode = '02' inner join OWHS T2 on T1.whscode = T2.WhsCode inner join OITB T3 on T0.itmsgrpcod = T3.ItmsGrpCod group by T0.ItemCode, T0.ItemName, T3.ItmsGrpNam, T2.whscode ) S0 group by S0.ItemCode order by S0.ItemCode
Tendrás que replicar la claúsula unión all como almacenes quieras mostrar. Además, por cada una que añadas tendrás que añadir un campo con valor -1 en el resto para que el SQL sea correcto. Es decir, si añades un tercer almacén tendrás que añadir en las dos primeras y al final -1 AS Stock3, y en la tercera subconsulta isnull(max(T1.Onhand), 0) AS stock3, y en el primer inner join poner el código del almacén que corresponda.
Un saludo
Agustín Marcos Cividanes
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.