Query Grupo de Artículos

[former_member528316]

Estimados,

Tengo el siguiente problema con una query que estoy generando, resulta que necesito poner el nombre de grupo de artículos, ya tengo el código, pero me falta agregar el nombre del grupo y el stock....a continuación dejo la query que estoy haciendo, para que me puedan guíar.

select itemcode, itemname, ItmsGrpCod, AvgPrice as 'COSTO', U_SEI_LINEA AS 'LINEA', U_SEI_FAMILIA AS 'FAMILIA', U_SEI_SUB_FAMILIA AS 'SUB_FAMILIA'

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=2 AND ItemCode=OITM.ItemCode) AS MESON

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=3 AND ItemCode=OITM.ItemCode) AS BRONCE

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=4 AND ItemCode=OITM.ItemCode) AS PLATA

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=5 AND ItemCode=OITM.ItemCode) AS ORO

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=6 AND ItemCode=OITM.ItemCode) AS DISTRIBUIDORES

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=7 AND ItemCode=OITM.ItemCode) AS HOLDING

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=8 AND ItemCode=OITM.ItemCode) AS FUNCIONARIOS

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=10 AND ItemCode=OITM.ItemCode) AS PPM

  FROM OITM with(nolock)

Atte.

Pablo Aguirre

[former_member188471 · Accepted solution]

Algo así te quedaría con el nombre de GRUPO y para que te de el Stock tendrías que especificar que ALMACÉN.

select OITM.itemcode, OITM.itemname, OITM.ItmsGrpCod, OITM.AvgPrice as 'COSTO', OITM.U_SEI_LINEA AS 'LINEA', OITM.U_SEI_FAMILIA AS 'FAMILIA', OITM.U_SEI_SUB_FAMILIA AS 'SUB_FAMILIA', OITB.ItmsGrpNam, OITW.OnHand

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=2 AND ItemCode=OITM.ItemCode) AS MESON

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=3 AND ItemCode=OITM.ItemCode) AS BRONCE

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=4 AND ItemCode=OITM.ItemCode) AS PLATA

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=5 AND ItemCode=OITM.ItemCode) AS ORO

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=6 AND ItemCode=OITM.ItemCode) AS DISTRIBUIDORES

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=7 AND ItemCode=OITM.ItemCode) AS HOLDING

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=8 AND ItemCode=OITM.ItemCode) AS FUNCIONARIOS

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=10 AND ItemCode=OITM.ItemCode) AS PPM

  FROM OITM with(nolock) INNER JOIN OITB ON OITM.[ItmsGrpCod] = OITB.[ItmsGrpCod]

INNER JOIN OITW ON OITM.ItemCode = OITW.ItemCode

  WHERE OITW.WhsCode='numero almacen'

[former_member528316 · Accepted solution]

Muchas Gracias Julian,

esta super bien lo que enviaste, solo que ahora debo agruparla por codigo, para que me arroje el stock total por producto, y no por almacén....se puede hacer de alguna forma???

[former_member188471 · Accepted solution]

select OITM.itemcode, OITM.itemname, OITM.ItmsGrpCod, OITM.AvgPrice as 'COSTO', OITB.ItmsGrpNam, sUM(OITW.OnHand) total

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=2 AND ItemCode=OITM.ItemCode) AS MESON

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=3 AND ItemCode=OITM.ItemCode) AS BRONCE

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=4 AND ItemCode=OITM.ItemCode) AS PLATA

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=5 AND ItemCode=OITM.ItemCode) AS ORO

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=6 AND ItemCode=OITM.ItemCode) AS DISTRIBUIDORES

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=7 AND ItemCode=OITM.ItemCode) AS HOLDING

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=8 AND ItemCode=OITM.ItemCode) AS FUNCIONARIOS

          ,(SELECT PRICE FROM ITM1 WITH(NOLOCK) WHERE PriceList=10 AND ItemCode=OITM.ItemCode) AS PPM

  FROM OITM with(nolock) INNER JOIN OITB ON OITM.[ItmsGrpCod] = OITB.[ItmsGrpCod]

INNER JOIN OITW ON OITM.ItemCode = OITW.ItemCode

GROUP BY OITM.itemcode, OITM.itemname, OITM.ItmsGrpCod, OITM.AvgPrice , OITB.ItmsGrpNam

SOLO AGREGA LOS CAMPOS DE USUARIO QUE FALTAN

[former_member528316 · Accepted solution]

Muchisimas gracias.