Get most recent row inserted with the least specificity

I'll first explain the data model then the desired results and what I have tried.

I have vehicles and sales tables:

CREATE TABLE VEHICLE
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    BRAND INT NOT NULL,
    MODEL VARCHAR(255),
    VERSION VARCHAR(255),

    UNIQUE(BRAND, MODEL, VERSION),
    FOREIGN KEY(BRAND) REFERENCES BRAND(ID)
)

CREATE TABLE SALES 
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    VEHICLE_ID INT NOT NULL,
    DATE DATE NOT NULL,
    SALE INT NOT NULL,
    CREATED_DATE DATETIME NOT NULL DEFAULT GETDATE(),

    FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE(ID)
)

This way I can insert several entries for the same vehicle for the same date (when I want to update, I insert a new row)

INSERT INTO SALES (VEHICLE_ID, DATE, SALE, USER_ID)   
VALUES (1, '2018-01-01', 2, 3) -- then later i update by inserting a new row
       (1, '2018-01-01', 4, 3)

I want to retrieve the last sale inserted for a specific date range (using the DATE), then filter for a specific BRAND, or model or version.

I got it working by doing this

SELECT 
    S.DATE AS date, SUM(S.SALE_PROJECTION) AS saleProjection
FROM 
    SALE_PROJECTION S,
    (SELECT MAX(ID) AS id 
     FROM SALE_PROJECTION 
     WHERE DATE >= CAST(@dateStart AS DATE) 
       AND DATE <= CAST(@dateEnd AS DATE) 
     GROUP BY DATE, VEHICLE_ID) S_M,
    VEHICLE V
WHERE 
    1 = 1
    AND S.ID = S_M.ID
    AND S.VEHICLE_ID = V.ID
    AND V.BRAND = 1
    AND V.MODEL = 'A6'
    AND V.VERSION = '1.0'
GROUP BY S.DATE
ORDER BY DATE

The problem is i want to get the sales for the brand 1 that has the least specificity, meaning: If i have 3 vehicles:

(1, 'A3', '1.0'),
(1, 'A3', '2.0'),
(1, 'A3', null),
(1, null, null);
  • if i insert a sale (1, 2018-01-01, 2, 3)
  • if i insert a sale (2, 2018-01-01, 3, 3) -- the sum for 2018-01-01 would be 5
  • but then insert a sale for (2, 2018-01-01, 3, 3) -- the sum for 2018-01-01 has to be 3, because it's the last inserted with the least specifity

But the oposite must be true as well

  • if i insert a sale (3, 2018-01-01, 4, 3)
  • then insert a sale for (1, 2018-01-01, 1, 3)
  • then insert a sale for (2, 2018-01-01, 1, 3)
  • the sum for 2018-01-01 has to be 2, because it's the last inserted

The most general combination of Brand, Model, Version has to "hide" the most specific.

Do i need to change my data model? or this is possible? I can give more examples if needed.

Thanks in advance