Kolor z liczby dni w widoku: słownik zamiast CASE

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Widok z liczbą dni do terminu w SQL Server można napisać z wyrażeniem CASE, w którym progi koloru leżą w definicji widoku. Wtedy każda zmiana koloru jest zmianą widoku. Ten artykuł pokazuje wzorzec, w którym kolor jest wierszem tabeli, a widok liczy dni i szuka wpisu po kluczu.

Kolor terminu trzyma się w tabeli słownika z kluczem głównym typu int, w którym kluczem jest liczba dni. Widok liczy dni funkcją DATEDIFF w CROSS APPLY i pobiera kolor podzapytaniem skalarnym po tym kluczu. Zmiana koloru to wtedy UPDATE, a dla dnia bez wpisu widok zwraca NULL.

Reguła koloru jako dane w tabeli, a nie jako CASE w widoku

Wzorzec składa się z dwóch tabel i widoku. Tabela słownika ma klucz główny typu int z liczbą dni oraz kolumnę z nazwą koloru. Tabela zgłoszeń trzyma numer i termin. Widok niczego nie koduje na stałe, bo progi koloru leżą w wierszach słownika.

Słownik z przykładu obejmuje dni od 0 do 10 i cztery kolory (red, orange, yellow, green). Zero dni ma kolor red, a green zaczyna się od szóstego dnia. Wpisy są przykładowe, więc każdy zespół ustali własne progi.

CREATE TABLE dbo.slownik_kolorow (
    dni   int         NOT NULL PRIMARY KEY,
    kolor varchar(20) NOT NULL
);
INSERT dbo.slownik_kolorow (dni, kolor) VALUES
    (0, 'red'), (1, 'orange'), (2, 'orange'),
    (3, 'yellow'), (4, 'yellow'), (5, 'yellow'),
    (6, 'green'), (7, 'green'), (8, 'green'), (9, 'green'), (10, 'green');

CREATE TABLE dbo.zgloszenia (
    id     int         NOT NULL PRIMARY KEY,
    numer  varchar(20) NOT NULL,
    termin date        NOT NULL
);
GO

Przykład ilustracyjny, dane wymyślone. Słownik kolorów z kluczem głównym typu int i tabela zgłoszeń z terminem.

W opisywanym wdrożeniu lista reklamacji pokazuje liczbę dni do terminu, a kolor komórki z tą liczbą pochodzi ze słownika, w którym kluczem jest liczba dni. Widok zwraca nazwę koloru, a kolorowaniem zajmuje się lista. Od strony użytkownika opisuje to strona o terminie reklamacji w kolorach, a to, co administrator zmienia sam w module reklamacji, wyjaśnia druga.

Zmiana koloru jest wtedy zmianą wiersza słownika, a nie definicji widoku. Przykład w tym artykule to wzorzec napisany od nowa, z nazwami przykładowymi. Sprawdziliśmy go na SQL Server 2022 Express z kolacją Polish_CI_AS w jednorazowej bazie. Składnia nie używa elementów nowszych niż SQL Server 2016 SP1, a starszych wersji nie sprawdzaliśmy.

Widok liczy dni raz i bierze kolor po kluczu

Widok łączy zgłoszenia z wyrażeniem CROSS APPLY, które oblicza kolumnę dni jako DATEDIFF(DAY, GETDATE(), z.termin). CROSS APPLY nadaje liczbie dni jedną nazwę, więc podzapytanie może ją wykorzystać bez powtarzania wyrażenia.

Kolor zwraca podzapytanie skalarne z tabeli słownika, w którym warunek s.dni = d.dni działa po kluczu głównym. Widok zwraca więc nazwę koloru jako zwykły tekst. Kolorowaniem zajmuje się lista, która ten widok odczytuje.

CREATE VIEW dbo.v_zgloszenia
AS
SELECT z.id, z.numer, z.termin, d.dni,
       (SELECT s.kolor
        FROM dbo.slownik_kolorow AS s
        WHERE s.dni = d.dni) AS kolor
FROM dbo.zgloszenia AS z
CROSS APPLY (SELECT DATEDIFF(DAY, GETDATE(), z.termin) AS dni) AS d;
GO

Przykład ilustracyjny. Widok liczy dni raz i bierze kolor ze słownika po kluczu dni.

Funkcja DATEDIFF z częścią DAY liczy przekroczone granice dób, więc godzina zwrócona przez GETDATE() nie zmienia wyniku. W teście wersja z GETDATE() i wersja z CAST(GETDATE() AS date) dały taki sam wynik dla każdego wiersza.

Wynik widoku zależy od daty uruchomienia, więc powtarzalny test buduje terminy względem bieżącej daty. Dane testowe używają DATEADD na CAST(GETDATE() AS date), a nie dat wpisanych na stałe. Pięć zgłoszeń daje dni od 14 do -3, a dwa z nich dostają kolor NULL: czternaście dni oraz minus trzy dni.

INSERT dbo.zgloszenia (id, numer, termin) VALUES
    (1, '2026-10-0001', DATEADD(DAY, 14, CAST(GETDATE() AS date))),
    (2, '2026-10-0002', DATEADD(DAY, 10, CAST(GETDATE() AS date))),
    (3, '2026-10-0003', DATEADD(DAY,  4, CAST(GETDATE() AS date))),
    (4, '2026-10-0004', CAST(GETDATE() AS date)),
    (5, '2026-10-0005', DATEADD(DAY, -3, CAST(GETDATE() AS date)));
SELECT id, numer, dni, kolor FROM dbo.v_zgloszenia ORDER BY id;

Przykład ilustracyjny. Terminy ustawione względem bieżącej daty i zapytanie o wynik widoku.

Dni spoza słownika dają kolor NULL

Słownik obejmuje dni od 0 do 10, więc zgłoszenie z terminem za 14 dni i zgłoszenie 3 dni po terminie nie mają w nim wiersza. Widok zwraca dla nich kolor NULL i nie zgłasza błędu ani ostrzeżenia.

Brak koloru jest więc cichy. Sam widok go nie sygnalizuje, a lista pokazuje po prostu komórkę bez koloru. Luki w słowniku wykrywa dopiero osobne zapytanie kontrolne, które opisujemy w ostatniej sekcji z kodem.

Klucz główny i typ klucza chronią widok przed błędami

Klucz główny na kolumnie dni pilnuje, żeby dla jednego dnia istniał jeden kolor. Próba dopisania drugiego koloru dla dnia 0 kończy się błędem 2627 w chwili zapisu słownika. Błąd pojawia się więc tam, gdzie powstaje duplikat.

Bez klucza ten sam duplikat psuje odczyt. W teście widok zwrócił wiersze do pierwszego dnia ze zdublowanym wpisem i zakończył się błędem 512, bo podzapytanie zwróciło więcej niż jedną wartość. Błąd ujawnia się w odczycie widoku, a nie tam, gdzie duplikat powstał.

Klucz słownika musi być liczbą bez duplikatów, bo inaczej widok zawiedzie dopiero przy odczycie, a nie przy zapisie słownika.

Znaczenie ma także typ klucza. Gdy kolumna słownika jest tekstowa, a porównuje się ją z liczbą z DATEDIFF, SQL Server konwertuje wartości słownika na int, a wpis niebędący liczbą (w teście abc) kończy zapytanie błędem 245. Klucz typu int unika tej konwersji. Kolejność konwersji opisuje dokumentacja o pierwszeństwie typów danych.

Plan zależy od liczby wierszy. Dla jednego wiersza wyniku optymalizator szukał koloru po kluczu (Nested Loops i Clustered Index Seek na słowniku). Dla 20 tysięcy wierszy wybrał Hash Match z jednokrotnym przeglądem małego słownika. Oba plany są poprawne, a klucz jest potrzebny przede wszystkim dla jednoznaczności i typu. Pomiarów czasu nie wykonywaliśmy, więc nie porównujemy kosztu z wyrażeniem CASE.

Wariant z przedziałami obejmuje także wartości ujemne

Wartość ujemna oznacza termin przekroczony, a zero oznacza termin dzisiaj. Słownik z pojedynczymi dniami nie obejmuje liczb ujemnych, bo wymagałby osobnego wiersza dla każdej z nich.

Wariant z przedziałami trzyma w słowniku dolną granicę w kolumnie dni_od. Widok używa OUTER APPLY z TOP (1), wybiera wiersz o największej granicy nieprzekraczającej liczby dni i sortuje malejąco po dni_od. W tym wariancie ORDER BY jest obowiązkowy, bo bez niego TOP (1) wybiera dowolny wiersz. Skutki takiego wyboru pokazuje artykuł o TOP bez ORDER BY w WMS.

CREATE TABLE dbo.slownik_przedzialow (dni_od int NOT NULL PRIMARY KEY, kolor varchar(20) NOT NULL);
INSERT dbo.slownik_przedzialow VALUES (-1000, 'darkred'), (0, 'red'), (1, 'orange'), (3, 'yellow'), (6, 'green');
GO
CREATE VIEW dbo.v_zgloszenia_przedzialy
AS
SELECT z.id, z.numer, d.dni, k.kolor
FROM dbo.zgloszenia AS z
CROSS APPLY (SELECT DATEDIFF(DAY, GETDATE(), z.termin) AS dni) AS d
OUTER APPLY (SELECT TOP (1) s.kolor
             FROM dbo.slownik_przedzialow AS s
             WHERE s.dni_od <= d.dni
             ORDER BY s.dni_od DESC) AS k;
GO

Przykład ilustracyjny. Słownik z dolnymi granicami przedziałów i widok z OUTER APPLY.

Przykładowy słownik zaczyna się od wiersza -1000 z kolorem darkred, więc zaległe zgłoszenia dostają kolor bez dopisywania wiersza dla każdej liczby dni. Czternaście dni trafia do przedziału od 6 dni w górę i dostaje green. Dla liczby dni poniżej -1000 nie byłoby żadnego wiersza z dolną granicą, więc kolor też byłby NULL.

Pięć zgłoszeń z przykładu: słownik dni daje NULL dla 14 i -3, a wariant z przedziałami koloruje oba terminy.

Zapytanie kontrolne pokazuje luki w słowniku

Zapytanie kontrolne grupuje wiersze widoku z kolorem NULL po liczbie dni i podaje, ile zgłoszeń przypada na każdy dzień bez wpisu. W przykładzie zwraca dwa wiersze: -3 oraz 14, po jednym zgłoszeniu.

SELECT dni, COUNT(*) AS ile
FROM dbo.v_zgloszenia
WHERE kolor IS NULL
GROUP BY dni
ORDER BY dni;

Przykład ilustracyjny. Zapytanie kontrolne pokazuje dni, dla których słownik nie ma koloru.

Przykład: dział reklamacji prowadzi listę zgłoszeń z kolumną dni do terminu. Dla zgłoszenia 2026-10-0003 termin mija za 4 dni, więc widok zwraca kolor yellow z wpisu słownika. Kierownik zmienia kolor yellow na orange jednym UPDATE na tabeli słownika, a widok zostaje bez zmian.

Zgłoszenie 2026-10-0005 jest 3 dni po terminie i ma NULL, bo słownik nie zawiera wartości ujemnych, co ujawnia zapytanie kontrolne. Po przejściu na wariant z przedziałami zaległe zgłoszenia dostają ciemnoczerwony kolor bez dopisywania wiersza dla każdej liczby dni.

Podobny pomysł słownika w tabeli, tam dla kategorii błędów, pokazuje artykuł o klasyfikacji błędów i ponowieniach wysyłki. Walidację dat w procedurze i błąd zwracany jako zwykły wynik opisuje artykuł o API na procedurach SQL i JSON, a termin zwrotu liczony z ostatniego dokumentu artykuł o odczycie stanu narzędzi przez API. Tę samą ideę danych w tabeli stosuje wąska lista z zapasowym zbiorem.

Cztery pytania przed budową własnego słownika kolorów

Przed wdrożeniem takiego słownika w innym systemie ustal cztery rzeczy.

• Zmiana reguły: czy reguła koloru ma się zmieniać bez wdrożenia, czy ma być częścią wersjonowanego kodu widoku.
• Termin przekroczony: jaki kolor ma dostać i czy ma się różnić od terminu przypadającego dzisiaj.
• Rodzaje terminów: ile potrzeba osobnych słowników, jeśli terminów jest kilka, na przykład termin zgłoszenia i termin płatności.
• Zmiany słownika: kto może go zmieniać i czy zmiana ma zostawiać ślad.