/*
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 2023 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;
-- Automatic start year
DECLARE @StartYear INT = 2023;
-- Last full calendar year
DECLARE @EndYear INT = YEAR(GETDATE()) - 1;
-- Date ranges
DECLARE @StartDate DATE = DATEFROMPARTS(@StartYear, 1, 1);
DECLARE @EndDate DATE = DATEFROMPARTS(@EndYear, 12, 31);
-- Last full year window for Revenue Last Year
DECLARE @LastFullYearStart DATE = DATEFROMPARTS(@EndYear, 1, 1);
DECLARE @LastFullYearEnd DATE = DATEFROMPARTS(@EndYear, 12, 31);
;WITH Base AS
(
SELECT
b.booking_no,
b.ddate,
b.entrydate,
b.booking_type_v32,
b.BookingProgressStatus,
b.invoiced,
o.firstname AS Operator,
NetAmount = b.price_quoted - ISNULL(b.tax1,0) - ISNULL(b.tax2,0)
FROM dbo.tblbookings b
LEFT JOIN dbo.tbloperators o
ON b.operatorsid = o.id
),
CreatedStream AS
(
SELECT
Operator,
YEAR(ddate) AS [Year],
MONTH(ddate) AS MonthNumber, -- internal for sorting
DATENAME(MONTH, ddate) AS [Month],
NetAmount AS CreatedTotal,
1 AS CreatedQty,
0 AS WHOutTotal,
0 AS WHOutQty,
0 AS InvoiceTotal,
0 AS InvoiceQty,
0 AS RevenueLastYear
FROM Base
WHERE entrydate BETWEEN @StartDate AND @EndDate
AND booking_type_v32 NOT IN (5,11,13)
),
WHStream AS
(
SELECT
Operator,
YEAR(ddate) AS [Year],
MONTH(ddate) AS MonthNumber,
DATENAME(MONTH, ddate) AS [Month],
0 AS CreatedTotal,
0 AS CreatedQty,
CASE WHEN booking_type_v32 NOT IN (5,11,13) THEN NetAmount ELSE 0 END AS WHOutTotal,
CASE WHEN booking_type_v32 NOT IN (5,11,13) THEN 1 ELSE 0 END AS WHOutQty,
CASE WHEN BookingProgressStatus = 3
AND invoiced = 'Y'
AND booking_type_v32 NOT IN (1,5,11,13)
THEN NetAmount ELSE 0 END AS InvoiceTotal,
CASE WHEN BookingProgressStatus = 3
AND invoiced = 'Y'
AND booking_type_v32 NOT IN (1,5,11,13)
THEN 1 ELSE 0 END AS InvoiceQty,
0 AS RevenueLastYear
FROM Base
WHERE ddate BETWEEN @StartDate AND @EndDate
),
LastYearStream AS
(
SELECT
Operator,
YEAR(DATEADD(YEAR, 1, ddate)) AS [Year],
MONTH(ddate) AS MonthNumber,
DATENAME(MONTH, ddate) AS [Month],
0 AS CreatedTotal,
0 AS CreatedQty,
0 AS WHOutTotal,
0 AS WHOutQty,
0 AS InvoiceTotal,
0 AS InvoiceQty,
NetAmount AS RevenueLastYear
FROM Base
WHERE ddate BETWEEN @LastFullYearStart AND @LastFullYearEnd
AND BookingProgressStatus = 3
AND invoiced = 'Y'
AND booking_type_v32 NOT IN (1,5,11,13)
)
SELECT
Operator,
[Year],
[Month],
ISNULL(SUM(CreatedTotal),0) AS [Booking Total-C],
ISNULL(SUM(CreatedQty),0) AS [Qty Booked-C],
ISNULL(CAST(SUM(CreatedTotal)/NULLIF(SUM(CreatedQty),0) AS DECIMAL(10,2)),0) AS [Average Booking-C],
ISNULL(SUM(WHOutTotal),0) AS [Booking Total-O],
ISNULL(SUM(WHOutQty),0) AS [Qty Booked-O],
ISNULL(CAST(SUM(WHOutTotal)/NULLIF(SUM(WHOutQty),0) AS DECIMAL(10,2)),0) AS [Average Booking-O],
ISNULL(SUM(InvoiceTotal),0) AS [Invoice Total],
ISNULL(SUM(InvoiceQty),0) AS [Qty Invoiced],
ISNULL(CAST(SUM(InvoiceTotal)/NULLIF(SUM(InvoiceQty),0) AS DECIMAL(10,2)),0) AS [Average Invoice],
ISNULL(CAST(SUM(InvoiceQty)/NULLIF(SUM(WHOutQty),0) AS DECIMAL(10,4)),0) AS [Ratio Closed Quantity],
ISNULL(CAST(SUM(InvoiceTotal)/NULLIF(SUM(WHOutTotal),0) AS DECIMAL(10,4)),0) AS [Ratio Closed Revenue],
ISNULL(SUM(WHOutTotal),0) - ISNULL(SUM(InvoiceTotal),0) AS [Revenue Lost],
ISNULL(SUM(RevenueLastYear),0) AS [Revenue Last Year]
FROM
(
SELECT * FROM CreatedStream
UNION ALL
SELECT * FROM WHStream
UNION ALL
SELECT * FROM LastYearStream
) Combined
GROUP BY
Operator,
[Year],
MonthNumber, -- still needed for correct month order
[Month]
ORDER BY
[Year],
MonthNumber, -- ensures January → December order
Operator;
END
GO