Skip to Content
Oct 07, 2019 at 08:55 AM

Items sold by preferred vendor for a date range


I currently have the below query which shows me items sold by preferred vendor for a date range. I am trying to add the Sale Price for each item, but am not having any luck. Is this something you can help with?

SELECT T2.DocNum, T2.CardName, T0.[ItemCode] AS 'Item No.', T0.[SuppCatNum] AS 'Mfr Catalog No.', T0.[ItemName] AS 'Item Description', T0.[CardCode] AS 'Preferred Vendor', T0.[AvgPrice] AS 'Item Cost', SUM(T1.[Quantity]) AS 'Amount Quantity' FROM [dbo].[OITM] T0 INNER JOIN [dbo].[INV1] T1 ON T1.[ItemCode] = T0.[ItemCode] AND T1.[ItemCode] = T0.[ItemCode] LEFT JOIN OINV T2 ON T2.DocEntry=t1.DocEntry WHERE T0.[CardCode] = (N'[%1]' ) AND T1.[DocDate] >= (CONVERT(DATETIME, '[%2]', 112) ) AND T1.[DocDate] <= (CONVERT(DATETIME, '[%3]', 112) ) AND T1.[Quantity] <> (0 ) GROUP BY T2.DocNum, T2.CardName, T0.[ItemCode], T0.[SuppCatNum], T0.[ItemName], T0.[CardCode], T0.[AvgPrice] ORDER BY T0.[ItemCode],T0.[SuppCatNum]