Here is yet another way of achieving the same result, but using conditional max/min functions windowed over an ordinal. The ordinal can be be set up based on whatever columns fits the purpose, but in this case I believe the OP intends them to be Date
and Time
.
DROP TABLE IF EXISTS #t;
CREATE TABLE #t (
Number INT,
Price MONEY,
Type CHAR(2),
Date DATE,
Time TIME(0)
);
INSERT INTO #t VALUES
(23456, 0.666, 'SV', '2014/02/02', '10:00:02'),
(23457, 1.4 , 'EC', '2014/02/02', '09:50:45'),
(23658, 2.5 , 'EC', '2014/02/02', '09:50:45'),
(23660, 2.5 , 'EC', '2014/02/02', '09:50:48'),
(23465, 0.669, 'SV', '2014/02/02', '09:36:34'),
(23456, 0.665, 'SV', '2014/02/02', '08:00:02'),
(23457, 1.3 , 'EC', '2014/02/02', '07:50:45'),
(23658, 2.4 , 'EC', '2014/02/02', '07:50:45'),
(23660, 2.4 , 'EC', '2014/02/02', '07:50:48'),
(23465, 0.668, 'SV', '2014/02/02', '07:36:34'), -- which one of these?
(23465, 0.670, 'SV', '2014/02/02', '07:36:34'); --
WITH time_ordered AS (
SELECT *, DENSE_RANK() OVER (ORDER BY Date, Time) AS ordinal FROM #t
)
SELECT
*,
CASE WHEN Type = 'EC'
THEN MAX(CASE WHEN ordinal = preceding_non_EC_ordinal THEN Price END)
OVER (PARTITION BY preceding_non_EC_ordinal ORDER BY ordinal ASC) END AS preceding_price,
CASE WHEN Type = 'EC'
THEN MIN(CASE WHEN ordinal = following_non_EC_ordinal THEN Price END)
OVER (PARTITION BY following_non_EC_ordinal ORDER BY ordinal DESC) END AS following_price
FROM (
SELECT
*,
MAX(CASE WHEN Type <> 'EC' THEN ordinal END)
OVER (ORDER BY ordinal ASC) AS preceding_non_EC_ordinal,
MIN(CASE WHEN Type <> 'EC' THEN ordinal END)
OVER (ORDER BY ordinal DESC) AS following_non_EC_ordinal
FROM time_ordered
) t
ORDER BY Date, Time
Note that the example given by the OP has been extended to show that interspersed sequences of EC
yeild the intended result. The ambiguity introduced by the earliest two consecutive rows with type SV
will in this case lead to the maximum value being picked. Setting up the ordinal to include the Price
is a possible way to change this behavior.
An SQLFiddle can be found here: http://sqlfiddle.com/#!18/85117/1