/*
This query provides monthly operator-level summary of bookings, WH Out, invoiced revenue, averages, closure ratios, lost revenue, and prior-year revenue for trend analysis over a rolling multi-year period. The report starts at 2022 and is forward facing to the last full calendar year.
*/
/*
Key Features & Metrics:
Booking Totals (Created & WH Out)
[Booking Total Created]– total revenue (net of tax) from bookings based on the entry date.[Booking Total WH Out]– total revenue (net of tax) based on the WH date (ddate).
Invoiced Metrics
[Invoice Total]and[Qty Invoiced]– revenue and count for fully invoiced bookings.
Averages
[Average Booking Created]– average revenue per booking for created bookings.[Average WH Out]– average revenue per WH Out booking.[Average Invoice]– average revenue per invoiced booking.
Ratios & Performance Indicators
[Ratio Closed Quantity]– % of WH Out bookings that were invoiced.[Ratio Closed Revenue]– % of WH Out revenue that was invoiced.[Revenue Lost]– difference between WH Out revenue and invoiced revenue (unclosed bookings).
Historical Comparison
[Revenue Last Year]– invoiced revenue for the same period in the previous year, aligned to allow trend analysis.
Rolling Years & Filters
Rolling window controlled by
@RollingYearsparameter.Revenue calculations exclude certain booking types as defined.
Output: One row per operator per month, including all totals, averages, ratios, lost revenue, and previous year revenue, ready for reporting or KPI dashboards.
*/
Running the Report
/* Complete step 2 before trying to use this report.
Add the code below to EXCEL Query Builder
*/
EXEC dbo.usp_OperatorBookingReport_From2023_Auto;
BEFORE Running the Report
/* Open SQL Server Management Studio and execute the code below to create a stored procedure */
CREATE PROCEDURE [dbo].[usp_OperatorBookingReport_From2023_Auto]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StartYear INT = 2022;
DECLARE @EndYear INT = YEAR(GETDATE()) - 1;
DECLARE @StartDate DATE = DATEFROMPARTS(@StartYear, 1, 1);
DECLARE @EndDate DATE = DATEFROMPARTS(@EndYear, 12, 31);
DECLARE @PrevStartDate DATE = DATEADD(YEAR, -1, @StartDate);
DECLARE @PrevEndDate DATE = DATEADD(YEAR, -1, @EndDate);
IF OBJECT_ID('tempdb..#tmpresults') IS NOT NULL
DROP TABLE #tmpresults;
------------------------------------------------------------
-- Revenue Last Year per operator per month
------------------------------------------------------------
;WITH RevenueLastYear AS (
SELECT
b.operatorsid,
YEAR(DATEADD(YEAR,1,b.ddate)) AS [Year],
MONTH(b.ddate) AS MonthNumber,
SUM(b.price_quoted - ISNULL(b.tax1,0) - ISNULL(b.tax2,0)) AS RevenueLastYear
FROM dbo.tblbookings b
WHERE b.ddate BETWEEN @PrevStartDate AND @PrevEndDate
AND b.invoiced = 'Y'
AND b.booking_type_v32 NOT IN (5,11,13)
GROUP BY b.operatorsid, YEAR(DATEADD(YEAR,1,b.ddate)), MONTH(b.ddate)
)
------------------------------------------------------------
-- Main aggregation stored in temp table
------------------------------------------------------------
SELECT
ISNULL(o.firstname,'Unknown') AS [Operator],
YEAR(ISNULL(b.ddate,b.entrydate)) AS [RYear],
DATENAME(MONTH,ISNULL(b.ddate,b.entrydate)) AS [RMonth],
SUM(CASE WHEN b.entrydate BETWEEN @StartDate AND @EndDate AND b.booking_type_v32 NOT IN (5,11,13) THEN b.price_quoted - ISNULL(b.tax1,0) - ISNULL(b.tax2,0) ELSE 0 END) AS [BKTotal-C],
COUNT(CASE WHEN b.entrydate BETWEEN @StartDate AND @EndDate AND b.booking_type_v32 NOT IN (5,11,13) THEN 1 END) AS [QtyBkd-C],
SUM(CASE WHEN b.ddate BETWEEN @StartDate AND @EndDate AND b.booking_type_v32 NOT IN (5,11,13)
THEN b.price_quoted - ISNULL(b.tax1,0) - ISNULL(b.tax2,0) ELSE 0 END) AS [BookingTotal-O],
COUNT(CASE WHEN b.ddate BETWEEN @StartDate AND @EndDate AND b.booking_type_v32 NOT IN (5,11,13) THEN 1 END) AS [QtyBooked-O],
SUM(CASE WHEN b.ddate BETWEEN @StartDate AND @EndDate AND b.invoiced='Y' AND b.booking_type_v32 NOT IN (5,11,13)
THEN b.price_quoted - ISNULL(b.tax1,0) - ISNULL(b.tax2,0) ELSE 0 END) AS [InvoiceTotal],
COUNT(CASE WHEN b.ddate BETWEEN @StartDate AND @EndDate AND b.invoiced='Y' AND b.booking_type_v32 NOT IN (5,11,13) THEN 1 END) AS [QtyInvoiced],
ISNULL(ry.RevenueLastYear,0) AS [RevLastYr]
INTO #tmpresults
FROM dbo.tbloperators o
LEFT JOIN vwBookAndHist b
ON b.operatorsid = o.id
AND b.ddate BETWEEN @StartDate AND @EndDate
LEFT JOIN RevenueLastYear ry
ON ry.operatorsid = o.id
AND MONTH(ISNULL(b.ddate,b.entrydate)) = ry.MonthNumber
AND YEAR(ISNULL(b.ddate,b.entrydate)) = ry.[Year]
GROUP BY
o.firstname,
YEAR(ISNULL(b.ddate,b.entrydate)),
DATENAME(MONTH,ISNULL(b.ddate,b.entrydate)),
MONTH(ISNULL(b.ddate,b.entrydate)),
ry.RevenueLastYear
HAVING
SUM(CASE WHEN b.entrydate BETWEEN @StartDate AND @EndDate THEN 1 ELSE 0 END) > 0
OR SUM(CASE WHEN b.ddate BETWEEN @StartDate AND @EndDate THEN 1 ELSE 0 END) > 0;
------------------------------------------------------------
-- Final output
------------------------------------------------------------
SELECT
Operator,
RYear as [Year],
RMonth as [Month],
[BKTotal-C] as [BooKing Total-C],
[QtyBkd-C] as [Quantity Booked-C],
[BKTotal-C]/NULLIF([QtyBkd-C],0) as [Average Booking-C],
[BookingTotal-O] as [Booking Total-O],
[QtyBooked-O] as [Quantity Booked-O],
[BookingTotal-O]/NULLIF([QtyBooked-O],0) as [Average Booking-O],
[InvoiceTotal] as [Invoice Total],
[QtyInvoiced] as [Quantity Invoiced],
[InvoiceTotal]/NULLIF([QtyInvoiced],0) as [Average Invoice],
CAST(100.0 * [QtyInvoiced] / NULLIF([QtyBooked-O],0) AS DECIMAL(6,2)) as [Ratio Closed Qty],
CAST(100.0 * [InvoiceTotal] / NULLIF([BookingTotal-O],0) AS DECIMAL(6,2)) as [Ratio Closed Revenue],
[BookingTotal-O] - [InvoiceTotal] as [Revenue Lost],
[RevLastYr] as [Revenue Last Year]
FROM #tmpresults
ORDER BY
[Year],
[Month],
[Operator];