on 02-02-2009 8:40 PM
Hello -
We have two queries shown below - Inventory and Sales Analysis Queries. However, the YTD Sold Qty in the Inventory Query does not seem to match the Jan Qty sales in the Sales Analysis Query.
Shouldn't the sum of the Jan total in Sales Analysis match the YTD Sold Qty in the Inventory Query? Please help?!
Inventory Query:
SELECT t1.ItemCode, t1.ItemName, (Case When t1.Lastpurprc = 0
then t1.LstEvlPric else t1.Lastpurprc end) as Price, t1.OnHand as
'QTY On Hand', T1.OnOrder as 'QTY On PO', T1.IsCommited as
'QTY Committed', sum(t0.Quantity) as 'YTD Sold Qty',
sum(t0.linetotal) AS 'YTD Sold Dollar Amt'
FROM DBO.OITM t1
Left Join DBO.INV1 t0 on t0.itemcode = t1.itemcode and
Year(T0.ShipDate) = Year(Getdate())
WHERE t1.InvntItem = 'Y'
Group by t1.ItemCode, t1.ItemName, t1.Lastpurprc, t1.LstEvlPric,
t1.OnHand, T1.OnOrder, T1.IsCommited
Sales Analysis Query:
SELECT T0.ITEMCODE,
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'JAN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'FEB QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'MAR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'APR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'MAY QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'JUN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'JUL QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'AUG QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'SEP QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'OCT QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'NOV QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'DEC QTY'
FROM [dbo].[OITM] T0
LEFT JOIN [dbo].[INV1] T1 ON T1.ItemCode = T0.ItemCode
WHERE T0.SellItem = 'Y'
GROUP BY T0.ItemCode,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())
ORDER BY T0. ITEMCODE
Do you have any items in your master data that are sales items but not stocked Items?
Your item query appears to be looking at stocked items, while your sales query appears to be looking at Sales items?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mike ,
Some of the issues could be :
a>In one query , you are comparing values --Year(T0.ShipDate) = Year(Getdate()) while other is by Docdate .
b> similarly another condition is by sellitem =y while in query it is by invtitem .
It is really difficult to shoot this kind of issue but well give a shot making similar .
Hope this helps
Bishal
Hi Mike ,
Changing previous logic could defeat the original purpose .But for testing , you can
Try this .
SELECT T0.ITEMCODE,
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'JAN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'FEB QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'MAR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'APR QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'MAY QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'JUN QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'JUL QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'AUG QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'SEP QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'OCT QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'NOV QTY',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK)
WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE =
T0.ITEMCODE) AS 'DEC QTY'
FROM dbo.OITM T0
LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode
WHERE T0.InvntItem = 'Y'
GROUP BY T0.ItemCode,YEAR(T1.ShipDate) HAVING YEAR(T1.ShipDate) = YEAR(GETDATE())
ORDER BY T0. ITEMCODE
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.