How to add custom YoY field to output?

Question

I'm attempting to determine the YoY growth by month, 2017 to 2018, for number of Company bookings per property.

I've tried casting and windowed functions but am not obtaining the correct result.

Example Table 1: Bookings

    BookID  Amnt  BookType  InDate  OutDate  PropertyID  Name  Status
    -----------------------------------------------------------------
    789555 $1000  Company  1/1/2018  3/1/2018  22111   Wendy   Active       
    478141 $1250   Owner   1/1/2017  2/1/2017  35825   John  Cancelled 

There are only two book types (e.g., Company, Owner) and two Book Status (e.g., Active and Cancelled).

Example Table 2: Properties

   Property ID    State    Property Start Date    Property End Date
---------------------------------------------------------------------
   33111         New York       2/3/2017                
   35825         Michigan       7/21/2016

The Property End Date is blank when the company still owns it.

Example Table 3: Months

   Start of Month          End of Month
-------------------------------------------
    1/1/2018                1/31/2018

The previous developer created this table which includes a row for each month from 2015-2020.

I've tried many various iterations of my current code and can't even come close.

Desired Outcome

I need to find the YoY growth by month, 2017 to 2018, for number of Company bookings per property. The stakeholder has requested the output to have the below columns:

Month Name  Bookings_Per_Property_2017  Bookings_Per_Property_2018  YoY                           
-----------------------------------------------------------------------

The number of Company bookings per property in a month should be calculated by counting the total number of active Company bookings made in a month divided by the total number of properties active in the month.

Answer 1

Here is a solution that should be close to what you need. It works by:

• LEFT JOINing the three tables; the important part is to properly check the overlaps in date ranges between months(StartOfMonth, EndOfMonth), bookings(InDate, OutDate) and properties(PropertyStartDate, PropertyEndDate): you can have a look at this reference post for general discussion on how to proceed efficiently

• aggregating by month, and using conditional COUNT(DISTINCT ...) to count the number of properties and bookings in each month and year. The logic implicitly relies on the fact that this aggregate function ignores NULL values. Since we are using LEFT JOINs, we also need to handle the possibility that a denominator could have a 0 value.

Notes:

• you did not provide expected results so this cannot be tested

• also, you did not explain how to compute the YoY column, so I left it alone; I assume that you can easily compute it from the other columns

Query:

SELECT
    MONTH(m.StartOfMonth) AS [Month],
    COUNT(DISTINCT CASE WHEN YEAR(StartOfMonth) = 2017 THEN b.BookID END) 
        / NULLIF(COUNT(DISTINCT CASE WHEN YEAR(StartOfMonth) = 2017 THEN p.PropertyID END), 0) 
        AS Bookings_Per_Property_2017,
    COUNT(DISTINCT CASE WHEN YEAR(StartOfMonth) = 2018 THEN b.BookID END) 
        / NULLIF(COUNT(DISTINCT CASE WHEN YEAR(StartOfMonth) = 2018 THEN p.PropertyID END), 0) 
        AS Bookings_Per_Property_2018
FROM months m
LEFT JOIN bookings b
    ON  m.StartOfMonth <= b.OutDate 
    AND m.EndOfMonth   >= b.InDate
    AND b.status = 'Active'
    AND b.BookType = 'Company'
LEFT JOIN properties p
    ON  m.StartOfMonth <= COLAESCE(p.PropertyEndDate, m.StartOfMonth)
    AND m.EndOfMonth   >= p.PropertyStartDate
GROUP BY MONTH(m.StartOfMonth)