Kiedy zlecenie jest zrealizowane: równość sum kontra liczba otwartych linii w T-SQL

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

Zamknięcie zlecenia przyjęcia w T-SQL wygląda na proste, dopóki nie trafi się zlecenie, w którym jedna linia przyjęła o dwie sztuki za dużo, a druga o dwie za mało. Suma zleconych i suma przyjętych wynoszą wtedy tyle samo, więc zapytanie porównujące sumy uznaje zlecenie za zrealizowane.

Zlecenie przyjęcia jest zrealizowane wtedy, gdy nie istnieje ani jedna linia z ilością pozostałą większą od zera, liczoną na widoku zawierającym tylko zatwierdzone przyjęcia. Ilość pozostałą obcinamy do zera, więc nadwyżka na jednej linii nie zniesie niedoboru na innej, a zlecenie z nadwyżką nadal się zamknie.

To wzorzec z nazwami przykładowymi, a nie opis działania żadnego systemu. Uruchomiliśmy go na SQL Server 2022 przy poziomach zgodności 130 i 160 i za każdym razem dostaliśmy ten sam wynik.

Zlecenie przyjęcia jest zrealizowane, gdy żadna linia nie ma braku

Weźmy zlecenie z dwiema liniami po 10 sztuk (przykład). Magazynier przyjmuje 12 sztuk Artykułu A i 8 sztuk Artykułu B, a dokument zatwierdza. Suma zlecona i suma przyjęta wynoszą po 20, więc porównanie sum uznaje zlecenie za zrealizowane, choć Artykułowi B brakuje 2 sztuk. Nadwyżka na jednej linii zasłoniła niedobór na drugiej.

Odwrotny przypadek jest równie kłopotliwy. Gdy na liniach przyjęto 12 i 10 sztuk przy zleconych 10 i 10, suma przyjęta wynosi 22 i równość sum nie zachodzi nigdy, więc zlecenie z nadwyżką nie zamknęłoby się. Liczba linii z ilością pozostałą większą od zera wynosi tu 0, więc zamknięcie działa.

Dwa pozostałe zlecenia przykładowe pokazują linie bez pełnego przyjęcia: zlecenie 26-1002 nie ma na linii 2 żadnego przyjęcia, a zlecenie 26-1003 ma tam przyjęcie tylko w buforze. Dwa kolejne pokazują wyjątki: zlecenie 26-1004 ma na linii 2 jedynie wiersz o ilości zero, a zlecenie 26-1006 nie ma żadnej linii. Tabela zbiera wszystkie sześć sytuacji.

Przykład. Porównanie sum zamyka zlecenie 26-1001 mimo otwartej linii, a zlecenie 26-1005 z nadwyżką zostawia otwarte.

Zlecenie jest zrealizowane wtedy, gdy nie ma ani jednej linii z ilością pozostałą większą od zera, a nie wtedy, gdy sumy się zgadzają.

Opis funkcji systemu magazynowego, która zapisuje nadmiar jako wiersz o ilości zero, jest w osobnym artykule o nadmiarze przy przyjęciu towaru w WMS. Ten artykuł dotyczy wyłącznie wzorca zapytania. Formularz przyjęcia dokumentu PZ ze zlecenia opisuje strona o przyjęciu PZ ze zlecenia w WMS.

Dane próbne mają trzy tabele i dwa poziomy realizacji

Przykład składa się z trzech tabel opisanych w zestawieniu poniżej. Sześć zleceń przykładowych, od 26-1001 do 26-1006, odtwarza po jednej sytuacji z tabeli z poprzedniej sekcji. Zlecenie ma status 1 (otwarte) albo 2 (zamknięte).

CREATE TABLE dbo.Zlecenie (Numer varchar(20) PRIMARY KEY, Status char(1) NOT NULL DEFAULT '1');
CREATE TABLE dbo.ZlecLinia (Numer varchar(20), Lp int, Zlecono decimal(15,3), PRIMARY KEY (Numer, Lp));
CREATE TABLE dbo.Przyjecie (Id int IDENTITY PRIMARY KEY, Numer varchar(20), Lp int, Ilosc decimal(15,3), Status char(1));

INSERT dbo.Zlecenie (Numer) VALUES ('26-1001'),('26-1002'),('26-1003'),('26-1004'),('26-1005'),('26-1006');
INSERT dbo.ZlecLinia VALUES ('26-1001',1,10),('26-1001',2,10),('26-1002',1,10),('26-1002',2,10),('26-1003',1,10),('26-1003',2,10),
                            ('26-1004',1,10),('26-1004',2,10),('26-1005',1,10),('26-1005',2,10);
INSERT dbo.Przyjecie (Numer, Lp, Ilosc, Status) VALUES
 ('26-1001',1,12,'1'),('26-1001',2,8,'1'),
 ('26-1002',1,10,'1'),
 ('26-1003',1,10,'1'),('26-1003',2,10,'0'),
 ('26-1004',1,10,'1'),('26-1004',2,0,'1'),
 ('26-1005',1,12,'1'),('26-1005',2,10,'1'),
 ('26-1001',1,5,'X');

Przyjęcie ze statusem X (5 sztuk na linii 1 zlecenia 26-1001) jest usunięte i nie może wpłynąć na żaden wynik. Dwa poziomy realizacji odpowiadają dwóm pytaniom. Poziom bufora obejmuje przyjęcia dopisane do dokumentu, ale jeszcze niezatwierdzone. Poziom zatwierdzonych obejmuje wyłącznie dokumenty zatwierdzone.

Różnica ma konsekwencje dla tego, co widzi operator i kiedy wolno zamknąć zlecenie. Omawiamy ją w dwóch następnych sekcjach.

Widok liczy ilość pozostałą na każdej linii i obcina ją do zera

Widok zwraca po jednym wierszu na linię zlecenia. Ilość przyjętą liczymy jako ISNULL(SUM(ilość), 0), więc linia bez żadnego przyjęcia ma 0, a nie NULL. Kolumna Brakuje to ilość zlecona minus przyjęta, obcięta do zera wyrażeniem CASE. Nadwyżka na jednej linii daje więc 0, a nie liczbę ujemną, która zniosłaby niedobór na innej.

Dwa widoki są tym samym zapytaniem z innym filtrem statusu w warunku złączenia. Widok v_Linie_Z_Buforem bierze statusy 0 i 1, a widok v_Linie_Zatwierdzone wyłącznie status 1. Przyjęcia ze statusem X nie liczą się w żadnym z nich. Składnię opisuje dokumentacja polecenia CREATE VIEW.

CREATE VIEW dbo.v_Linie_Z_Buforem AS
SELECT l.Numer, l.Lp, l.Zlecono,
       ISNULL(SUM(p.Ilosc), 0) AS Przyjeto,
       CASE WHEN l.Zlecono - ISNULL(SUM(p.Ilosc), 0) > 0 THEN l.Zlecono - ISNULL(SUM(p.Ilosc), 0) ELSE 0 END AS Brakuje
FROM dbo.ZlecLinia l
LEFT JOIN dbo.Przyjecie p ON p.Numer = l.Numer AND p.Lp = l.Lp AND p.Status IN ('0','1')
GROUP BY l.Numer, l.Lp, l.Zlecono;
GO

CREATE VIEW dbo.v_Linie_Zatwierdzone AS
SELECT l.Numer, l.Lp, l.Zlecono,
       ISNULL(SUM(p.Ilosc), 0) AS Przyjeto,
       CASE WHEN l.Zlecono - ISNULL(SUM(p.Ilosc), 0) > 0 THEN l.Zlecono - ISNULL(SUM(p.Ilosc), 0) ELSE 0 END AS Brakuje
FROM dbo.ZlecLinia l
LEFT JOIN dbo.Przyjecie p ON p.Numer = l.Numer AND p.Lp = l.Lp AND p.Status = '1'
GROUP BY l.Numer, l.Lp, l.Zlecono;
GO

Przykład ilustracyjny. Dwa widoki różnią się jednym warunkiem w złączeniu; GO oddziela partie.

Obcięcie do zera zapisaliśmy wyrażeniem CASE, a nie funkcjami LEAST i GREATEST, więc zapytania nie zależą od wersji silnika (według dokumentacji obie funkcje pojawiły się dopiero w SQL Server 2022).

Widok z buforem jest przeznaczony dla operatora. W zleceniu 26-1003 linia 2 jest przyjęta w buforze, więc widok z buforem pokazuje 0 linii otwartych, a widok zatwierdzonych nadal 1. Operator widzi już zero do przyjęcia, a zamknięcie czeka na zatwierdzenie dokumentu.

Zamknięcie zlecenia to jeden UPDATE z trzema warunkami

Zamknięcie to jeden UPDATE nagłówka z trzema warunkami. Zlecenie musi mieć status 1, musi mieć co najmniej jedną linię i w widoku zatwierdzonych nie może istnieć linia z kolumną Brakuje większą od zera. Pierwszy warunek sprawia, że ponowne wykonanie polecenia nie dotyka zleceń już zamkniętych.

UPDATE z SET Status = '2'
FROM dbo.Zlecenie z
WHERE z.Status = '1'
  AND EXISTS (SELECT 1 FROM dbo.ZlecLinia l WHERE l.Numer = z.Numer)
  AND NOT EXISTS (SELECT 1 FROM dbo.v_Linie_Zatwierdzone r WHERE r.Numer = z.Numer AND r.Brakuje > 0);

W przykładzie zamyka się tylko zlecenie 26-1005 (1 wiersz), a drugie wykonanie tego samego polecenia zmienia 0 wierszy. Po zamknięciu status 2 ma wyłącznie 26-1005, a pozostałe pięć zleceń zostaje ze statusem 1.

Warunek, że zlecenie ma linie, jest strażnikiem. Bez niego ten sam UPDATE zamknąłby także zlecenie 26-1006, które nie ma żadnej linii, bo nie istnieje w nim linia z brakiem. Zapytanie sprawdzające, które zlecenia zamknęłyby się bez strażnika, zwróciło zlecenia 26-1005 i 26-1006.

SELECT 'PULAPKA_PUSTE' AS Sekcja, z.Numer FROM dbo.Zlecenie z
WHERE z.Status = '1' AND NOT EXISTS (SELECT 1 FROM dbo.v_Linie_Zatwierdzone r WHERE r.Numer = z.Numer AND r.Brakuje > 0)
ORDER BY z.Numer;

Filtr statusu w WHERE i COUNT DISTINCT zakrywają lukę

Trzy konstrukcje, które wyglądają niewinnie, psują wynik po cichu. Każdą uruchomiliśmy na tych samych danych, przed zamknięciem zleceń.

Pierwsza dotyczy złączenia. Filtr statusu wpisany w WHERE zamiast w warunek ON zamienia LEFT JOIN w złączenie wewnętrzne, bo wiersz bez przyjęcia ma po stronie przyjęć status NULL i zostaje odrzucony. Dla zlecenia 26-1002, którego linia 2 nie ma żadnego przyjęcia, takie zapytanie widzi 1 linię zamiast 2. Linia bez przyjęć znika, a zlecenie wygląda na kompletne.

SELECT 'PULAPKA_WHERE' AS Sekcja, l.Numer, COUNT(*) AS Linie_widoczne_z_2
FROM dbo.ZlecLinia l LEFT JOIN dbo.Przyjecie p ON p.Numer = l.Numer AND p.Lp = l.Lp
WHERE p.Status = '1' AND l.Numer = '26-1002'
GROUP BY l.Numer;

Druga dotyczy zliczania. W zleceniu 26-1004 linia 2 ma tylko wiersz o ilości 0. Wyrażenie COUNT(DISTINCT Lp) zlicza 2 linie z przyjęciem, a wersja z CASE WHEN Ilosc <> 0 THEN Lp END zlicza 1. Wiersz o ilości zero zapisuje wyjątek, a nie przyjęcie, więc nie wolno go liczyć jako przyjętej linii. Funkcję opisuje dokumentacja COUNT.

SELECT 'PULAPKA_COUNT' AS Sekcja, p.Numer, COUNT(DISTINCT p.Lp) AS Linie_z_przyjeciem_naiwnie,
       COUNT(DISTINCT CASE WHEN p.Ilosc <> 0 THEN p.Lp END) AS Linie_z_przyjeciem_ilosc_niezerowa
FROM dbo.Przyjecie p WHERE p.Numer = '26-1004' AND p.Status = '1' GROUP BY p.Numer;

Przykład ilustracyjny. Wynik: zlecenie 26-1004, 2 linie z przyjęciem naiwnie i 1 z ilością niezerową.

Trzecia dotyczy sumy różnic. Dla zlecenia 26-1001 wyrażenie SUM(Zlecono - Przyjeto) wynosi 0, bo nadwyżka +2 i niedobór -2 się znoszą. Suma ilości z kolumny Brakuje po obcięciu wynosi 2 i dopiero ona pokazuje, że zlecenie nie jest zrealizowane.

SELECT 'PULAPKA_ROZNICA' AS Sekcja, Numer, SUM(Zlecono - Przyjeto) AS Suma_roznic_bez_obciecia, SUM(Brakuje) AS Suma_brakuje
FROM dbo.v_Linie_Zatwierdzone WHERE Numer = '26-1001' GROUP BY Numer;

Przykład. Wynik błędny to ten, po którym zlecenie wyglądałoby na zrealizowane.

Zapytanie kontrolne pokazuje różnicę między sumami a liczbą linii

Zapytanie kontrolne zestawia dla każdego zlecenia wynik naiwnego porównania sum z liczbą linii otwartych w dwóch widokach, zatwierdzonych i z buforem. Uruchamiamy je przed zamknięciem zleceń, bo pokazuje, które zlecenia zamknęłyby się błędnie.

SELECT 'KONTROLA' AS Sekcja, z.Numer,
       CASE WHEN (SELECT SUM(Zlecono) FROM dbo.ZlecLinia WHERE Numer = z.Numer)
               = (SELECT SUM(Ilosc) FROM dbo.Przyjecie WHERE Numer = z.Numer AND Status = '1')
            THEN 'zamknie' ELSE 'nie zamknie' END AS Naiwnie_sumy,
       (SELECT COUNT(*) FROM dbo.v_Linie_Zatwierdzone r WHERE r.Numer = z.Numer AND r.Brakuje > 0) AS Otwarte_linie_zatw,
       (SELECT COUNT(*) FROM dbo.v_Linie_Z_Buforem r WHERE r.Numer = z.Numer AND r.Brakuje > 0) AS Otwarte_linie_bufor
FROM dbo.Zlecenie z ORDER BY z.Numer;

Przykład. Jedyny wiersz z błędnym wynikiem naiwnych sum to zlecenie 26-1001.

Tak ten wynik wygląda w siatce zapytania, razem z drugą siatką po wykonaniu UPDATE.

Przykład dał identyczny wynik na SQL Server 2022 przy poziomach zgodności 130 i 160. Widoki, złączenie LEFT JOIN i warunek NOT EXISTS są dostępne od SQL Server 2016 (według dokumentacji). Wzorzec sprawdziliśmy wyłącznie dla przyjęć, nie dla wydań, i nie oceniamy wydajności widoków.

Pytania przed napisaniem własnego warunku zamknięcia

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

• Zamknięcie: jak zamyka się zlecenie: po porównaniu sum czy po liczbie otwartych linii, i czy robi to człowiek.
• Widoczność: czy operator ma widzieć ilość pozostałą od razu po dopisaniu pozycji do bufora, czy dopiero po zatwierdzeniu dokumentu.
• Nadwyżka: co ma się stać ze zleceniem, gdy na jednej linii przyjęto więcej, niż zlecono.
• Wyjątki: czy w przyjęciach występują wiersze o ilości zero, które zapisują wyjątek, i kto je liczy.

Pokrewne wzorce z tej serii opisują artykuły o znaczniku wysłano w jednym UPDATE z OUTPUT i o alokacji zachłannej kompletów. Rozkład wydania na dostawy jednego artykułu opisuje artykuł o alokacji wydania sumą narastającą, a pułapkę zapytania o jeden wiersz w TOP 1 bez ORDER BY. O filtrze, który po cichu zmienia sens zapytania, pisze też artykuł o saldzie lokalizacji i pułapce HAVING. Wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.