Come ottenere il primo giorno della settimana in SQL Server

Questo è un contenuto aggiuntivo per il corso LearnSQL.it Analisi del comportamento dei clienti in SQL Server.

Nell'articolo precedente abbiamo parlato di come sia possibile utilizzare la funzione DATEPART() di SQL Server con settimana o iso_week per raggruppare gli eventi per settimana. La query potrebbe avere questo aspetto:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Il risultato è simile a questo:

La query visualizza il numero della settimana come etichetta per la settimana. Questa etichetta non è molto utile. Dopo tutto, come facciamo a sapere cosa significa "settimana 22"? È in aprile, maggio o giugno? È meglio visualizzare una data associata a ogni settimana, cioè il suo primo giorno.

In questo articolo vi mostreremo come ottenere il primo giorno della settimana in SQL Server. Per prima cosa: una scorciatoia.

Il trucco: Usare la funzione MIN()

Prima di discutere i modi corretti per calcolare il primo giorno della settimana in SQL Server, parliamo di un trucco che si può usare per mostrare un'etichetta leggibile per una settimana: la funzione MIN():

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  MIN(RegistrationDate) as WeekStart,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Il risultato sarà simile a questo:

Questa query mostra il numero e la data di registrazione minima di ogni settimana (in altre parole, il primo giorno della settimana). Naturalmente, questa soluzione non è affatto perfetta: presuppone che gli eventi da contare (in questo caso, le registrazioni) si verifichino ogni giorno. Se non ci sono registrazioni il primo giorno della settimana, la query mostrerà il secondo giorno della settimana come etichetta WeekStart.

Questa soluzione può essere sufficiente se si lavora semplicemente in modo interattivo con i dati e si ha bisogno di un'approssimazione della data di un evento. Tuttavia, se si ha bisogno della data precisa del primo giorno della settimana, questa soluzione non va bene. Proviamo a fare qualcosa di diverso.

Come calcolare le etichette della settimana in SQL Server

Esistono due modi comuni per definire il primo giorno della settimana: Domenica (tipicamente usato negli Stati Uniti) e lunedì (tipicamente usato in Europa). Iniziamo con l'illustrare come trovare il primo giorno della settimana in uno di questi due stili.

Opzione 1: domenica come primo giorno della settimana

Inizieremo con la domenica, poiché è più facile da spiegare. Ecco l'espressione:

DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday;

La funzione DATEADD() accetta tre argomenti: una parte di data, un numero e una data. Aggiunge quindi un valore numerico specificato alla parte di data specificata di un valore di data in ingresso e restituisce successivamente il valore modificato.

Nell'espressione qui sopra, aggiungiamo un numero specifico di settimane alla data -1. Cosa significa? La data 0 è la mezzanotte del 1° gennaio 1900, che si dà il caso sia lunedì. Quindi la data -1 è domenica 31 dicembre 1899. L'espressione precedente aggiunge un certo numero di settimane a questa data.

L'argomento numero della nostra espressione viene calcolato con la funzione DATEDIFF(). Anche DATEDIFF() accetta tre argomenti: la parte di data, la data di inizio e la data di fine. Restituisce il conteggio delle parti di data specificate tra la data iniziale e la data finale. Quando viene utilizzata con l'argomento settimana, DATEDIFF() funziona indipendentemente dall'impostazione di DATEFIRST: utilizza sempre la domenica come primo giorno della settimana.

L'espressione DATEDIFF(week, -1, RegistrationDate) calcola il numero di settimane tra domenica 31 dicembre 1899 (data -1) e RegistrationDate.

Questa espressione:

DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday

... prende il numero di settimane tra domenica 31 dicembre 1899 e la data di registrazione, aggiunge quel numero a quella domenica e infine restituisce domenica (in altre parole, l'inizio della settimana in cui è avvenuta la registrazione).

Opzione 2: lunedì come primo giorno della settimana

Vediamo ora un'espressione che restituisce lunedì come primo giorno della settimana:

SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday;

Nell'espressione precedente, aggiungiamo il numero di settimane specificato alla data 0. Come si ricorderà, lo 0 rappresenta la mezzanotte del giorno di registrazione. Come si ricorderà, lo 0 rappresenta la mezzanotte di lunedì 1 gennaio 1900.

La funzione DATEDIFF() considera la domenica come primo giorno della settimana, indipendentemente dall'impostazione di DATEFIRST. Questo è il calendario del gennaio 1900 utilizzato da DATEDIFF():

L'espressione DATEDIFF(week, 0, RegistrationDate - 1) calcola il numero di settimane tra lunedì 1° gennaio 1900 (data 0) e un giorno prima della Data di registrazione. Lo spostamento all'indietro di un giorno è necessario perché DATEDIFF() utilizza la domenica come primo giorno della settimana.

Prendiamo come esempio domenica 7 gennaio 1900. L'espressione DATEDIFF(week, 0, '19000107') restituirà 1; se si considera la domenica come primo giorno della settimana, il 7 gennaio si trova nella seconda settimana. Tuttavia, vorremmo che il 7 gennaio fosse trattato come se fosse nella settimana 1, come se il lunedì fosse il primo giorno della settimana. Pertanto, dobbiamo "tornare indietro" di un giorno per ottenere il giusto numero di settimane per una data.

Alla fine, questa è l'espressione che otteniamo:

SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday;

BONUS: Come calcolare il primo giorno della settimana in base all'impostazione di DATEFIRST

Se volete vedere un'espressione che funzioni correttamente per tutte le impostazioni di DATEFIRST, ho una brutta notizia per voi: non è bella. In pratica, probabilmente si considererà solo il lunedì o la domenica come primo giorno della settimana. Ma, per completezza di questo articolo, ecco un'espressione che funzionerà correttamente indipendentemente da DATEFIRST:

SELECT DATEADD(week, 
  DATEDIFF(week, (@@DATEFIRST-8)%7, RegistrationDate-(@@DATEFIRST % 7)), 
  (@@DATEFIRST-8)%7) AS DatefirstAsFirstDayOfWeek;

Innanzitutto, spieghiamo che @@DATEFIRST-8)%7 traduce il valore di DATEFIRST nell'appropriato giorno della settimana a cavallo tra il 1899 e il 1900. (Si potrebbe trovare un'espressione diversa che calcola lo stesso valore). Ecco una tabella che mostra quanti giorni sono trascorsi da quel momento:

L'altra espressione è

DATEDIFF(week, (@@DATEFIRST-8)%7, RegistrationDate - (@@DATEFIRST % 7)). 

Questa espressione calcola la differenza tra il giorno della settimana scelto e la data di registrazione.

Poiché DATEDIFF() utilizza la domenica come primo giorno della settimana, dobbiamo sottrarre il valore di DATEFIRST dalla data di registrazione. Stiamo "spostando indietro" i giorni che, secondo DATEDIFF(), cadono nella settimana successiva.

Quindi, per DATEFIRST = 3 (mercoledì), domenica, lunedì e martedì devono essere "spostati indietro" alla settimana precedente perché DATEDIFF() funzioni come vogliamo.