Analyse & Programmation

MS-SQL calendrier dynamique

Comment créer un calendrier dynamique dans la langue de son choix, remplir une table calendrier ou utiliser une table de dimension calendrier dans un entrepôt de données.

1. La langue utilisée pour la session courante avec MS - SQL c’est très simple seulement utiliser la commande "Set language" avec l’argument "french", "us_english", "italian", ect…  Chez nous les serveurs sont en anglais US par défaut donc pour l’affichage en français du mois et de la journée j’utilise cette commande.
2. J’utilise une table de type variable mais vous pouvez modifier le script pour utiliser une table temporaire ou une table régulière. La table de type variable est pratique si la table est utilisée de façon dynamique et qu'elle n’est plus utile par la suite.
3. J’utilise un astuce pour créer une clé que je réutilise dans les tables de fait pour créer la clé étrangère. Un nombre entier qui est au format YYYYMMDD. Je bâtie cette clé avec l’année multiplier par 10000 plus le mois multiplier par 100 + le jour du mois. (Year(@MyDate)*10000) + (Month(@MyDate)*100) + Day(@MyDate)
4. La fonction suivante donne la date avec l’heure à zéro. Cast(Floor(Cast(@MyDate AS FLOAT))AS DATETIME)
5. J’ajoute un champs qui détermine les dates de fin de semaine. Utile dans certain calcul. Case When Datepart(weekday,@MyDate) in (1,7) Then 1 Else 0 End

Plusieurs autres fonctions de date existe et s’ajoute facilement au script suivant.

L'exemple qui suit va chercher une période de 20 ans (10 ans avant et 10 après l’année en cours).

Set language 'french'
Declare @MyDate datetime
Declare @x int
Declare @MyCalTable table (id_MyCalTable Int Primary key, CalendarDate datetime,
YearNumeric Int, MonthNumeric Int,
MonthName varchar(20), MonthDayNumeric Int,
WeekDayNumeric Int, WeekDayName varchar(12),
IsWeekend TinyInt, LastDateOfWeek smalldatetime,
DayOfYearNumeric Int, OrderAbs Int)

Set @MyDate = Dateadd(yy, Datediff(yy,0,Getdate())-10, 0)

--print 'Ma date de départ est ' + cast(@MyDate as varchar(50))
Set @x = 1

While @MyDate < Dateadd(Year,10,Getdate())
begin
	INSERT INTO @MyCalTable
	SELECT (Year(@MyDate)*10000) + (Month(@MyDate)*100) + Day(@MyDate),
		Cast(Floor(Cast(@MyDate AS FLOAT))AS DATETIME),
		Year(@MyDate),
		Month(@MyDate),
		Datename(month,@MyDate),
		Day(@MyDate),
		Datepart(weekday,@MyDate),
		Datename(dw,@MyDate),
		Case When Datepart(weekday,@MyDate) in (1,7) Then 1 Else 0 End,
		CAST(FLOOR(CAST(Dateadd(Day, 7-Datepart(weekday,@MyDate) ,@MyDate) 
                    AS FLOAT))AS DATETIME),
		Datepart(dy,@MyDate),
		@x
	Set @MyDate = Dateadd(Day,1,@MyDate)
	Set @x = @x + 1
end

Et voilà!

Select * From @MyCalTable