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.
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