1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| DECLARE @t TABLE
(
DateTrade DATETIME,
Value1 INT,
Value2 INT
);
INSERT INTO @t VALUES ('01/01/2010 09:00:00', 2, 34);
INSERT INTO @t VALUES ('01/01/2010 09:01:00', 10, 45);
INSERT INTO @t VALUES ('01/01/2010 09:02:00', 8, 21);
INSERT INTO @t VALUES ('01/01/2010 09:03:00', 28, 32);
INSERT INTO @t VALUES ('01/01/2010 09:04:00', 36, 31);
INSERT INTO @t VALUES ('01/01/2010 09:05:00', 56, 7);
INSERT INTO @t VALUES ('01/01/2010 09:06:00', 14, 1);
WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateTrade) AS num,
*
FROM @t
)
SELECT
T2.DateTrade,
CASE T2.num % 2 WHEN 0 THEN CASE
WHEN T.Value1 > T2.Value1 THEN T.Value1
WHEN T.Value1 < T2.Value1 THEN T2.Value1
ELSE T.Value1
END
ELSE NULL
END AS Value1,
CASE T2.num % 2 WHEN 0 THEN CASE
WHEN T.Value2 > T2.Value2 THEN T2.Value2
WHEN T.Value2 < T2.Value2 THEN T.Value2
ELSE T.Value2
END
ELSE NULL
END AS Value2
FROM CTE AS T
INNER JOIN CTE AS T2
ON T.num = T2.num - 1
WHERE T2.num % 2 = 0; |
Partager