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
| WITH T AS
(
SELECT 1 IdEnr, 'carottes' NomLegume, 3.4 Cout, convert(smalldatetime, '06/01/2008') dt union
SELECT 2, 'Patates', 2.5, convert(smalldatetime, '06/01/2008') union
SELECT 3, 'Salade', 1.2, convert(smalldatetime, '06/02/2008') union
SELECT 4, 'carottes', 3.6, convert(smalldatetime, '06/05/2008') union
SELECT 5, 'Salade', 1.4, convert(smalldatetime, '06/05/2008') union
SELECT 6, 'patates', 2.8, convert(smalldatetime, '06/08/2008') union
SELECT 7, 'patates', 2.8, convert(smalldatetime, '06/08/2008') union
SELECT 8, 'carottes', 2.8, convert(smalldatetime, '06/20/2008') union
SELECT 9, 'salade', 1.1, convert(smalldatetime, '06/25/2008') union
SELECT 10, 'patates', 3.0, convert(smalldatetime, '06/30/2008')
),
T2 AS
(
SELECT T.*, rank() OVER (PARTITION BY T.NomLegume ORDER BY T.dt DESC, IdEnr DESC) rk
FROM T
WHERE T.dt <= convert(smalldatetime, '06/20/2008') -- Ici votre paramètre, attention au format 'MM/JJ/AAAA'
)
SELECT T2.IdEnr, T2.NomLegume, T2.Cout, T2.dt
FROM T2
WHERE rk = 1
IdEnr NomLegume Cout dt
----------- --------- --------------------------------------- -----------------------
8 carottes 2.8 2008-06-20 00:00:00
7 patates 2.8 2008-06-08 00:00:00
5 Salade 1.4 2008-06-05 00:00:00
(3 row(s) affected) |
Partager