Query de stocks real para venta

[former_member211460]

Hola Foro

Espero que alguien me pudiera de favor ayudar, tengo el siguiente
query que esta funcionando el cual me arroja la suma de 2 almacenes (01 y 10)
esto es para que los vendedores vean el total real de estos , a este query se
muestra y se le resta el comprometido del almacén 10 para que en otra columna
traiga exclusivamente lo que se va a vender, mi pregunta es cómo puedo poner en
otra columna las existencias de otro almacén por ejemplo el 60 pero que no las
sume solo que lo traiga como informativo. Saludos y gracias

SELECT        TOP (100) PERCENT OITM.ItemCode AS Referencia, OITM.CodeBars AS [Codigo Barras], OITM.ItemName AS Descripcion, SUM(OITW.OnHand)

                         AS [Disponible 01 y 10], SUM(OITW.IsCommited) AS [Comprometido 10], dbo.ITM1.Price AS [Precio Publico s/iva], SUM(OITW.OnHand)

                         - SUM(OITW.IsCommited) AS [Total Disponible], OITM.FrgnName AS Marca

FROM            dbo.OITM AS OITM INNER JOIN

                         dbo.OITW AS OITW ON OITM.ItemCode = OITW.ItemCode INNER JOIN

                         dbo.OWHS AS OWHS ON OITW.WhsCode = OWHS.WhsCode INNER JOIN

                         dbo.ITM1 ON OITM.ItemCode = dbo.ITM1.ItemCode

WHERE        (OITW.WhsCode = '01') OR

                         (OITW.WhsCode = '10')

GROUP BY OITM.ItemCode, OITM.ItemName, OITM.CodeBars, dbo.ITM1.Price, dbo.ITM1.PriceList, OITM.IssueMthd, OITM.FrgnName

HAVING        (dbo.ITM1.PriceList = 1) AND (OITM.IssueMthd = 'b')

ORDER BY Descripcion

[Former Member · Accepted solution]

Estimado podes agregar esto antes de FROM

SELECT TOP (100) PERCENT 
                    OITM.ItemCode AS Referencia, 
                    OITM.CodeBars AS [Codigo Barras], 
                    OITM.ItemName AS Descripcion, 
                    SUM(OITW.OnHand) AS [Disponible 01 y 10], 
                    SUM(OITW.IsCommited) AS [Comprometido 10], 
                    ITM1.Price AS [Precio Publico s/iva], 
                    SUM(OITW.OnHand)- SUM(OITW.IsCommited) AS [Total Disponible], 
                    OITM.FrgnName AS Marca,
                    TotalBodega = (Select SUM(T0.onhand) from OITW T0 where T0.WhsCode='60' and T0.ItemCode=OITM.ItemCode)
FROM OITM AS OITM 
                    INNER JOIN OITW AS OITW ON OITM.ItemCode = OITW.ItemCode 
                    INNER JOIN OWHS AS OWHS ON OITW.WhsCode = OWHS.WhsCode 
                    INNER JOIN ITM1 ON OITM.ItemCode = dbo.ITM1.ItemCode
WHERE (OITW.WhsCode = '01' OR  OITW.WhsCode = '10')
GROUP BY OITM.ItemCode, OITM.ItemName, OITM.CodeBars, dbo.ITM1.Price, dbo.ITM1.PriceList, OITM.IssueMthd, OITM.FrgnName
HAVING        (dbo.ITM1.PriceList = 1) AND (OITM.IssueMthd = 'b')
ORDER BY Descripcion

Saludos

RC

[former_member211460 · Accepted solution]

Rodolfo, muchas gracias

funcionó bien

saludos