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
|
CREATE TABLE #tmp (reference char(5), annee int, mois int, prix smallmoney)
INSERT INTO #tmp
--reference | annee | mois | prix
SELECT 'kxz32', 2006, 1, 32 UNION ALL
SELECT 'kxz32', 2006, 3, 35 UNION ALL
SELECT 'kxz32', 2006, 4, 33 UNION ALL
SELECT 'tu453', 2006, 1, 12 UNION ALL
SELECT 'tu453', 2006, 2, 15 UNION ALL
SELECT 'tu453', 2006, 5, 11 UNION ALL
SELECT 'tu453', 2006, 7, 16
GO
SELECT t2.reference, t2.annee, t2.mois --, t1.prix
, COALESCE(t1.prix, (SELECT ttt1.prix FROM #tmp ttt1
JOIN (SELECT reference, MAX(annee + CAST(RIGHT('0'+CAST(mois as varchar(2)), 2) as int)) as MaxMois FROM #tmp GROUP BY reference) ttt2
ON ttt1.reference = ttt2.reference AND ttt1.annee + CAST(RIGHT('0'+CAST(ttt1.mois as varchar(2)), 2) as int) = ttt2.MaxMois
WHERE ttt1.reference = t2.reference))
FROM (#tmp t1
RIGHT JOIN (
SELECT tt1.reference,
tt2.annee,
tt2.mois
FROM #tmp tt1
CROSS JOIN (SELECT DISTINCT annee, mois FROM #tmp) tt2) t2 ON t1.reference = t2.reference AND t1.annee = t2.annee AND t1.mois = t2.mois )
ORDER BY t2.reference, t2.annee, t2.mois
GO
DROP TABLE #tmp
GO |
Partager