/* Lists all items sold on confirmed bookings between start and end date
Int1 offers ability to show a shorter date range for comparison sales
*/
DECLARE @StartDate AS VARCHAR(20)
DECLARE @EndDate AS VARCHAR(20)
DECLARE @Int1Start AS VARCHAR(20)
SET @StartDate = '2019-01-01'
SET @EndDate = '2021-08-31'
SET @Int1Start = '2020-01-01'
SELECT Max(V.product_code_v42) AS [Product Code],
Max(i.descriptionv6) AS
[Product Description],
Max(i.on_hand) AS [Inventory],
Max(i.ord_unit) AS [Reorder Unit],
Max(i.re_ord_level) AS [Reorder Level],
Sum(V.trans_qty) AS
[Qty Sold since 2019],
Isnull((SELECT Sum(h.trans_qty)
FROM vwitemandhist h
LEFT OUTER JOIN vwbookandhist x
ON x.booking_no = h.booking_no_v32
WHERE h.trans_type_v41 IN ( 6, 7 )
AND ( h.firstdate > = @StartDate
AND h.firstdate <= @Int1Start )
AND h.product_code_v42 = v.product_code_v42
AND x.bookingprogressstatus = 3), 0) AS
[Qty Sold 2019-2020],
Isnull((SELECT Sum(h.trans_qty)
FROM vwitemandhist h
LEFT OUTER JOIN vwbookandhist x
ON x.booking_no = h.booking_no_v32
WHERE h.trans_type_v41 IN ( 6, 7 )
AND ( h.firstdate > = @Int1Start
AND h.firstdate <= @EndDate )
AND h.product_code_v42 = v.product_code_v42
AND x.bookingprogressstatus = 3), 0) AS
[Qty Sold 2020-2021],
Max(i.cost_price) AS
[Cost Price Per unit],
Max(i.retail_price) AS
[Retail Price Per Unit]
FROM vwitemandhist V
LEFT OUTER JOIN tblinvmas i
ON I.product_code = V.product_code_v42
LEFT OUTER JOIN vwbookandhist b
ON b.booking_no = v.booking_no_v32
WHERE v.trans_type_v41 IN ( 6, 7 )
AND ( v.firstdate > = @StartDate
AND v.firstdate <= @EndDate )
AND b.bookingprogressstatus = 3
GROUP BY v.product_code_v42