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
| WITH T0 AS
(
SELECT ...
CASE dbo.VENTE.TYPEPIECE
WHEN '2' THEN
CASE WHEN dbo.VENTE.TYPEPIECE = '2' AND dbo.COMMANDE.DELAICALCULE IS NULL
THEN DATEADD(day, 15, dbo.VENTE.DATECOMMANDE)
ELSE dbo.COMMANDE.DELAICALCULE
END
WHEN '3' THEN
CASE WHEN dbo.VENTE.TYPEPIECE = '3'
THEN dbo.VENTE.DATEPIECE
END
WHEN '4' THEN
CASE WHEN dbo.VENTE.TYPEPIECE = '4' AND dbo.VENTE.DATEBL IS NULL
THEN dbo.VENTE.DATEFACTURE
ELSE dbo.VENTE.DATEBL
END
END AS MA_DATE
FROM ...
)
SELECT *,
datepart(iso_week, @DATE) AS NUM_SEMAINE,
CASE
WHEN datepart(iso_week, MA_DATE) = 1 AND MONTH(MA_DATE) = 12 THEN YEAR(MA_DATE) + 1
WHEN datepart(iso_week, MA_DATE) = 53 AND MONTH(MA_DATE) = 1 THEN YEAR(MA_DATE) - 1
ELSE YEAR(MA_DATE)
END AS YEAR_WEEK
FROM T0 |
Partager