The query will return information for all confirmed and invoiced bookings.
It uses the product rental rate, description and cost values.
SELECT
G.Group_code AS [GROUP],
G.group_descV6 AS [GROUP DESCRIPTION],
C.category_code AS [CATEGORY],
C.cat_descV6 AS [CATEGORY DESCRIPTION],
I.product_code_v42 AS [PRODUCT],
m.descriptionV6 AS [DESCRIPTION],
SUM(I.trans_qty) AS [# TIMES RENTED],
M.on_hand AS [QTY OWNED],
R.rate_1st_day AS [RENTAL PRICE],
M.cost_price AS [COST PRICE]
FROM TBLITEMTRAN I
LEFT OUTER JOIN TBLBOOKINGS B
ON B.booking_no = I.booking_no_v32
LEFT OUTER JOIN TBLINVMAS M
ON M.product_code = I.product_code_v42
LEFT OUTER JOIN tblGroup G
ON G.Group_code = M.groupFld
LEFT OUTER JOIN tblCategory C
ON C.category_code = M.category
LEFT OUTER JOIN tblRatetbl R
ON R.ProductCode = I.product_code_v42
AND R.tableNo = 0
WHERE
B.booking_type_v32 IN (0, 2) /* Single Rental or Quote only */
AND B.invoiced = 'Y'
AND B.BookingProgressStatus = 3
AND NULLIF(LTRIM(RTRIM(G.Group_code)), '') IS NOT NULL
GROUP BY
G.Group_code,
G.group_descV6,
C.category_code,
C.cat_descV6,
I.product_code_v42,
m.descriptionV6,
M.on_hand,
R.rate_1st_day,
M.cost_price
ORDER BY
G.Group_code,
C.category_code,
I.product_code_v42;