Items Sold By Vendor by date

[benchristoffels]

Hello,

I am using a query to show Items sold on Invoices by Vendor.  I am wanting to add a column that shows the Vendor Price list for each item and am having difficulty getting it.  Here is what I am using.  Any ideas are helpful.  Thanks!

SELECT T4.CardCode,T4.CardName, SUM(T0.Price * T0.Quantity) AS turnover,SUM(T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

FROM INV1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN OINV T3 ON T3.DocEntry = T0.DocEntry

INNER JOIN OCRD T4 ON T1.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T4.CardCode LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2]

GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

UNION ALL

SELECT T4.CardCode,T4.CardName, SUM(-T0.Price * T0.Quantity) AS turnover,SUM(-T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

FROM RIN1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN ORIN T3 ON T3.DocEntry = T0.DocEntry

INNER JOIN OCRD T4 ON T1.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T4.CardName LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2]

GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

Thanks

Ben

[kothandaraman_nagarajan]

Hi,

Try:

SELECT T4.CardCode,T4.CardName, SUM(T0.Price * T0.Quantity) AS turnover,SUM(T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName, T6.[ListNum] FROM [dbo].[INV1]  T0 INNER JOIN [dbo].[OITM]  T1 ON T0.ItemCode = T1.ItemCode INNER JOIN [dbo].[OITB]  T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod INNER JOIN [dbo].[OINV]  T3 ON T0.DocEntry = T3.DocEntry INNER JOIN [dbo].[OCRD]  T4 ON T3.Cardcode = T4.CardCode INNER JOIN [dbo].[OCRG]  T5 ON T4.GroupCode = T5.GroupCode INNER JOIN OPLN T6 ON T4.ListNum = T6.ListNum WHERE T4.CardCode LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2] GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName,T6.[ListNum]

union all

SELECT T4.CardCode,T4.CardName, -SUM(T0.Price * T0.Quantity) AS turnover,-SUM(T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName, T6.[ListNum] FROM [dbo].[RIN1]  T0 INNER JOIN [dbo].[OITM]  T1 ON T0.ItemCode = T1.ItemCode INNER JOIN [dbo].[OITB]  T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod INNER JOIN [dbo].[ORIN]  T3 ON T0.DocEntry = T3.DocEntry INNER JOIN [dbo].[OCRD]  T4 ON T3.Cardcode = T4.CardCode INNER JOIN [dbo].[OCRG]  T5 ON T4.GroupCode = T5.GroupCode INNER JOIN OPLN T6 ON T4.ListNum = T6.ListNum WHERE T4.CardCode LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2] GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName,T6.[ListNum]

Thanks & Regards,

Nagarajan

[benchristoffels]

I am getting 'No Data Found' when I run the query.

Thanks for the reply.

[kothandaraman_nagarajan]

Already tested in my DB and gettting result from above query. Select different customer code and posting date. Let me know your result.

[benchristoffels]

I have tried three different Vendors and I get no data found on each one.  If I run the same vendors with my original query, I then get results, but the vendor price is not showing.

[benchristoffels]

Take a look at this.  I was able to get the vendor price to show up, but for each item it is giving me 3 lines.  I have to end up sorting it in excel to remove the 2 extra lines that I don't need.  It looks like one is 0 and the other is the sell price.  The middle one, if you sort by doc num, is the vendor price.  Any ideas on how to get it down to just vendor net showing?

SELECT T4.CardCode,T4.CardName, T3.DocNum, SUM(T0.Price * T0.Quantity) AS 'Total Price',SUM(T0.Quantity) AS QTY, T6.[Price] AS 'Vendor',T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

FROM INV1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN OINV T3 ON T3.DocEntry = T0.DocEntry INNER JOIN ITM1 T6 ON T1.ItemCode = T6.ItemCode

INNER JOIN OCRD T4 ON T1.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T4.CardCode LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2]

GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName, T6.[Price], T3.DocNum

UNION ALL

SELECT T4.CardCode,T4.CardName, T3.DocNum, SUM(-T0.Price * T0.Quantity) AS turnover,SUM(-T0.Quantity) AS QTY, -T6.[Price] AS 'Vendor',T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

FROM RIN1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN ORIN T3 ON T3.DocEntry = T0.DocEntry INNER JOIN ITM1 T6 ON T1.ItemCode = T6.ItemCode

INNER JOIN OCRD T4 ON T1.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T4.CardName LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2]

GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName, T6.[Price], T3.DocNum

[kothandaraman_nagarajan]

Do you need vendor price list or item price?

[benchristoffels]

I need to show vendor price list, and then the sell price that is on the invoice for each item.  This will give me my margin for the items sold.

[kothandaraman_nagarajan]

Run below query. Let me know whether getting your requirement or not.

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Quantity], T1.[Price], T1.[LineTotal], T2.[ListNum], T3.[GroupName], T5.[ItmsGrpNam] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode INNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCode INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod WHERE T0.[DocNum] = [%0]

[benchristoffels]

I don't want to run it by doc num.  What I am looking at is to find all items sold by vendor as well returns.  The initial report gave it to me, but the only thing missing was the vendor price list so I could easily calculate my gross margin per sale as well as for all items sold by that vendor.

[kothandaraman_nagarajan]

Try:

SELECT T4.CardCode,T4.CardName, SUM(T0.Price * T0.Quantity) AS turnover,SUM(T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName, T6.[ListNum] FROM INV1  T0 INNER JOIN OITM  T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB  T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod INNER JOIN OINV T3 ON T0.DocEntry = T3.DocEntry INNER JOIN OCRD  T4 ON T1.Cardcode = T4.CardCode INNER JOIN OCRG  T5 ON T4.GroupCode = T5.GroupCode INNER JOIN OPLN T6 ON T4.ListNum = T6.ListNum WHERE T4.CardCode LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2] GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName,T6.[ListNum]

union all

SELECT T4.CardCode,T4.CardName, SUM(T0.Price * T0.Quantity) AS turnover,SUM(T0.Quantity) AS QTY, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName, T6.[ListNum] FROM RIN1  T0 INNER JOIN OITM  T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB  T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod INNER JOIN ORIN T3 ON T0.DocEntry = T3.DocEntry INNER JOIN OCRD  T4 ON T1.Cardcode = T4.CardCode INNER JOIN OCRG  T5 ON T4.GroupCode = T5.GroupCode INNER JOIN OPLN T6 ON T4.ListNum = T6.ListNum WHERE T4.CardCode LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2] GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName,T6.[ListNum]

[benchristoffels]

When I run it, i get the Price List Number, rather than the dollar amount for the price list.  It shows 16 which is the price list num, and it should show $14.99 which is the cost for the item.

[kothandaraman_nagarajan]

Try:

SELECT T4.CardCode,T4.CardName, T3.DocNum, SUM(T0.Price * T0.Quantity) AS 'Total Price',SUM(T0.Quantity) AS QTY, T6.[Price] AS 'Vendor',T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

FROM INV1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN OINV T3 ON T3.DocEntry = T0.DocEntry INNER JOIN ITM1 T6 ON T1.ItemCode = T6.ItemCode 

INNER JOIN OCRD T4 ON T3.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T4.CardCode LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2]

GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName, T6.[Price], T3.DocNum

UNION

SELECT T4.CardCode,T4.CardName, T3.DocNum, SUM(-T0.Price * T0.Quantity) AS turnover,SUM(-T0.Quantity) AS QTY, -T6.[Price] AS 'Vendor',T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName

FROM RIN1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN ORIN T3 ON T3.DocEntry = T0.DocEntry INNER JOIN ITM1 T6 ON T1.ItemCode = T6.ItemCode
INNER JOIN OCRD T4 ON T3.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T4.CardName LIKE '[%0]%'AND T3.DocDate Between [%1] AND [%2]

GROUP BY T4.CardCode, T4.CardName, T2.ItmsGrpNam, T5.GroupName, T1.ItemCode, T1.ItemName, T6.[Price], T3.DocNum