Saldo lokalizacji w T-SQL: najstarsze przyjęcie ze stanem i pułapka HAVING po typie ruchu

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

Saldo lokalizacji z ruchów magazynowych w T-SQL istnieje dopiero po zsumowaniu przyjęć i rozchodów, więc warunek HAVING musi stać w grupie, która obejmuje oba rodzaje ruchu. Gdy grupowanie obejmuje także typ ruchu, lokalizacja po całkowitym wydaniu wraca na listę miejsc ze stanem.

Jedno GROUP BY po adresie i palecie sumuje przyjęcia dodatnie i rozchody ujemne, a HAVING SUM(ilosc) > 0 zostawia tylko lokalizacje, w których towar naprawdę leży. Data najstarszego przyjęcia to MIN(CASE WHEN typ = 'P' THEN data_ruchu END), więc to samo zapytanie daje kolejność FIFO. Grupowanie po typie i statusie zwraca natomiast lokalizację z saldem zero.

To wzorzec z nazwami przykładowymi. Uruchomiliśmy go na SQL Server 2022 Express w jednorazowej bazie przy trzech poziomach zgodności, od 100 do 150. Wyniki były identyczne. Przykład nie używa STRING_AGG ani CREATE OR ALTER, więc powinien działać od SQL Server 2016, ale starszych silników nie sprawdzaliśmy.

Dlaczego saldo lokalizacji jest sumą netto z ruchów

W przykładzie stan nie jest zapisany w żadnej kolumnie. Saldo lokalizacji jest sumą ilości z tabeli ruch: przyjęcie ma ilość dodatnią i typ P, a rozchód ilość ujemną i typ R. Każdy ruch ma też status, którego trzy wartości opisuje tabela reguły zaliczania w następnej sekcji. Ruch niesie ponadto artykuł, lokalizację, paletę i datę.

Tabela lokalizacja opisuje adres oraz trzy kolumny bitowe, wymienione w tabeli poniżej. Każda z nich może wyłączyć miejsce z wydawania. Dane testowe mają osiem lokalizacji, a każda ilustruje inny przypadek; zestawia je tabela w sekcji o jednym GROUP BY.

Przykład. Trzy kolumny, z których każda wyłącza miejsce z wyniku.

CREATE TABLE dbo.lokalizacja (
    lokalizacja_id   int         NOT NULL PRIMARY KEY,
    adres            varchar(20) NOT NULL,
    zablokowana      bit         NOT NULL,
    aktywna          bit         NOT NULL,
    strefa_odkladcza bit         NOT NULL);
CREATE TABLE dbo.ruch (
    ruch_id        int IDENTITY(1,1) PRIMARY KEY,
    artykul_id     int           NOT NULL,
    lokalizacja_id int           NOT NULL,
    paleta         varchar(20)   NOT NULL,
    typ            char(1)       NOT NULL,   -- P = przyjęcie (ilość dodatnia), R = rozchód (ilość ujemna)
    status         char(1)       NOT NULL,   -- 0 = bufor, 1 = zatwierdzony, X = usunięty
    ilosc          decimal(15,3) NOT NULL,
    data_ruchu     date          NOT NULL);

Przykład ilustracyjny. Nazwy tabel i kolumn są wymyślone.

INSERT dbo.lokalizacja VALUES
 (1,'A-01-1',0,1,0), (2,'B-02-1',0,1,0), (3,'C-03-1',0,1,0), (4,'D-04-1',1,1,0),
 (5,'E-05-1',0,1,1), (6,'F-06-1',0,1,0), (7,'G-07-1',0,1,0), (8,'H-08-1',0,0,0);
INSERT dbo.ruch (artykul_id, lokalizacja_id, paleta, typ, status, ilosc, data_ruchu) VALUES
 (1,1,'PAL-0001','P','1', 10,'2026-01-05'), (1,1,'PAL-0001','R','1',-10,'2026-02-01'),
 (1,2,'PAL-0002','P','1', 20,'2026-02-10'), (1,2,'PAL-0002','R','0', -5,'2026-03-01'),
 (1,3,'PAL-0003','P','1',  8,'2026-01-20'),
 (1,4,'PAL-0004','P','1', 12,'2026-01-02'),
 (1,5,'PAL-0005','P','1',  9,'2026-01-03'),
 (1,6,'PAL-0006','P','0',  6,'2026-03-01'),
 (1,7,'PAL-0007','P','1',  7,'2026-02-01'), (1,7,'PAL-0007','R','X', -7,'2026-02-15'),
 (1,8,'PAL-0008','P','1',  4,'2026-01-04'),
 (2,2,'PAL-0009','P','1',100,'2026-01-01'), (2,2,'PAL-0009','R','1',-30,'2026-02-02');

Przykład ilustracyjny. Wartości są wymyślone.

Które zapisy wchodzą do salda

Reguła zaliczania w przykładzie ma trzy przypadki, zebrane w tabeli. Rozchód zapisany w buforze zmniejsza więc saldo, zanim dokument zostanie zatwierdzony. To założenie przykładu, a nie cecha jakiegokolwiek systemu: przy własnym schemacie trzeba je ustalić osobno, co jest pierwszym pytaniem w ostatniej sekcji.

Przykład. Reguła dotyczy wyłącznie tego przykładu.

Zapytanie ma zwrócić miejsca, w których artykuł naprawdę leży: adres i paletę z saldem większym od zera, od najstarszego przyjęcia. Lokalizacje wyłączone z wydawania odpadają w tym samym zapytaniu dzięki trzem kolumnom z tabeli lokalizacja.

Warunek zaliczania ma dwa człony połączone przez OR i tu łatwo o błąd. AND wiąże silniej niż OR, więc bez nawiasów wokół obu członów warunek artykułu obejmuje tylko pierwszy z nich, a drugi wciąga rozchody innych artykułów. W przykładzie brak nawiasów dał 10 wierszy zamiast 9.

DECLARE @art int = 1;
SELECT 'bez nawiasów' AS wariant, COUNT(*) AS wiersze
FROM dbo.ruch r
WHERE r.artykul_id = @art AND (r.typ = 'P' AND r.status = '1') OR (r.typ = 'R' AND r.status <> 'X')
UNION ALL
SELECT 'z nawiasami', COUNT(*)
FROM dbo.ruch r
WHERE r.artykul_id = @art AND ((r.typ = 'P' AND r.status = '1') OR (r.typ = 'R' AND r.status <> 'X'));

Przykład ilustracyjny. Zliczenie wierszy przed grupowaniem, bez filtrów lokalizacji: 10 wobec 9.

Pułapka HAVING w grupach po typie i statusie

Wersja, którą łatwo napisać odruchowo, grupuje po adresie, a do tego po typie ruchu i jego statusie, bo te kolumny stoją w SELECT. HAVING SUM(ilosc) > 0 ocenia wtedy każdą grupę osobno. Grupa przyjęć ma zawsze sumę dodatnią, grupa rozchodów ujemną, więc HAVING przepuszcza przyjęcia i odrzuca rozchody. Lokalizacja wydana w całości nadal trafia do wyniku.

DECLARE @art int = 1;
SELECT l.adres, r.typ, r.status, SUM(r.ilosc) AS ilosc
FROM dbo.ruch r
JOIN dbo.lokalizacja l ON l.lokalizacja_id = r.lokalizacja_id
WHERE r.artykul_id = @art
  AND ((r.typ = 'P' AND r.status = '1') OR (r.typ = 'R' AND r.status <> 'X'))
  AND l.zablokowana = 0 AND l.aktywna = 1 AND l.strefa_odkladcza = 0
GROUP BY l.adres, r.typ, r.status
HAVING SUM(r.ilosc) > 0
ORDER BY l.adres;

Przykład ilustracyjny. Wariant z pułapką: HAVING ocenia grupy po typie i statusie.

Na danych przykładu ta wersja zwraca cztery wiersze. Lokalizacja A-01-1 ma przyjęcie 10, mimo że paleta PAL-0001 została wydana w całości, więc jej saldo wynosi zero. Lokalizacja B-02-1 pokazuje 20 zamiast 15, bo rozchód 5 z bufora siedzi w osobnej grupie i nie zmniejszył sumy przyjęć.

Saldo istnieje dopiero po zsumowaniu przyjęć i rozchodów, więc nie oceniaj go osobno w grupie przyjęć ani w grupie rozchodów.

Jedno GROUP BY z saldem i datą najstarszego przyjęcia

Poprawka to jedno GROUP BY po adresie i palecie. Warunek zaliczania ruchów zostaje w WHERE, a HAVING SUM(ilosc) > 0 ocenia saldo netto całej grupy, w której przyjęcia i rozchody tej samej palety leżą razem. A-01-1 ma sumę zero i odpada, a B-02-1 ma 15.

Data najstarszego przyjęcia to MIN(CASE WHEN typ = 'P' THEN data_ruchu END). Wyrażenie CASE daje NULL dla rozchodów, a MIN pomija NULL, więc w tym samym zapytaniu dostajemy saldo i datę. Sortowanie FIFO to ORDER BY najstarsze_przyjecie, adres, paleta. Dwa dalsze klucze rozstrzygają równe daty, więc kolejność jest taka sama przy każdym wywołaniu. Pominięcie ich zostawia kolejność przy równych datach przypadkowi, o czym przypomina artykuł o TOP 1 bez ORDER BY.

DECLARE @art int = 1;
SELECT l.adres, r.paleta,
       SUM(r.ilosc) AS saldo,
       MIN(CASE WHEN r.typ = 'P' THEN r.data_ruchu END) AS najstarsze_przyjecie
FROM dbo.ruch r
JOIN dbo.lokalizacja l ON l.lokalizacja_id = r.lokalizacja_id
WHERE r.artykul_id = @art
  AND ((r.typ = 'P' AND r.status = '1') OR (r.typ = 'R' AND r.status <> 'X'))
  AND l.zablokowana = 0 AND l.aktywna = 1 AND l.strefa_odkladcza = 0
GROUP BY l.adres, r.paleta
HAVING SUM(r.ilosc) > 0
ORDER BY najstarsze_przyjecie, l.adres, r.paleta;

Przykład ilustracyjny. Wariant poprawny: saldo netto i najstarsze przyjęcie w jednym GROUP BY.

Wynik ma trzy wiersze. Na pierwszym miejscu stoi C-03-1 z saldem 8 i przyjęciem z 20 stycznia. Za nią idą G-07-1 z saldem 7 oraz B-02-1 z saldem 15 i przyjęciem z 10 lutego. Tabela zestawia osiem lokalizacji przykładu w obu wariantach.

Przykład. Wynik uruchomienia na SQL Server 2022 Express, artykuł 1.

Wyłączenia lokalizacji i sortowanie FIFO

Wyłączenia lokalizacji idą do WHERE przez złączenie z tabelą lokalizacja, nie do HAVING. W przykładzie korzystają z nich trzy lokalizacje, wskazane w tabeli powyżej, i żadna nie pojawia się w wyniku. HAVING zostaje zarezerwowane dla jednego warunku: salda netto większego od zera.

Ilość trzeba trzymać w decimal, nie w float. Suma 0,1 + 0,2 - 0,3 w float jest większa od zera, więc lokalizacja z „zerem” przechodzi przez HAVING > 0. W decimal(15,3) ta suma wynosi zero i lokalizacja odpada.

SELECT CASE WHEN CAST(0.1 AS float) + CAST(0.2 AS float) - CAST(0.3 AS float) > 0
            THEN N'tak' ELSE N'nie' END AS float_wiekszy_od_zera,
       CASE WHEN CAST(0.1 AS decimal(15,3)) + CAST(0.2 AS decimal(15,3)) - CAST(0.3 AS decimal(15,3)) > 0
            THEN N'tak' ELSE N'nie' END AS decimal_wiekszy_od_zera;

Przykład ilustracyjny. Wynik: float tak, decimal nie.

Data przyjęcia w FIFO to data ruchu P na tej lokalizacji i palecie. Jeśli przeniesienie zapisujesz jako rozchód i nowe przyjęcie, nowa lokalizacja dostaje datę przeniesienia, a zapytanie nie odróżni jej od przyjęcia z zewnątrz. Regułę FIFO trzeba więc ustalić razem ze sposobem zapisu przeniesień. To ostrzeżenie wzorca i nie dotyczy żadnego konkretnego systemu.

Wzorzec daje FIFO po dacie przyjęcia, nie FEFO. Sortowanie po terminie ważności wymaga innej kolumny i osobnej reguły.

Przykład. Sześć pułapek z jednorazowej bazy testowej.

Zapytania kontrolne na saldo zero

Pierwsze zapytanie kontrolne sumuje te same ruchy tylko po adresie i zostawia HAVING SUM(ilosc) = 0. Poprawny wynik nie może zawierać żadnego z tych adresów, a w przykładzie jest to A-01-1. Drugie zapytanie używa wspólnego WITH i operatora EXCEPT: pokazuje adresy obecne wyłącznie w wariancie z pułapką, czyli znów A-01-1.

DECLARE @art int = 1;
-- lokalizacje z saldem zero: nie mogą wystąpić w wyniku poprawnym
SELECT l.adres, SUM(r.ilosc) AS saldo
FROM dbo.ruch r
JOIN dbo.lokalizacja l ON l.lokalizacja_id = r.lokalizacja_id
WHERE r.artykul_id = @art
  AND ((r.typ = 'P' AND r.status = '1') OR (r.typ = 'R' AND r.status <> 'X'))
GROUP BY l.adres
HAVING SUM(r.ilosc) = 0;

DECLARE @art int = 1;
-- adresy obecne w wariancie z pułapką, których nie ma w poprawnym
WITH z AS (
    SELECT l.adres, r.typ, r.status, r.paleta, r.ilosc
    FROM dbo.ruch r
    JOIN dbo.lokalizacja l ON l.lokalizacja_id = r.lokalizacja_id
    WHERE r.artykul_id = @art
      AND ((r.typ = 'P' AND r.status = '1') OR (r.typ = 'R' AND r.status <> 'X'))
      AND l.zablokowana = 0 AND l.aktywna = 1 AND l.strefa_odkladcza = 0)
SELECT adres FROM z GROUP BY adres, typ, status HAVING SUM(ilosc) > 0
EXCEPT
SELECT adres FROM z GROUP BY adres, paleta HAVING SUM(ilosc) > 0;

Przykład ilustracyjny. Oba zapytania zwracają po jednym wierszu: A-01-1.

Przykład: zapytanie o miejsca składowania artykułu

Analityk uruchamia zapytanie o miejsca składowania Artykułu A. Wariant z pułapką zwraca cztery wiersze, w tym lokalizację A-01-1, z której PAL-0001 wyjechała w całości, oraz B-02-1 z ilością 20, chociaż 5 sztuk jest już zapisanych w buforze jako rozchód. Wariant poprawny zwraca trzy wiersze. Na pierwszym miejscu stoi C-03-1 z 8 sztukami przyjętymi w styczniu, potem G-07-1 i B-02-1 z saldem 15. Zapytanie kontrolne na saldo zero zostaje w skrypcie testowym, żeby po każdej zmianie sprawdzić, czy A-01-1 nie wróciła do wyniku.

Stan palet na każdy dzień okresu liczy inny wzorzec, opisany w artykule o paletodobie w T-SQL, a tu mamy saldo w jednej chwili. Dwa sąsiednie wzorce z tej serii dotyczą raportu z numerami w jednej kolumnie oraz indeksu filtrowanego, a wybór ceny z przedziałów opisuje tekst o zakresach cennika. Wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.

Pytania przed napisaniem własnego salda lokalizacji

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

• Bufor: czy rozchód zapisany w buforze ma zmniejszać saldo od razu, czy dopiero po zatwierdzeniu dokumentu.
• Data: od której daty liczyć FIFO: od pierwszego przyjęcia palety czy od ostatniego przeniesienia.
• Wyłączenia: które właściwości lokalizacji mają ją wyłączać z wyniku, a które tylko oznaczać.
• Kontrola: kto i kiedy ma uruchamiać zapytanie kontrolne na saldo zero.