Historical Sales Inventory value by customer

The below query will give you the sold inventory values with including quantity, sold value, sold price and profit by customer.

SELECT Vendor
,ITEMNMBR
,QTY
,[Sold Value]
,[Sold Price]
,[Sold Price] – [Sold Value] AS Profit
FROM (
SELECT CUSTNMBR AS Vendor
,ITEMNMBR
,(
SELECT SUM(- TRXQTY)
FROM IV30300
WHERE ITEMNMBR = A.ITEMNMBR
AND DOCTYPE = 6
AND CUSTNMBR = A.CUSTNMBR
) AS QTY
,(
SELECT SUM(EXTDCOST)
FROM IV30300
WHERE ITEMNMBR = A.ITEMNMBR
AND DOCTYPE = 6
AND CUSTNMBR = A.CUSTNMBR
) AS [Sold Value]
,(
(
SELECT SUM(- AA.TRXQTY * BB.UNITPRCE)
FROM IV30300 AA
INNER JOIN SOP30300 BB ON BB.SOPTYPE = 3
AND AA.DOCNUMBR = BB.SOPNUMBE
AND AA.LNSEQNBR = BB.LNITMSEQ
WHERE AA.ITEMNMBR = A.ITEMNMBR
AND AA.DOCTYPE = 6
AND CUSTNMBR = A.CUSTNMBR
GROUP BY AA.ITEMNMBR
)
) AS [Sold Price]
FROM IV30300 A
WHERE A.CUSTNMBR <> ”
AND DOCTYPE = 6
GROUP BY A.CUSTNMBR
,A.ITEMNMBR
) SalesByCustomer

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