How many people test-drove each vehicle?
A customer can drive the same car twice, and a sale can have multiple buyers. The report needs separate counts for visits and people, with each total tied to the individual car in inventory.
I count test drives and buyers separately before combining the totals for each car. That way, a sale with two buyers cannot make the same test drive appear twice.
Vehicle activity by inventory item
;WITH TestDriveCounts AS (
SELECT
[Inventory code],
COUNT(DISTINCT [Test drive code]) AS TestDrives,
COUNT(DISTINCT [Customer number]) AS TestDriveCustomers
FROM TESTDRIVE
GROUP BY [Inventory code]
),
BuyerCounts AS (
SELECT
[Inventory code],
COUNT(DISTINCT [Customer number]) AS Buyers
FROM SALES
GROUP BY [Inventory code]
)
SELECT
i.[Inventory code],
i.[vin number],
v.[Year],
v.Make,
v.Model,
COALESCE(td.TestDrives, 0) AS TestDrives,
COALESCE(td.TestDriveCustomers, 0) AS TestDriveCustomers,
COALESCE(b.Buyers, 0) AS Buyers
FROM INVENTORY AS i
LEFT JOIN VEHICLE AS v ON v.[vehicle code] = i.[vehicle code]
LEFT JOIN TestDriveCounts AS td ON td.[Inventory code] = i.[Inventory code]
LEFT JOIN BuyerCounts AS b ON b.[Inventory code] = i.[Inventory code]
ORDER BY i.[Inventory code];What the counts tell us
Repeat drives
If one customer comes back for a second drive, that adds another test drive. The number of distinct test-drive customers stays at 1.
Cars with no activity
Starting from inventory keeps every car in the report. A car with no test-drive or sales records shows 0 in the corresponding counts.
Purchase conversion
The buyer count includes everyone recorded on the sale. To measure test-drive conversion, I would match customers across the two tables for the same car and check that the drive came before the sale.