on 04-14-2014 4:30 AM
Estou tentando desenvolver um relatório de sugestão de compras onde eu pego a media de vendas do produto dos últimos 3 meses e a quantidade em estoque e caso o estoque seja menor que a quantidade em estoque ele me sugira essa diferença para comprar.
Estou utilizando esta query:
SELECT T0.[ItemCode], T0.[Dscription],T2.[FrgnName],T2.[SuppCatNum],T3.FIRMNAME,max(t0.docdate) as 'Data', Ceiling(sum(T0.[Quantity])/3) as 'Media Vendas',T4.ONHAND,T2.[BuyUnitMsr]AS 'UNID', Ceiling(sum(T0.[Quantity])/3)-T4.ONHAND as 'SugestaoCompra'
FROM
[INV1] T0 INNER JOIN [OINV] T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN [OITM] T2 ON T0.ItemCode = T2.ItemCode
INNER JOIN [OMRC] T3 ON T2.FirmCode = T3.FirmCode
INNER JOIN [OITW] T4 ON T0.ItemCode = T4.ItemCode
WHERE T1.[InvntSttus] <>'C' AND T2.sellitem='Y' AND t4.whscode='01' AND T1.BPLID=1
GROUP BY T0.[ItemCode], T0.[Dscription],T2.[FrgnName], T2.[SuppCatNum],T3.FIRMNAME,T4.ONHAND,T2.[BuyUnitMsr]
Mais não estou obtendo êxito a query parece esta completamente errada pois os valores vem duplicados e os valores em estoque não batem.
Agradeço qualquer ajuda.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Vinicius, Muito abrigado pela ajuda.
Conseguir fazer a query corretamente segue abaixo:
DECLARE @DP INT, @FAB VARCHAR(30)
SET @DP = 01
SET @FAB = 'FABCASTER'
SELECT T0.[ItemCode],
T0.[Dscription],
T2.[FrgnName],
T2.[SuppCatNum],
T3.FIRMNAME,
max(t0.docdate) as 'Data',
(sum(T0.[Quantity])/3) as 'Media Vendas',
T4.ONHAND,
T2.[BuyUnitMsr]AS 'UNID',
((sum(T0.[Quantity])/3))-(SELECT (T4.ONORDER) FROM OITW T4 WHERE T0.ItemCode = T4.ItemCode AND t4.whscode=@DP)-
(SELECT SUM(T4.[OnHand] - T4.ISCOMMITED) FROM OITW T4 WHERE T0.ItemCode = T4.ItemCode AND t4.whscode=@DP) as 'SugestaoCompra',
(SELECT SUM(T4.[OnHand] - T4.ISCOMMITED) FROM OITW T4 WHERE T0.ItemCode = T4.ItemCode AND t4.whscode=@DP) as [Estoque],
(SELECT (T4.ONORDER) FROM OITW T4 WHERE T0.ItemCode = T4.ItemCode AND t4.whscode=@DP) AS COMPRA
FROM
[INV1] T0
INNER JOIN [OINV] T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN [OITM] T2 ON T0.ItemCode = T2.ItemCode
INNER JOIN [OMRC] T3 ON T2.FirmCode = T3.FirmCode
INNER JOIN [OITW] T4 ON T0.ItemCode = T4.ItemCode
WHERE T1.DOCENTRY NOT IN
(SELECT T9.DOCENTRY
FROM OINV T9 INNER JOIN INV1 T5 ON T9.DOCENTRY = T5.DOCENTRY
INNER JOIN RIN1 T6 ON T6.BASEENTRY = T5.DOCENTRY
INNER JOIN ORIN T7 ON T7 .DOCENTRY = T6.DOCENTRY) AND
T2.sellitem='Y' AND t4.whscode=@DP and T3.FIRMNAME=@FAB
GROUP BY T0.[ItemCode], T0.[Dscription],T2.[FrgnName], T2.[SuppCatNum],T3.FIRMNAME,T4.ONHAND,T2.[BuyUnitMsr]
ORDER BY T0.[ItemCode]
User | Count |
---|---|
100 | |
11 | |
11 | |
6 | |
6 | |
5 | |
4 | |
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.