SQL for returning number of days since last availability, grouped by category?

I'm using SQL and I have some dummy car dealership data that for a given dealership tells me whether a particular make is currently being sold there, and if not, when the last time that make was available at that dealership. An example of a few rows of data for one dealership looks like this, focusing only on the fields of interest:

What I'm trying to do is return one row per dealership that tells me for given car makes, whether they are currently available and if not, how many days has it been since they were last available

using dealership ID 612 as an example, it would return something like this:

Where BMW and Mercedes are 0 since at least one of each is available right now, Audi is 61 as it has been that many days since an Audi was last available at the dealership (audi with most recent sold_date) and Ford is Null as a ford has never been sold at this particular dealership before.

Well you can always simplify things and just use the with clause as in:

WITH LatestAvailability AS (
    SELECT 
        dealership_ID,
        make,
        MAX(CASE WHEN Available = 'Yes' THEN 1 ELSE 0 END) AS is_available,
        MAX(sold_date) AS last_sold_date
    FROM dealership_data
    GROUP BY dealership_ID, make
),
DaysSinceLastAvailable AS (
    SELECT
        dealership_ID,
        make,
        CASE
            WHEN is_available = 1 THEN 0
            WHEN last_sold_date IS NOT NULL THEN DATEDIFF(DAY, last_sold_date, GETDATE())
            ELSE NULL
        END AS days_since_last_available
    FROM LatestAvailability
),
PivotedData AS (
    SELECT
        dealership_ID,
        make,
        days_since_last_available
    FROM DaysSinceLastAvailable
)
SELECT
    pd.dealership_ID,
    MAX(CASE WHEN pd.make = 'BMW' THEN pd.days_since_last_available ELSE NULL END) AS BMW,
    MAX(CASE WHEN pd.make = 'Audi' THEN pd.days_since_last_available ELSE NULL END) AS Audi,
    MAX(CASE WHEN pd.make = 'Mercedes Benz' THEN pd.days_since_last_available ELSE NULL END) AS `Mercedes Benz`,
    MAX(CASE WHEN pd.make = 'Ford' THEN pd.days_since_last_available ELSE NULL END) AS Ford
FROM PivotedData pd
GROUP BY pd.dealership_ID;

Break through:

1. Filter the dataset: For every dealership and make, find out whether the car is in stock now or the most recent sold_date for out-of-stock cars.
2. Days since last in stock: Calculate date difference between today and the most recent sold_date for not currently in stock cars.
3. Pivot the data: Rows to columns on each car make, respective values 0, the number of days or NULL.