By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE SCHEMA algotrading
CREATE TABLE algotrading.optiontracking (
ConId INT,
TimeLog DATETIME,
LastPrice NUMERIC(19,2),
Ask NUMERIC(19,2),
Bid NUMERIC(19,2)
);
INSERT algotrading.optiontracking
VALUES
(1, '2020-06-03 10:18:43', 10.40, 0.52, 0.55),
(1, '2020-06-03 10:20:41', 10.30, 0.55, 0.42),
(1, '2020-06-03 10:23:54', 10.45, 0.60, 0.58),
(1, '2020-06-03 10:26:26', 10.42, 0.52, 0.51),
(1, '2020-06-03 10:28:57', 10.48, 0.51, 0.50),
(1, '2020-06-04 10:25:57', 10.48, 0.65, 0.64),
(1, '2020-06-04 10:26:57', 10.48, 0.80, 0.79),
(2, '2020-06-03 10:18:43', 12.10, 1.36, 1.33),
(2, '2020-06-03 10:20:41', 12.05, 1.38, 1.35),
(2, '2020-06-03 10:23:54', 12.03, 1.33, 1.32),
(2, '2020-06-03 10:26:26', 12.13, 1.25, 1.22),
(2, '2020-06-03 10:28:57', 12.15, 1.20, 1.19)
SELECT OT.ConID, OT.TimeLog, OT.Ask, MaxBid.SellPrice, MaxBid.SellPriceTime
FROM algotrading.optiontracking as OT
OUTER APPLY (
SELECT TOP 1 OT2.Bid AS SellPrice, OT2.TimeLog AS SellPriceTime
FROM algotrading.optiontracking OT2
WHERE OT2.ConID = OT.ConID
AND OT2.TimeLog > OT.TimeLog -- Later
AND OT2.TimeLog < DATEADD(day, 1, CONVERT(DATE, OT.TimeLog)) -- Before start of next day
ORDER BY OT2.Bid DESC -- Max Bid
) MaxBid
WHERE OT.TimeLog < '2020-07-01' -- Is this what was intended?
| ConID | TimeLog | Ask | SellPrice | SellPriceTime |
|---|---|---|---|---|
| 1 | 2020-06-03 10:18:43.000 | 0.52 | 0.58 | 2020-06-03 10:23:54.000 |
| 1 | 2020-06-03 10:20:41.000 | 0.55 | 0.58 | 2020-06-03 10:23:54.000 |
| 1 | 2020-06-03 10:23:54.000 | 0.60 | 0.51 | 2020-06-03 10:26:26.000 |
| 1 | 2020-06-03 10:26:26.000 | 0.52 | 0.50 | 2020-06-03 10:28:57.000 |
| 1 | 2020-06-03 10:28:57.000 | 0.51 | null | null |
| 1 | 2020-06-04 10:25:57.000 | 0.65 | 0.79 | 2020-06-04 10:26:57.000 |
| 1 | 2020-06-04 10:26:57.000 | 0.80 | null | null |
| 2 | 2020-06-03 10:18:43.000 | 1.36 | 1.35 | 2020-06-03 10:20:41.000 |
| 2 | 2020-06-03 10:20:41.000 | 1.38 | 1.32 | 2020-06-03 10:23:54.000 |
| 2 | 2020-06-03 10:23:54.000 | 1.33 | 1.22 | 2020-06-03 10:26:26.000 |
| 2 | 2020-06-03 10:26:26.000 | 1.25 | 1.19 | 2020-06-03 10:28:57.000 |
| 2 | 2020-06-03 10:28:57.000 | 1.20 | null | null |