SQL Server: creazione di una vista calendario

Spesso nelle statistiche finanziarie di tipo gestionale sarebbe utile avere una vista che di fatto rappresenti un calendario da poter utilizzare in outer join per ricavare ad esempio dati di venduto suddivisi per anno, trimestre, mese, settimana, giorno.

Per ottenere lo scopo è possibile utilizzare le Common Table Expression (CTE) creando set di risultati temporaneo che rappresenta le date dei giorni in un dato periodo (dal 01/01/2000 al 31/12/2099 nell’esempio che segue) sfruttando la ricorsione.

WITH Calendario(Data) AS
(
   SELECT CAST(‘2000-01-01‘ AS Date) As Data
   UNION All
   SELECT DATEADD( DAY,1,Data) AS Data
   FROM Calendario
   WHERE Data < CAST(‘2099-12-31‘ AS Date)
)

SELECT Data,
  YEAR(Data) AS Anno,
  DATEPART(QUARTER,Data) AS Trimestre,
  MONTH(Data) AS Mese,
  DATENAME(month,Data) AS NomeMese,
  DATEPART(week,Data) AS Settimana,
  DAY(Data) AS Giorno,
  DATENAME(weekday,Data) AS NomeMese,
  DATEPART(dayofyear, Data) AS GiornoAnno,
  DATEPART(weekday, Data) AS GiornoSettimana
FROM Calendario OPTION (maxrecursion 0)

Il problema nasce quando si tenta di creare una vista basata sulla CTE in quanto le viste non supportano l’opzione maxrecursion, a riguardo si veda Allow MAXRECURSION limit values other than 100 for views and UDFs.

Soluzione 1

Una soluzione proposta nel post CTE in a View può essere quella di creare una vista senza l’opzione maxrecursion:

CREATE VIEW
vw_Calendario
AS
WITH Calendario(Data) AS
(
  SELECT CAST(‘2000-01-01’ AS Date) As Data
  UNION All
  SELECT DATEADD( DAY,1,Data) AS Data
  FROM Calendario
  WHERE Data < CAST(‘2099-12-31’ AS Date)
)

SELECT Data FROM Calendario
GO

Quindi eseguire la una selezione sulla vista specificando l’opzione maxrecursion:

SELECT Data,
  YEAR(Data) AS Anno,
  DATEPART(QUARTER,Data) AS Trimestre,
  MONTH(Data) AS Mese,
  DATENAME(month,Data) AS NomeMese,
  DATEPART(week,Data) AS Settimana,
  DAY(Data) AS Giorno,
  DATENAME(weekday,Data) AS NomeMese,
  DATEPART(dayofyear, Data) AS GiornoAnno,
  DATEPART(weekday, Data) AS GiornoSettimana
FROM vw_Calendario OPTION (maxrecursion 0)

Soluzione 2

Volendo è anche possibile creare una vista basta su una CTE utilizzano una Funzione utente che ingloba la CTE e che poi verrà richiamata dalla vista. Per un esempio di questo approccio si veda il post Common Table Expression (CTE) with a View in SQL Server 2005/8.

Soluzione 3

Una terza soluzione può essere quella di creare una CTE per così dire annidata per evitare di usare l’opzione maxrecursion consideranto che per default vemdono ammesse 100 ricorsioni.

Di seguito una query che prima costruisce le date iniziali dei trimestri a blocchi di 100 e poi sulla base di tali date in ricorsione genera le date del trimestre. In questo modo tramite 3 blocchi da 100 trimestri è possibile coprire le date dal primo gennaio 2000 al 31 dicembre 2099:

WITH CalendarioDataInzioTrimestre(Trimestre, Data) AS
(
    SELECT 1 AS Trimestre, CAST(‘2000-01-01’ AS Date) AS Data
    UNION All
    SELECT Trimestre+1 AS Incremento, DATEADD(quarter,1,Data) AS Data
    FROM CalendarioDataInzioTrimestre
    WHERE Trimestre<100
    UNION All
    SELECT Trimestre+100 AS Incremento, DATEADD(quarter,100,Data) AS Data
    FROM CalendarioDataInzioTrimestre
    WHERE Trimestre<100
    UNION All
    SELECT Trimestre+200 AS Incremento, DATEADD(quarter,200,Data) AS Data
    FROM CalendarioDataInzioTrimestre
    WHERE Trimestre<200
), Calendario(Data) AS
(
SELECT CalendarioDataInzioTrimestre.Data AS Data
FROM CalendarioDataInzioTrimestre
UNION All
SELECT DATEADD(day,1,Data) AS Data
FROM Calendario
WHERE DATEPART(QUARTER,Data) = DATEPART(QUARTER,DATEADD(day,1,Data))
)
SELECT  Data,
  YEAR(Data) AS Anno,
  DATEPART(QUARTER,Data) AS Trimestre,
  MONTH(Data) AS Mese,
  DATENAME(month,Data) AS NomeMese,
  DATEPART(week,Data) AS Settimana,
  DAY(Data) AS Giorno,
  DATENAME(weekday,Data) AS NomeGiorno,
  DATEPART(dayofyear, Data) AS GiornoAnno,
  DATEPART(weekday, Data) AS GiornoSettimana FROM Calendario
Order By Data

In modo analogo è possibile creare una vista basata su CTE per un calendario non giornaliero, ma mensile sempre dal primo gennaio 2000 al 31 dicembre 2099:

WITH CalendarioDataInzioMese(Anno, Data) AS
(
    SELECT 1 AS Anno, CAST(‘2000-01-01’ AS Date) AS Data
    UNION All
    SELECT Anno+1 AS Anno, DATEADD(YEAR,1,Data) AS Data
    FROM CalendarioDataInzioMese
    WHERE Anno<=99
), Calendario(Data) AS
(
SELECT CalendarioDataInzioMese.Data AS Data
FROM CalendarioDataInzioMese
UNION All
SELECT DATEADD(MONTH,1,Data) AS Data
FROM Calendario
WHERE DATEPART(YEAR,Data) = DATEPART(YEAR,DATEADD(MONTH,1,Data))
)
SELECT Data,
  YEAR(Data) AS Anno,
  DATEPART(QUARTER,Data) AS Trimestre,
  MONTH(Data) AS Mese,
  DATENAME(month,Data) AS NomeMese,
  DATEPART(week,Data) AS Settimana,
  DAY(Data) AS Giorno,
  DATENAME(weekday,Data) AS NomeGiorno,
  DATEPART(dayofyear, Data) AS GiornoAnno,
  DATEPART(weekday, Data) AS GiornoSettimana FROM Calendario
Order By Data

Conclusioni

Si tenga conto che l’approccio basato su CTE implica una elaborazione lato server che diventa significativa se la vista viene usata all’interno di altre select in join.

In questi casi optare per la creazione di una vista calendario con i soli dati necessari (periodo temporale limitato e granularità del periodo non superiore al necessario, quindi evitare di generare un calendario giornaliero se basta un calendario mensile).

La cosa migliore in ogni caso sarebbe sfruttare la CTE per creare popolare una tabella se il calendario deve essere utilizzato spesso e in join.