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
| DECLARE @t TABLE
(
[date] DATETIME,
valeur INT,
tag CHAR(3)
)
INSERT INTO @t (date,valeur,tag) VALUES ('20090101',112,'P1');
INSERT INTO @t (date,valeur,tag) VALUES ('20090101',100,'P2');
INSERT INTO @t (date,valeur,tag) VALUES ('20090102',150,'P1');
INSERT INTO @t (date,valeur,tag) VALUES ('20090102',185,'P2');
INSERT INTO @t (date,valeur,tag) VALUES ('20090103',100,'R1');
INSERT INTO @t (date,valeur,tag) VALUES ('20090103',500,'R34');
WITH CTE_NUM_LINE
AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY date ORDER BY date) AS num_op,
*
FROM @t
WHERE tag IN ('P1','P2')
)
SELECT
[date],
SUM(CASE num_op WHEN 1 THEN valeur
ELSE -1 * valeur
END
) AS valeur,
'P3' AS tag
FROM CTE_NUM_LINE
GROUP BY [date] |
Partager