Wybór partii wg terminu ważności w T-SQL: gdzie w ORDER BY lądują wartości NULL

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

Wybór partii wg terminu ważności w T-SQL wydaje się prosty: TOP (1) i ORDER BY po dacie. W przykładzie artykuł ma sześć partii, z czego dwie bez terminu ważności, jedna jest wyczerpana, dwie mają ten sam termin, a jedna termin późny. Zapytanie o partię na 25 sztuk zwraca partię bez terminu, choć są partie z terminem, które trzeba zużyć wcześniej.

TOP (1) z ORDER BY po terminie ważności zwraca najpierw partię bez terminu, bo w T-SQL wartość NULL jest przy sortowaniu rosnącym mniejsza od każdej daty. Wystarczy wyrażenie CASE na początku ORDER BY, które przesuwa NULL na koniec, oraz jawne rozstrzyganie remisów kolejnymi kluczami.

To wzorzec z nazwami przykładowymi. Sprawdziliśmy go na SQL Server 2022 przy czterech poziomach zgodności z zakresu od 110 do 160 i wynik był za każdym razem ten sam. Partie i daty ważności od strony systemu magazynowego opisuje strona Partie produkcyjne i daty ważności.

Partia bez terminu ważności trafia na początek kolejki wyboru

W magazynie zdarzają się partie przyjęte bez daty ważności. W tabeli mają w tej kolumnie NULL, czyli brak wartości, a nie najstarszą ani najświeższą datę. Zapytanie wybierające według terminu musi więc wprost zdecydować, co zrobić z taką partią, bo inaczej decyduje o tym domyślny porządek sortowania.

W scenariuszu (przykład) magazyn ma Artykuł A w sześciu partiach, a dwie z nich przyjęto bez terminu. Zapytanie wydające według terminu wybiera partię A-01 bez terminu, choć są partie z terminem, które trzeba zużyć wcześniej. Po dopisaniu klucza CASE wybór pada na partię A-04, a z dwóch partii z tym samym terminem na tę, która przyszła wcześniej.

Dane próbne z wartościami NULL

Dane próbne to sześć partii artykułu 1 i jedna partia artykułu 2. Dwie partie artykułu 1 nie mają terminu, jedna ma stan zero, a dwie mają ten sam termin przy różnych datach przyjęcia. Artykuł 2 ma jedną partię bez terminu, partię 7 o stanie 20.

CREATE TABLE dbo.Partia (
    IdPartii        int           NOT NULL PRIMARY KEY,
    IdArtykulu      int           NOT NULL,
    Lokalizacja     varchar(10)   NOT NULL,
    DataPrzydatnosci  date          NULL,
    DataPrzyjecia   date          NOT NULL,
    Stan            decimal(12,3) NOT NULL
);
INSERT dbo.Partia VALUES
 (1, 1, 'A-01', NULL,         '2026-08-01', 40),
 (2, 1, 'A-02', '2026-11-30', '2026-09-01', 40),
 (3, 1, 'A-03', '2026-10-20', '2026-09-05', 40),
 (4, 1, 'A-04', '2026-10-20', '2026-09-02', 40),
 (5, 1, 'A-05', '2026-09-30', '2026-09-03',  0),
 (6, 1, 'A-06', NULL,         '2026-08-15', 10),
 (7, 2, 'B-01', NULL,         '2026-09-10', 20);

Przykład ilustracyjny. Kolumna DataPrzydatnosci przechowuje termin ważności partii, a NULL oznacza brak terminu.

Rosnące ORDER BY układa NULL przed każdą datą, a malejące na końcu

ORDER BY rosnąco po terminie ułożył partie bez terminu, 1 i 6, na początku, a potem terminy od najwcześniejszego. ORDER BY malejąco ułożył je na końcu. Dlatego zapytanie z warunkiem Stan >= 25 zwróciło partię 1 (lokalizacja A-01, stan 40), choć istniały partie z terminem i wystarczającym stanem.

Dwa bloki poniżej tworzą jedną partię poleceń: pierwszy pokazuje pułapkę, a drugi wzorzec. Kolejność wyniku opisuje klauzula ORDER BY, a zachowanie wartości brakujących w porównaniach NULL i UNKNOWN.

DECLARE @Artykul int = 1, @Ilosc decimal(12,3) = 25;
SELECT TOP (1) IdPartii, Lokalizacja, DataPrzydatnosci, Stan
FROM dbo.Partia
WHERE IdArtykulu = @Artykul AND Stan >= @Ilosc
ORDER BY DataPrzydatnosci;

Przykład ilustracyjny. Pułapka: partia bez terminu jako pierwsza.

Przykład. Artykuł 1, żądanie 25 sztuk.

Partia bez terminu nie jest ani najstarsza, ani najświeższa, więc jej miejsce w kolejności trzeba wskazać wprost.

CASE na początku ORDER BY zastępuje brakujące NULLS LAST

W T-SQL nie ma NULLS LAST: dopisanie go do ORDER BY daje błąd składni 102 (Incorrect syntax near 'NULLS'). Miejsce NULL ustawiamy więc wyrażeniem CASE jako pierwszym kluczem sortowania: 0 dla partii z terminem i 1 dla partii bez terminu. Dopiero po nim idzie sama data przydatności.

DECLARE @Wybrana int;
SELECT TOP (1) @Wybrana = IdPartii
FROM dbo.Partia
WHERE IdArtykulu = @Artykul AND Stan >= @Ilosc
ORDER BY CASE WHEN DataPrzydatnosci IS NULL THEN 1 ELSE 0 END,
         DataPrzydatnosci, DataPrzyjecia, IdPartii;
SELECT IdPartii, Lokalizacja, DataPrzydatnosci, DataPrzyjecia, Stan
FROM dbo.Partia WHERE IdPartii = @Wybrana;

Przykład ilustracyjny. Wzorzec: NULL na końcu, remis rozstrzygnięty jawnie. Blok korzysta ze zmiennych z poprzedniego bloku.

Wariant z COALESCE(DataPrzydatnosci, '9999-12-31') dał ten sam wynik, partię 4, dla kolumny typu date. Dla kolumny smalldatetime to samo COALESCE ze stałą kończy się błędem 242, bo stała leży poza zakresem typu, a wariant z CASE działa. Stała zastępcza musi więc mieścić się w typie kolumny.

Miejsce partii bez terminu w kolejności jest decyzją biznesową, a nie regułą języka. W przykładzie stoją na końcu. Jeśli mają stać na początku, zmieniamy wartości w CASE.

Remis terminu rozstrzygają kolejne klucze, a zapytanie kontrolne pilnuje reguły

Partie 3 i 4 mają ten sam termin. Wygrywa partia 4, bo została przyjęta wcześniej, czyli rozstrzyga kolejny klucz, data przyjęcia. Ostatni klucz, numer partii, domyka remis do końca, ponieważ bez pełnego klucza kolejność remisu nie jest określona. Jak wygląda zapytanie bez sortowania, pokazuje artykuł o TOP 1 bez ORDER BY.

Zapytanie kontrolne sprawdza regułę: partia bez terminu może zostać wybrana tylko wtedy, gdy nie ma partii z terminem i wystarczającym stanem. W przykładzie zwróciło OK. Sortowanie po dacie najstarszego przyjęcia w jednym zapytaniu pokazuje artykuł o saldzie lokalizacji.

SELECT CASE WHEN w.DataPrzydatnosci IS NULL AND EXISTS (
                 SELECT 1 FROM dbo.Partia p
                 WHERE p.IdArtykulu = @Artykul AND p.Stan >= @Ilosc
                   AND p.DataPrzydatnosci IS NOT NULL)
            THEN 'BLAD' ELSE 'OK' END AS Kontrola
FROM dbo.Partia w WHERE w.IdPartii = @Wybrana;

Przykład ilustracyjny. Zapytanie kontrolne wykonane w tej samej partii po wyborze.

Artykuł 2 ma tylko partię bez terminu, partię 7 o stanie 20. Przy żądaniu 10 sztuk oba zapytania zwracają partię 7, a kontrola daje OK, bo CASE zmienia kolejność, a niczego nie filtruje. Pojedyncza partia musi pokryć całą żądaną ilość: przy 45 sztukach, czyli więcej niż stan każdej partii (40), zapytanie zwróciło zero wierszy.

Wydanie z kilku partii wymaga rozkładu ilości, który opisuje artykuł o alokacji wydania na dostawy. Model dostawy i właściciela opisuje osobny artykuł o stanie magazynu per dostawa i spółka.

Tak oba wyniki wyglądają obok siebie w siatce zapytania.

Sortowanie z CASE ma koszt w planie, a wariant z dwoma zapytaniami go omija

Sprawdziliśmy plan zapytania (SHOWPLAN_TEXT) na tabeli z 200 tysiącami wierszy. Indeks był zbudowany w kolejności kolumn użytych w ORDER BY, z artykułem na początku i z dołączoną kolumną Stan (INCLUDE). Samo ORDER BY po kolumnach indeksu użyło go bez sortowania: Top nad Index Seek, w kolejności ORDERED FORWARD. Wersja z CASE dała Sort (TOP 1) nad Compute Scalar i Index Seek, czyli odczyt partii artykułu i sortowanie.

Bez kolumny Stan w indeksie wersja bez CASE pobierała stan z tabeli (Nested Loops z odczytem kluczy), a wersja z CASE przeszła na skan indeksu klastrowego z sortowaniem. To pomiar planu, a nie czasu, więc nie twierdzimy, że CASE jest wolniejszy w czasie. Planu wariantu z COALESCE nie porównywaliśmy.

Wariant przyjazny indeksowi to dwa zapytania. Pierwsze wybiera spośród partii z terminem (DataPrzydatnosci IS NOT NULL), sortując po terminie i dalej po kluczach rozstrzygających remis. Drugie, tylko gdy wynik pierwszego jest pusty, wybiera spośród partii bez terminu, sortując po dacie przyjęcia i numerze. Na 200 tysiącach wierszy wybrał tę samą partię co wersja z CASE, a plan pierwszego zapytania nie miał sortowania.

Pytania przed napisaniem własnego wyboru partii

Zanim przeniesiesz wzorzec do własnej bazy, odpowiedz sobie na cztery pytania.

• Terminy: czy w Twojej bazie są partie bez terminu ważności i czy wolno wydawać je przed partiami z terminem.
• Miejsce NULL: czy partia bez terminu ma stać na początku, czy na końcu kolejki, a może zależeć od artykułu.
• Remis: czy remis terminu rozstrzyga data przyjęcia, czy numer partii.
• Za mała partia: co ma się stać, gdy żadna pojedyncza partia nie pokrywa żądanej ilości.

Pokrewny wzorzec, w którym liczy się numer dnia tygodnia, a nie NULL, opisuje artykuł o oknie zamówień i DATEFIRST. Wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.