SQL - Time Table With Stored Procedure (SQL Server)

• Script for Table and Stored Procedure creation - 2 Kb

Introduction

In many project, we can use a time table (for example in HR domain).

This SQL Script is used to create the Time Table and the Stored Procedure that completes automatically a range between 2 given dates.

This Script is based on SQL Server and based on french holiday, for the other countries, the public holidays part must be modified.

Time Table

The details of the Table created by the Script :

• TEMPS_ID (Type : smallINt, Propriété : NOT NULL) : ID of the Date
• TEMPS_DATE (Type : smalldatetime, Propriété : NOT NULL) : Date
• TEMPS_ANNEE (Type : smallINt, Propriété : NOT NULL) : Year of The Date
• TEMPS_MOIS (Type : tINyINt, Propriété : NOT NULL) : Month of the Date
• TEMPS_JOUR (Type : tINyINt, Propriété : NOT NULL) : Day of The Date
• TEMPS_JOURDESEMAINE (Type : tINyINt, Propriété : NOT NULL) : WeekDay N° of the Date
• TEMPS_JOURDELANNEE (Type : smallINt, Propriété : NOT NULL) : N° of the Day in the year of the Date
• TEMPS_JOUROUVRE (Type : char(1), Propriété : NOT NULL) : Flag to know if the day is open or not
• TEMPS_TRIMESTRE (Type : tINyINt , Propriété : NOT NULL) : N° of the quarter of the Date
• TEMPS_SAISON (Type : char(10), Propriété : NOT NULL) : Season of the Date
• TEMPS_FERIE (Type : varchar(20), Propriété : NULL) : If the date is public holiday, which one ?

The Stored Procedure

The Stored Procedure is used to add data into the Time Table, in fact with this SP we can clear all data of the existing Time Table and just execute this SP.

The SP is in the SQL source code.

Related Links

The french version (complete version) of this article is visible at this link :

 - http://www.asp-php.net/scripts/asp.net/table_sql_temps.php

Updates

I modify the SQL script for add functions (from Frédéric BROUARD) to calculate exactly the seasons, You can see these function at the adress :

 - http://sqlpro.developpez.com/MSSQLServer_fonctions/UDF.html#FN_SEASONS