Package Revenue

This is an advance query as you will need to change date range as needed. This query will allow you to get the revenue that packages generated as well as determine how many times a package has been rented within a particular time period. 

Date range is based on Invoice Date of booking and Actual Invoice Date of any invoices produced for long term rentals.

You can execute this query using Excel Query Builder in RentalPoint.....

DECLARE @StartDate AS VARCHAR(20) 
DECLARE @EndDate AS VARCHAR(20) 

SET @StartDate = 'Nov 1 2018' 
SET @EndDate = 'Feb 28 2019' 

SELECT IT.product_code_v42                   [Product Code], 
       M.descriptionv6                       [Product Description], 
       Sum(IT.trans_qty)                     [Times Rented], 
       Sum(IT.days_using)                    [Days Using], 
       Cast(Sum(IT.price) AS DECIMAL(14, 2)) AS [Revenue], 
       Max(it.booking_no_v32)                AS 
       [Booking Ref (for long term rentals], 
       (SELECT Count(*) 
        FROM   tblinvhead i 
        WHERE  it.booking_no_v32 = booking_no 
               AND i.invdate > @StartDate 
               AND i.invdate < @EndDate) 
       [Times Invoiced (for long term rentals)] 
FROM   dbo.tblitemtran AS IT 
       INNER JOIN dbo.tblbookings AS B 
               ON IT.booking_no_v32 = B.booking_no 
       LEFT OUTER JOIN dbo.tblinvmas AS M 
                    ON IT.product_code_v42 = M.product_code 
WHERE  ( M.product_config = '1' ) 
       AND ( B.inv_date > @StartDate ) 
       AND ( B.inv_date < @EndDate ) 
       AND ( b.invoiced = 'Y' ) 
GROUP  BY IT.product_code_v42, 
          M.descriptionv6, 
          it.booking_no_v32 
ORDER  BY IT.product_code_v42