Sandro Bizioli


Chi sogna di giorno conosce molte cose che sfuggono a chi sogna soltanto di notte. (E.A.Poe)
Mappa

Funzione Calendario

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
-- ***************************************************************************

Categoria: SQL Server
mercoledì, 04 lug 2007 Ore. 17.28
Statistiche
  • Views Home Page: 111.456
  • Views Posts: 567.884
  • Views Gallerie: 117.357
  • n° Posts: 227
  • n° Commenti: 222
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003