CODEWITHSUNDEEP
sqlsqlitedatetimejoingroup-by
foxever
foxever

Reputation: 23

How to find closest time?

B.Update is the closet time before RefuelTime. Have tried left join, but there is no common field between Table A and B. Return Cartesian product.

Table A, RefuelRecord:

RefuelId RefuelTime
1 2022-02-01 12:15:00
2 2022-03-01 12:15:00
3 2022-04-01 12:15:00

Table B, GasUpdateRecord :

UpdateTime, Price
2022-01-20 0:00:00 9.0
2022-02-20 0:00:00 8.1
2022-03-20 0:00:00 7.2

Result table:
1 2022-02-01 12:15:00 2022-01-20 0:00:00
2 2022-03-01 12:15:00 2022-02-20 0:00:00
3 2022-04-01 12:15:00 2022-03-20 0:00:00

SQL:

Create table RefuelRecord (RefuelId int, RefuelTime datetime);
INSERT INTO RefuelRecord VALUES(1, '2022-02-01 12:15:00');
INSERT INTO RefuelRecord VALUES(2, '2022-03-01 12:15:00');
INSERT INTO RefuelRecord VALUES(3, '2022-04-01 12:15:00');

Create table PriceUpdateRecord (UpdateTime datetime, Price double);
INSERT INTO PriceUpdateRecord VALUES('2022-01-20 12:15:00', 9.0);
INSERT INTO PriceUpdateRecord VALUES('2022-02-20 12:15:00', 8.0);
INSERT INTO PriceUpdateRecord VALUES('2022-03-20 12:15:00', 7.0);

Upvotes: 1

Views: 57

Answers (1)

forpas
forpas

Reputation: 164139

Do a LEFT join of RefuelRecord to PriceUpdateRecord on the condition that UpdateTime should be less than RefuelTime.
Then use aggregation and SQLite's feature of bare columns to get for each RefuelId the row from PriceUpdateRecord with the max UpdateTime:

SELECT r.RefuelId, 
       r.RefuelTime,
       MAX(p.UpdateTime) UpdateTime,
       p.Price
FROM RefuelRecord r LEFT JOIN PriceUpdateRecord p
ON p.UpdateTime < r.RefuelTime
GROUP BY RefuelId;

See the demo.

Upvotes: 1

Related Questions