Molto spesso può capitare di dover lavorare con le date ed effettare calcoli su di esse, come estrarre i giorni festivi, le festività o calcolare quanti giorni sono trascrosi tra un'intervallo di date ed un altro.
Tutte operazioni che possono essere tranquillamente compiute con i consueti comandi DatePart(), DateFirst() ed alcune Case.
Spesso, però, queste istruzioni possono risultare scomode e poco leggibili.
Facciamo qualche esempio: supponiamo di voler ottenere il numero dei giorni trascorsi dal 1 gennaio 2007 al 1 maggio 2007
Una semplice funzione datediff sarebbe sufficiente
select datediff(d, '20070101', '20070501')
/* Output
120
*/
Ma se volessimo sapere quanti e quali sono quelli festivi? E per le festività infrasettimanali?
Le cose si complicherebbero molto soprattutto se il risultato dovesse essere messo in join con i dati di una nostra ipotetica tabella ordini, ad esempio.
Questa funzione è stata pensata proprio per agevolare tali operazioni mettendo a disposizione una tabella di appoggio con cui lavorare in modo semplice.
Viene restituita, infatti, una tabella nella quale troveremo, oltre all'intervallo di date, una serie di campi che possono tornare utili.
Ecco la struttura:
Anno Smallint
Mese tinyint
Giorno tinyint
realDate varchar(10)
NomeGiorno varchar(10) --
Descrizione varchar(100) --Descrizione del giorno
Festivo bit --Indica se è un festivo o no
Days int -- Numero dei giorni trascorsi a partire dalla data di start
Per funzionare al meglio è necessario (ma non indispensabile) creare una tabella d'appoggio contenente le festività o le semplici ricorrenze, tanto per rendere più completo il nostro calendario.
Ecco lo script:
-- Creo la tabella con le festività
Create Table myFeste
(
Mese smallint,
Giorno tinyint,
Descrizione varchar(100),
Festivo bit
)
-- Aggiungo le festività
insert into myFeste values(1, 1, 'Primo dell''anno', 1)
insert into myFeste values(1, 6, 'Epifania', 1)
insert into myFeste values(4, 25, '25 Aprile', 1)
insert into myFeste values(5, 1, 'Festa dei lavoratori', 1)
insert into myFeste values(5, 26, 'Compleanno di Sandro', 0)
insert into myFeste values(6, 2, 'Festa della repubblica', 1)
insert into myFeste values(15, 8, 'Ferragosto', 1)
insert into myFeste values(1, 11, 'Tutti i santi', 1)
insert into myFeste values(8, 12, 'Immacolata', 1)
insert into myFeste values(12, 25, 'SS. Natale', 1)
insert into myFeste values(12, 26, 'S. Stefano', 1)
A questo punto è necessario creare la nostra funzione come da script seguente:
-- ***************************************************************************
-- descr.: Creazione di una tabella contenente un'intervallo di date
-- author: Sandro Bizioli
--
-- Parametri
--
-- NAME TYPE
-- @datePart VARCHAR(5) = NULL, mandatory
-- @number INT = NULL, optional
-- @dateStart SMALLDATETIME = NULL, mandatory
--
-- Return (Table)
-- NAME TYPE
-- Anno SmallInt Intero indicante l'anno
-- Mese TinyInt Mese (1-12)
-- Giorno TinyInt Giorno del mese (1-31)
-- miaData SmallDateTime Data nel formato esteso
-- NomeGiorno Varchar(10) Nome del giorno della settimana
-- Descrizione Varchar(100) Descrizione estesa del giorno
-- Festivo Bit Indica se il giorno è festivo o no
-- Days Int Giorni trascorsi dalla data di inizio @dateStart
--
-- revision history
-- yyyy/mm/dd by description
-- ========== ======= ==========================================================
-- 2007/06/13 SB created
--
-- ***************************************************************************
CREATE FUNCTION fnCalendar(@datePart as varchar(5), @number as int, @dateStart as smalldatetime)
RETURNS @myReturn TABLE (
Anno smallint,
Mese tinyint,
Giorno tinyint,
miaData varchar(10),
NomeGiorno varchar(10),
Descrizione varchar(100),
Festivo bit,
Days int
)
AS
BEGIN
-- Imposto le variabili
declare @dateEnd as smalldatetime
declare @dateTest as smalldatetime
set @dateStart = coalesce(@dateStart, getdate())
set @dateTest = @dateStart
-- Imposto la data di fine
set @dateEnd =
case
when @datePart = 'y' or @datePart = 'year' then dateadd(year, @number, @dateStart)
when @datePart = 'm' or @datePart = 'month' then dateadd(month, @number, @dateStart)
else dateadd(day, @number, @dateStart)
end
-- Creo il mio calendario
DECLARE @Calendar TABLE (
Anno smallint,
Mese smallint,
Giorno tinyint,
miaData smalldatetime,
Descrizione varchar(100),
Festivo bit,
Days int
)
WHILE @dateTest < @dateEnd
begin
Insert into @Calendar
(
anno,
mese,
giorno,
miaData,
descrizione,
festivo,
Days
)
values(
year(@DateTest),
month(@DateTest),
day(@datetest),
@dateTest,
DATENAME (dw, @dateTest),
case
when datepart (dw, @dateTest) = 6 then 1
when datepart (dw, @dateTest) = 7 then 1
when DATENAME (dw, @dateTest) = 'domenica' then 1
when DATENAME (dw, @dateTest) = 'sabato' then 1
else 0
end,
datediff(day,@DateStart, @DateTest)
)
set @dateTest = dateadd(day, 1, @dateTest)
END
-- Restituisco la tabella vera e propria
IF OBJECT_ID('myFeste') IS NULL
begin
insert @myReturn
select
C.Anno ,
C.Mese,
C.Giorno,
convert(varchar(10), C.miaData , 105),
datename(dw, miaData),
c.Descrizione as 'Descrizione',
c.festivo as 'festivo',
datediff(day, @datestart, miadata)
from @Calendar C
end
ELSE
begin
insert @myReturn
select
C.Anno ,
C.Mese,
C.Giorno,
convert(varchar(10), C.miaData , 105),
datename(dw, miaData),
coalesce(f.descrizione, c.Descrizione) as 'Descrizione',
coalesce(f.festivo, c.festivo) as 'festivo',
datediff(day, @datestart, miadata)
from @Calendar C
left join myFeste F on F.mese = c.mese and F.giorno = c.giorno
end
return
end
go
Non ci resta che provarla.
Esempio 1
Ottenere i giorni trascorsi dal 1 gennaio 2007 al 1 maggio 2007
Select * From fnCalendar('d', 150, '20071201')
Esempio 2
Ottenere i giorni festivi trascorsi dal 1 gennaio 2007 al 1 maggio 2007
Select * From fnCalendar('d', 150, '20071201') where festivo =1
Esempio 3
Ottenere il calendario dei prossimi 2 mesi
Select * From fnCalendar('m', 2, getdate())
Esempio 4
Ottenere l'elenco dei giorni festivi del corrente anno
Select * From fnCalendar('y', 1, '20070101') where festivo = 1
Per finire alcune note sull'utilizzo della funzione
-- ***************************************************************************
-- Utilizzo di fnCalendar
--
-- Select * From fnCalendar('d', 10, '20061224')
--
-- NAME TYPE
-- @datePart VARCHAR(5) = NULL, mandatory
-- @number INT = NULL, optional
-- @dateStart SMALLDATETIME = NULL, mandatory
-- ***************************************************************************