cancel
Showing results for 
Search instead for 
Did you mean: 

Sales Data In 2 Queries Do Not Match

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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?

Former Member
0 Kudos

There is one service that is a sale item and not a stock item. However, it does not look like this is the reason for the difference in qty sold.

Any other ideas?

Thanks!

Mike

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Bishal,

Thank you that is very helpful. To be consistent we would like both Queries to have date based on Ship Date and both should be Inventory Item. How can we change so that they are both consistent like this?

Thanks!

Mike

Former Member
0 Kudos

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

Answers (0)