Received and Sold Inventory value by vendor

The below query will give you the received and sold inventory values with including quantity received & sold, quantity received value & sold value based on the vendors.

SELECT Vendor
,ITEMNMBR
,[Received QTY]
,[Sold QTY]
,([Received QTY] – [Sold QTY]) [Remaining QTY]
,[Purchase Value]
,[Sold Value]
,([Purchase Value] – [Sold Value]) [Remaining Inventory Value]
FROM (
SELECT VENDORID AS Vendor
,ITEMNMBR
,(
SELECT SUM(QTYRECVD)
FROM IV10200
WHERE ITEMNMBR = A.ITEMNMBR
AND PCHSRCTY = 5
AND VENDORID = A.VENDORID
) AS [Received QTY]
,(
SELECT SUM(QTYSOLD)
FROM IV10200
WHERE ITEMNMBR = A.ITEMNMBR
AND PCHSRCTY = 5
AND VENDORID = A.VENDORID
) AS [Sold QTY]
,(
SELECT SUM(QTYRECVD * UNITCOST)
FROM IV10200
WHERE ITEMNMBR = A.ITEMNMBR
AND PCHSRCTY = 5
AND VENDORID = A.VENDORID
) AS [Purchase Value]
,(
SELECT SUM(QTYSOLD * UNITCOST)
FROM IV10200
WHERE ITEMNMBR = A.ITEMNMBR
AND PCHSRCTY = 5
AND VENDORID = A.VENDORID
) AS [Sold Value]
FROM IV10200 A
WHERE A.VENDORID <> ”
AND PCHSRCTY = 5
GROUP BY A.VENDORID
,A.ITEMNMBR
) PurchaseByVendor

Hope this helps!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s