Items sold by preferred vendor for a date range

[former_member634273]

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]

[former_member631142]

Hi Andrea,

Does Sale Price which u are trying to add is an additional column or is it "AvgPrice" column?

[former_member634273]

Hi Kedar

Thank you so much for responding, the sale price i am trying to add is an additional column.

I'd like to see how much we sold it for

Thank you

[former_member631142]

Hi Andrea,
You can use Windows function within SQL statement. Please refer below sample and you can modify as per your actual data set and key columns.

Sample Data - for your reference
ITEMCODE	DOCNUM	SUPPCATNUM	AVGPRICE	QUANTITY
ABC		123	X		10		100
ABC		345	X		20		200
DEF		678	Y		50		500

SELECT ItemCode, DocNum, SUM(AvgPrice) OVER (PARTITION BY ItemCode) AS SALE_PRICE
, SUM(Quantity) OVER (PARTITION BY ItemCode, DocNum) AS QUANTITY
FROM
(
SELECT 'ABC' AS ItemCode,
'123' AS DocNum,
'X' AS SuppCatNum,
10 AS AvgPrice,
100 AS Quantity
FROM DUMMY
UNION ALl
SELECT 'ABC' AS ItemCode,
'345' AS DocNum,
'X' AS SuppCatNum,
20 AS AvgPrice,
200 AS Quantity
FROM DUMMY
UNION ALL
SELECT 'DEF' AS ItemCode,
'678' AS DocNum,
'Y' AS SuppCatNum,
50 AS AvgPrice,
500 AS Quantity
FROM DUMMY
)
ORDER BY ItemCode, DocNum
;

It will give output as below:
ITEMCODE	DOCNUM	SALE_PRICE	QUANTITY
ABC		123	30		100
ABC		345	30		200
DEF		678	50		500		

Regards
Kedar