Skip to Content

Items Sold By Vendor by date

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 08, 2015 at 01:17 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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