Liczba dni roboczych z zakresu dat

Odejmowanie dat

Zacznijmy od czegoś bardzo łatwego. Do wyznaczenia różnicy jako bezwzględnej liczby np. dni, miesięcy, lat pomiędzy dwoma datami możemy uzyć funkcji sql DATEDIFF. Funkcja zwraca wartość typu integer i jest to liczba jednostek takiego typu, jaki określimy w pierwszym argumencie funkcji. Aby określić np. różnicę w dniach pomiędzy dwoma datami – przekazujemy parametr day lub skrót dd ewentualnie jeszcze krócej d. Przykład :

-- przypisywanie wartości domyślnej w definicji zmiennej - SQL w wersji 2008 +

declare @StartDt smalldatetime = '2012-12-01'
declare @EndDt smalldatetime = '2012-12-21'

select DATEDIFF(dd,@StartDt,@EndDt) as dni, DATEDIFF(ww,@StartDt,@EndDt) as tygodni

Wynik :

dni         tygodni
----------- -----------
20          3

(1 row(s) affected)

Liczba dni roboczych pomiędzy dwoma datami

Czasem trzeba pokazać informację bardziej precyzyjną, np liczbę dni roboczych pomiędzy dwoma datami. Załóżmy że masz do zrobienia raport z informacją o zleceniach i czasie ich realizacji od momentu przyjęcia do wysłania. W firmach w których pracowałem, interesujący był średni czas realizacji zleceń, zadań, kroków procesów (np. budowalanych – średni czas realizacji budowy stacji bazowej od momentu akwizycji działki pod budowę do momentu komercyjnego jej uruchomienia). Przykładów zastosowań znajdziemy mnóstwo. Jak zatem wykonać tego typu kalkulację ?

T-SQL oferuje szereg funkcji wbudowanych związanych z datą i czasem – oprócz DATEDIFF użyjemy do tego zadania DATENAME – funkcję zwaracjącą nazwę dnia tygodnia. Zakładam, że wszystkie soboty i niedziele są traktowane jako dni wolne.

declare @StartDate smalldatetime = '2012-12-01'
declare @EndDate smalldatetime = '2012-12-21'

-- liczba dni roboczych pomiędzy dwoma datami
SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1)
        -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
        -(CASE WHEN DATENAME(dw, @StartDate) =  DATENAME(dw,6) THEN 1 ELSE 0 END)
        -(CASE WHEN DATENAME(dw, @EndDate) =  DATENAME(dw,5) THEN 1 ELSE 0 END) 
       as WorkingDays

Powyższe zapytanie zwraca nam informację poprawną, ale tylko dla regularnych tygodni. Ponadto widać, że poprawność działania jest uzależniona od ustawień językowych sesji w ramach której łączymy się z bazą. W naszym przypadku zmienna sesji dot. języka jest ustawiona na us_english (operacje porównania do 'Saturday’ i 'Sunday’).
Żeby rozwiązać te problemy musimy oprócz dni wolnych by definition, uwględnić inne dni wolne czyli święta. Stworzymy w tym celu tabelę dbo.DniWolne, do której wrzucamy wszystkie extra dni wolne.

Aby uniezależnić się z kolei od ustawień językowych skorzystamy z funkcji DATENAME. Funkcja DATENAME nie jest wrażliwa na ustwienia zmiennej DATEFIRST (wbrew temu co można przeczytać w BOL), więc zawsze wskazuje na sobotę (5) i niedzielę (6). Typ wyliczeniowy dni tygodnia, z którego korzysta ta funkcja, obejmuje zakres od 0 (poniedziałek) do 6 (niedziela).

Ponieważ prawdopodobnie będziemy chcieli używać naszej kalkulacji w różnych miejscach, najlepiej zrobić z niej funkcję użytkownika (UDF User Defined Function), wtedy będziemy mogli używać jej w dowolnych (sensownych :)) miejscach kwerend i w innych obiektach w bazie (widoki, procedurki etc.). Rozwiązanie naszego problemu to :

-- tabela do przechowywania informacji o dniach wolnych

IF OBJECT_ID('dbo.DniWolne') is not null drop table dbo.DniWolne
GO

CREATE TABLE dbo.DniWolne(
	dtWolnyDzien smalldatetime NOT NULL Primary key clustered
);

-- Wrzucamy do tabeli wszystkie święta
INSERT INTO dbo.DniWolne
VALUES ('2012-12-25'),('2012-12-26'),('2013-01-01'),('2013-01-06')
-- itp itd
GO

-- Meritum czyli funkcja zwracająca liczbę dni roboczych pomiędzy dwoma datami 
-- z uwzględnieniem świąt zdefiniowanych w tabeli dbo.DniWolne

IF OBJECT_ID('dbo.fnWorkingDays') is not null drop function dbo.fnWorkingDays
GO

CREATE FUNCTION dbo.fnWorkingDays
(
	@StartDate datetime,
	@EndDate datetime
)
RETURNS int
AS
BEGIN
DECLARE @days int

DECLARE @SatName nvarchar(100),@SunName nvarchar(100)

SELECT @days = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
        -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
        -(CASE WHEN DATENAME(dw, @StartDate) =  DATENAME(dw,6) THEN 1 ELSE 0 END)
        -(CASE WHEN DATENAME(dw, @EndDate) =  DATENAME(dw,5) THEN 1 ELSE 0 END) 
        - (SELECT COUNT(*) FROM dbo.DniWolne WHERE dtWolnyDzien BETWEEN @StartDate AND @EndDate 
		AND DATENAME(dw, dtWolnyDzien) <> DATENAME(dw,5) 
		AND DATENAME(dw, dtWolnyDzien) <> DATENAME(dw,6) )
 
RETURN (@days)
 
END
GO

-- no to jedziemy
select dbo.fnWorkingDays('2012-12-01','2012-12-28') as WorkingDays

Algorytm działania

bierzemy bezwzględną różnicę dni pomiędzy @StartDate a @EndDate – czyli funkcja DATEDIFF.
od liczby dni obliczonych w p.1 odejmujemy wszystkie soboty i niedziele występujące pomiedzy tymi datami – będzie to warunek opcjonalny, tylko wtedy gdy różnica w tygodniach pomiędzy datami jest większa niż 1.
dodatkowo konieczna korekta wynikająca ze sposobu obliczania różnicy w tygodniach pomiędzy datami a także, czy daty początku i końca przedziału nie wypadają np w sobotę lub niedzielę.

Uwagi

Stosując funkcje użytkownia, trzeba pamiętać, że będzie ona wykonywana dla każdego wiersza osobno, czyli tyle razy, ile wierszy zwracanych będzie przez nasze zapytanie (jeśli umieścimy ją w select). Jej definicja to proste przekształcenia na zmiennych + jedna kwerenda do małej tabelki z dniami wolnymi. Można ją jeszcze trochę optymalizować, np. poprzez dodanie kolumny wyliczeniowej IsWorkingDay w tabeli DniWolne, tak aby trochę uprościć kwerendę zliczającą dodatkowe dni wolne w zadanym okresie (niektóre dni wolne wypadają w sobotę lub niedzielę i dla tych, które się załapią w przedziale, badamy w funkcji czy brać je pod uwagę czy nie). Nie będzie to jednak znaczący wzrostu wydajności (stawiam, że żaden :)), z uwagi na charakter tabeli (mini) oraz niewlieką liczbę wierszy.

Ustawienia zmiennej sesyjnej dot. języka, można sprawdzić poleceniem np. DBCC USEROPTIONS
DBCC_USEROPTIONS
Ewenetualnie badając wartość zmiennej globalnej @@LANGID, która to przechowuje obowiązujące id języka. Informacje o językach zainstalowanych na naszym serwerze możemy wyciągnąć za pomocą widoku systemowego sys.syslanguages.

Tips & Tricks