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 44 45 46 47 48 49 50 51 52 53
| declare @DtDeb datetime = '16/03/2013 00:00:00', @DtFin datetime = '06/12/2013 00:00:00', @RegroupementCalendaire integer =3-- 0 jour , 1 semaine , 2 mois , 3 période complète
DECLARE @ecart int = (datepart(dy,@DtDeb) - Datepart(DY,@DtFin))
;WITH CALENDRIER (DateD,DateF) AS
(
SELECT
@DtDeb as DateTime,
(case
when @RegroupementCalendaire=0 then DATEADD(day, +1, @DtDeb)
when @RegroupementCalendaire=1 then DATEADD(day, -1, DATEADD(week, DATEDIFF(week, 0, @DtDeb) + 1, 0))
when @RegroupementCalendaire=2 then DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, @DtDeb) + 1, 0))
when @RegroupementCalendaire=3 then @DtFin
end) as Datetime
UNION ALL
SELECT (case
when (@RegroupementCalendaire=0 AND DateD <= @DtFin) then DATEADD(d,1,DateD)
when (@RegroupementCalendaire=1 AND DateD < @DtFin) then DATEADD(WW,1,DATEADD(day,-datepart(dw,DateD)+1,DateD))
when (@RegroupementCalendaire=2 AND DateD < @DtFin) then DATEADD(MM,1,DATEADD(month, DATEDIFF(month, 0, DateD), 0))
when @RegroupementCalendaire=3 then @DtDeb
end),
(case
when (@RegroupementCalendaire=0 AND DateF <= @DtFin) then DATEADD(d,1,DateF)
when (@RegroupementCalendaire=1 AND DateF < @DtFin) then DATEADD(WW,1,DATEADD(day, -1, DATEADD(week, DATEDIFF(week, 0, DateD) + 1, 0)))
when (@RegroupementCalendaire=2 AND DateF < @DtFin) then DATEADD(MM,1,DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, DateD) + 1, 0)))
when @RegroupementCalendaire=3 then @DtFin
end)
FROM CALENDRIER
WHERE case
when (@RegroupementCalendaire=0 AND DateD <= @DtFin) then DateF
when (@RegroupementCalendaire=1 AND DateD <= @DtFin) then DateF
when (@RegroupementCalendaire=2 AND DateF <= @DtFin) then DateF
end < @DtFin
)
SELECT DateD as DtDeb,
DateF as DtFin,
dbo.fn_localTimetoUTC(DateD) as DtDeb_UTC,
dbo.fn_localTimetoUTC(DateF) as DtFin_UTC,
(case
when (@RegroupementCalendaire=0 AND DateD <= @DtFin) then FORMAT((CONVERT(datetime,DateD,103)),'d','fr-FR')
when (@RegroupementCalendaire=1 AND DateD <= @DtFin) then CONVERT(nvarchar,DATEPART(YEAR,DateD))+' - S'+(CONVERT(nvarchar,DATEPART(WEEK, DateD)))
when (@RegroupementCalendaire=2 AND DateD <= @DtFin) then (CONVERT(nvarchar,(DATEPART(MM, DateD)))+' - '+CONVERT(nvarchar,DATEPART(YEAR,DateD)))
when (@RegroupementCalendaire=3 ) then CONVERT(nvarchar,'Période')
END ) as Champs
INTO #Calendar
FROM CALENDRIER
OPTION (MAXRECURSION 0)
UPDATE #Calendar SET DtFin = @DtFin,
DtFin_UTC = dbo.fn_LocalTimeToUTC(@DtFin)
WHERE #Calendar.DtFin > @DtFin
SELECT * FROM #Calendar
DROP TABLE #Calendar |
Partager