Paletodoba w T-SQL: stan z końca poprzedniego dnia plus przyjęcia dnia

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

Opisujemy mechanizm rozliczania magazynowania z wdrożenia systemu Studio WMS.net u operatora logistycznego. To przypadek jednego wdrożenia, a nie opis standardu produktu. Kod w artykule napisaliśmy od nowa, z nazwami przykładowymi. Uruchomiliśmy go na SQL Server 2022 Express na danych testowych z tabeli w tekście, a wyniki zgadzają się z podanymi wartościami.

Operator pobiera od właściciela towaru opłatę za każdą dobę, w której paleta stoi w magazynie. Tę jednostkę nazywamy paletodobą, a od strony użytkownika programu opisuje ją artykuł o rozliczaniu usług magazynowych metodą dobopalety. Tutaj schodzimy poziom niżej: do zapytania, które odtwarza regułę z ruchów magazynowych, i do pułapek, które pojawiają się przy pisaniu go od zera.

Co oznacza paletodoba w rozliczeniu magazynowania

W opisywanym wdrożeniu paletodoba dla dnia D to liczba palet ze stanem dodatnim po zatwierdzonych ruchach do końca dnia D minus jeden, plus liczba palet przyjętych w dniu D. Stan z końca poprzedniego dnia obejmuje więc wszystkie ruchy do dnia D minus jeden włącznie. Wynikiem jest jeden wiersz na dzień.

Przyjęcia dnia liczą się niezależnie od rozchodów dnia. Rozchód zmienia dopiero stan z końca dnia, czyli paletodobę dnia następnego.

Paleta przyjęta i wydana tego samego dnia też jest paletodobą, bo przyjęcie dnia liczy się niezależnie od rozchodu.

Tabela pokazuje regułę na danych testowych, które podajemy w dalszych sekcjach. Dla rodzaju palety EUR mamy trzy palety, a okres trwa sześć dni, od 1 do 6 marca. Paleta PAL-000103 jest przyjęta i wydana 4 marca, więc daje jedną paletodobę tego dnia, a 5 marca nie ma już stanu.

Przykład, rodzaj EUR. Dzień bez żadnej palety, czyli 1 marca, pojawia się w wyniku dzięki siatce dni i LEFT JOIN. Sam COUNT(*) po parach (dzień, paleta) by go pominął.

Wykres pokazuje też, że PAL-000102 liczy się jeszcze 6 marca. Wydanie z tego dnia zeruje dopiero stan z końca dnia, więc paleta należy do stanu z końca dnia poprzedniego.

Jakie dane i jakie kategorie bierze zapytanie

W opisywanym wdrożeniu rozliczenie kosztów powstaje procedurą uruchamianą dla jednego kontrahenta. Parametrami są kontrahent, okres od-do, data rozliczenia i opcjonalny oddział. Procedura sprawdza, które kategorie są włączone dla kontrahenta, bo każda kategoria ma osobny wpis definicji, i generuje tylko je. Całość wykonuje się w jednej transakcji, a wynikiem jest dokument w buforze.

Kategorie w tym wdrożeniu to cztery pozycje.

• Przyjęcie palet: zatwierdzone dokumenty przyjęcia z okresu, jedna paleta to jeden wiersz po unikalnym numerze.
• Wydanie palet: zatwierdzone dokumenty wydania z okresu, a paleta wchodzi do opłaty tylko wtedy, gdy jej stan po wydaniu wynosi zero.
• Magazynowanie: paletodoby liczone osobno dla każdego rodzaju palety, który ma pozycję w cenniku kontrahenta.
• Usługi: zatwierdzone usługi z ilością i ceną z cennika, z datą z dokumentu, do którego usługę dopisano.

Wynik ma dwa poziomy wierszy. Wiersze szczegółowe opisują jedną z trzech rzeczy: dokument, dzień albo rodzaj palety, a jeden wiersz ogólny na kategorię zawiera sumę ilości z wierszy szczegółowych. W usługach wiersz ogólny powstaje osobno dla każdej usługi. Każda kategoria ma też wariant z oddziałem i bez niego. Przy podanym oddziale zapytania zawężają się do tego oddziału, a w nagłówku rozliczenia zapisuje się analitykę oddziału, co pozwala wystawiać osobne faktury dla magazynów należących do różnych jednostek.

Przykład pokazuje trzy z czterech kategorii: przyjęcia, wydania i magazynowanie. Usługi pomijamy, bo nie wnoszą do zapytania nowej techniki. Tabela ruchów trzyma dla każdej palety dokument, kierunek (przychód albo rozchód), ilość oraz dwie daty: dokumentu i na fakturę. Ilość w rozchodzie jest ujemna, a rodzaj palety niesie tylko ruch przychodowy. Wiersz cennika wyznaczają trzy cechy: właściciel towaru, kategoria i rodzaj palety. Kategorie oznaczamy w nim trzema kodami: M dla magazynowania, PP dla przyjęcia palet i WP dla wydania palet.

CREATE TABLE dbo.ruchy_palet (
    id_ruchu         bigint        IDENTITY(1,1) PRIMARY KEY,
    numer_dokumentu  varchar(20)   NOT NULL,
    rodzaj_dokumentu varchar(3)    NOT NULL,
    kierunek         char(1)       NOT NULL,
    numer_palety     varchar(20)   NOT NULL,
    rodzaj_palety    varchar(10)   NULL,
    ilosc            decimal(13,3) NOT NULL,
    czy_zatwierdzony bit           NOT NULL,
    id_wlasciciela   bigint        NOT NULL,
    magazyn          varchar(5)    NOT NULL,
    oddzial          varchar(5)    NOT NULL,
    data_dokumentu   date          NOT NULL,
    data_na_fakturze date          NULL
);

CREATE TABLE dbo.cennik (
    id_wlasciciela bigint       NOT NULL,
    kategoria      varchar(2)   NOT NULL,
    rodzaj_palety  varchar(10)  NOT NULL,
    cena           decimal(9,2) NOT NULL,
    waluta         char(3)      NOT NULL,
    CONSTRAINT pk_cennik PRIMARY KEY (id_wlasciciela, kategoria, rodzaj_palety)
);

Przykład ilustracyjny. Nazwy obiektów i typy kolumn są wymyślone.

Dane testowe dotyczą jednego właściciela towaru i jednego magazynu, a wszystkie ruchy są zatwierdzone. Parametry zapytania to okres od 1 do 6 marca 2026 oraz puste wartości magazynu i oddziału, które oznaczają wszystkie.

Przykład. Rodzaj palety wpisujemy w tabeli ruchów tylko przy przyjęciu, a w kolumnie Rodzaj pokazujemy go dla czytelności także przy wydaniu.

Cennik właściciela ma cztery wiersze: magazynowanie dla EUR po 1,20 i dla POL po 0,70 oraz przyjęcie palet i wydanie palet dla EUR po 3,00, wszystko w złotych. Brak pozycji przyjęcia i wydania dla POL jest celowy, bo posłuży do pokazania wiersza bez ceny.

INSERT INTO dbo.ruchy_palet
    (numer_dokumentu, rodzaj_dokumentu, kierunek, numer_palety, rodzaj_palety, ilosc,
     czy_zatwierdzony, id_wlasciciela, magazyn, oddzial, data_dokumentu, data_na_fakturze)
VALUES
 ('PZ-26-0001', 'PZ', 'P', 'PAL-000101', 'EUR',  100, 1, 1, 'M1', 'O1', '2026-03-02', NULL),
 ('WZ-26-0001', 'WZ', 'R', 'PAL-000101', NULL,  -100, 1, 1, 'M1', 'O1', '2026-03-05', NULL),
 ('PZ-26-0001', 'PZ', 'P', 'PAL-000102', 'EUR',   50, 1, 1, 'M1', 'O1', '2026-03-02', NULL),
 ('WZ-26-0002', 'WZ', 'R', 'PAL-000102', NULL,   -20, 1, 1, 'M1', 'O1', '2026-03-04', NULL),
 ('WZ-26-0003', 'WZ', 'R', 'PAL-000102', NULL,   -30, 1, 1, 'M1', 'O1', '2026-03-06', NULL),
 ('PZ-26-0002', 'PZ', 'P', 'PAL-000103', 'EUR',   80, 1, 1, 'M1', 'O1', '2026-03-04', NULL),
 ('WZ-26-0004', 'WZ', 'R', 'PAL-000103', NULL,   -80, 1, 1, 'M1', 'O1', '2026-03-04', NULL),
 ('PZ-26-0003', 'PZ', 'P', 'PAL-000104', 'POL',   40, 1, 1, 'M1', 'O1', '2026-02-28', '2026-03-03');

INSERT INTO dbo.cennik (id_wlasciciela, kategoria, rodzaj_palety, cena, waluta)
VALUES (1, 'M',  'EUR', 1.20, 'PLN'),
       (1, 'M',  'POL', 0.70, 'PLN'),
       (1, 'PP', 'EUR', 3.00, 'PLN'),
       (1, 'WP', 'EUR', 3.00, 'PLN');

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

Data na fakturę zamiast daty dokumentu

Data rozliczeniowa to data na fakturę, a gdy jej brak, data dokumentu. W opisywanym wdrożeniu reguła obowiązuje w przyjęciach, wydaniach, stanie dziennym i usługach. Skutek praktyczny jest prosty: przyjęcie z końca miesiąca można rozliczyć w następnym okresie.

W danych testowych dotyczy to palety PAL-000104. Dokument przyjęcia nosi datę 28 lutego, ale data na fakturę to 3 marca, więc w okresie od 1 do 6 marca paleta liczy się dopiero od 3 marca. Dla 1 i 2 marca nie daje żadnej paletodoby.

Wyrażenie COALESCE zapisujemy w jednym miejscu, w wyrażeniu tabelowym o nazwie ruchy, razem z filtrami zatwierdzenia, właściciela, magazynu i oddziału. Każdy dalszy krok czyta kolumnę data_rozliczeniowa i nie wie, skąd się wzięła. Reguły nie powtarzamy w kolejnych krokach, bo wystarczy jedna rozbieżność, żeby przyjęcie i stan dzienny trafiły do różnych dni. Wyrażenie nie ogranicza dat, ponieważ stan z końca poprzedniego dnia zależy od całej historii palety.

DECLARE @id_wlasciciela bigint     = 1,
        @od             date       = '2026-03-01',
        @do             date       = '2026-03-06',
        @oddzial        varchar(5) = '',
        @magazyn        varchar(5) = '';

WITH dni AS (
    SELECT @od AS dzien
    UNION ALL
    SELECT DATEADD(DAY, 1, dzien) FROM dni WHERE dzien < @do
),
ruchy AS (
    SELECT r.numer_dokumentu, r.rodzaj_dokumentu, r.kierunek, r.numer_palety,
           r.rodzaj_palety, r.ilosc, r.magazyn,
           COALESCE(r.data_na_fakturze, r.data_dokumentu) AS data_rozliczeniowa
    FROM dbo.ruchy_palet AS r
    WHERE r.czy_zatwierdzony = 1
      AND r.id_wlasciciela = @id_wlasciciela
      AND (@magazyn = '' OR r.magazyn = @magazyn)
      AND (@oddzial = '' OR r.oddzial = @oddzial)
),

Początek zapytania: parametry i dwa wyrażenia, kalendarz oraz ruchy. Kolejne wyrażenia dopisujemy po przecinku w jednej instrukcji WITH.

Stan z końca poprzedniego dnia plus przyjęcia dnia

Kalendarz dni daje rekurencyjne wyrażenie tabelowe o nazwie dni. Domyślny limit rekurencji w SQL Server wynosi sto poziomów, co wystarcza na okres do 101 dni. Dłuższy okres wymaga wskazówki OPTION (MAXRECURSION 400) albo osobnej tabeli kalendarza, a przykład kończy się właśnie taką wskazówką. Bez niej zapytanie o 102 dni kończy się błędem o wyczerpanym limicie rekurencji.

Rodzaj palety bierzemy z jej najwcześniejszego ruchu przychodowego. Złączenie CROSS APPLY z TOP (1) zwraca jeden wiersz na paletę, więc rozchód nie musi nieść rodzaju. ORDER BY jest tu obowiązkowy z powodu opisanego w artykule o TOP 1 bez ORDER BY, a drugi składnik sortowania, numer dokumentu, rozstrzyga remis przy dwóch przyjęciach z tego samego dnia.

palety AS (
    SELECT p.numer_palety, x.rodzaj_palety
    FROM (SELECT DISTINCT numer_palety FROM ruchy) AS p
    CROSS APPLY (SELECT TOP (1) r.rodzaj_palety
                 FROM ruchy AS r
                 WHERE r.numer_palety = p.numer_palety AND r.kierunek = 'P'
                 ORDER BY r.data_rozliczeniowa, r.numer_dokumentu) AS x
),

Przykład ilustracyjny. Wyrażenie palety przypisuje rodzaj do numeru palety.

W opisywanym wdrożeniu stan dzienny wyznacza pętla po dniach okresu. W przykładzie ta sama reguła ma postać zapytania zbiorczego, które wyznacza przedziały dni ze stanem dodatnim. Najpierw sumujemy ilość na paletę i dzień. Potem funkcja okna SUM z klauzulą OVER daje saldo narastające, a LEAD podaje datę następnej zmiany.

zmiany AS (
    SELECT numer_palety, data_rozliczeniowa, SUM(ilosc) AS suma
    FROM ruchy
    GROUP BY numer_palety, data_rozliczeniowa
),
przedzialy AS (
    SELECT numer_palety,
           data_rozliczeniowa AS od_dnia,
           SUM(suma) OVER (PARTITION BY numer_palety
                           ORDER BY data_rozliczeniowa
                           ROWS UNBOUNDED PRECEDING) AS saldo,
           LEAD(data_rozliczeniowa) OVER (PARTITION BY numer_palety
                                          ORDER BY data_rozliczeniowa) AS nastepna_zmiana
    FROM zmiany
),

Przykład ilustracyjny. Każdy wiersz to przedział od dnia zmiany do dnia poprzedzającego następną zmianę.

Paleta ma stan dodatni na koniec dnia X, gdy saldo jest większe od zera, dzień zmiany nie jest późniejszy niż X, a następna zmiana jeszcze nie nastąpiła albo wypada po X. Stan z końca poprzedniego dnia to złączenie kalendarza z przedziałami, w którym za X podstawiamy dzień kalendarza minus jeden. Wynikiem są pary: dzień i numer palety.

Przyjęte tego dnia pochodzą z ruchów przychodowych trzech rodzajów dokumentów: PZ, PZB i PW. W opisywanym wdrożeniu do przyjętych zalicza się ruchy z pięciu rodzajów dokumentów przychodowych, a przykład ogranicza się do trzech z nich. Grupujemy po dniu i numerze palety i zostawiamy grupy z dodatnią sumą ilości. Rozchody do tego kroku nie wchodzą, więc paleta wydana tego samego dnia nadal się liczy. Ograniczenie dat do okresu rozliczenia jest konieczne, bo bez niego do wyniku trafiłyby przyjęcia spoza okresu.

stan_wczoraj AS (
    SELECT d.dzien, p.numer_palety
    FROM dni AS d
    JOIN przedzialy AS p
      ON p.saldo > 0
     AND p.od_dnia <= DATEADD(DAY, -1, d.dzien)
     AND (p.nastepna_zmiana IS NULL OR DATEADD(DAY, -1, d.dzien) < p.nastepna_zmiana)
),
przyjete AS (
    SELECT r.data_rozliczeniowa AS dzien, r.numer_palety
    FROM ruchy AS r
    WHERE r.kierunek = 'P'
      AND r.rodzaj_dokumentu IN ('PZ', 'PZB', 'PW')
      AND r.data_rozliczeniowa BETWEEN @od AND @do
    GROUP BY r.data_rozliczeniowa, r.numer_palety
    HAVING SUM(r.ilosc) > 0
),

Przykład ilustracyjny. Dwa zbiory par: stan z poprzedniego dnia i przyjęcia dnia.

Zbiory łączymy operatorem UNION, a nie UNION ALL. UNION usuwa duplikaty, więc paleta, która ma stan z poprzedniego dnia i przychód tego samego dnia, liczy się raz. Do par dołączamy rodzaj palety i liczymy COUNT(*) na dzień i rodzaj. Sam COUNT(*) pominąłby dzień bez żadnej palety, czyli 1 marca dla EUR, dlatego wynik opieramy na siatce: dni razy rodzaje palet z cennika kategorii magazynowania. Liczniki dołączamy przez LEFT JOIN z COALESCE(liczba, 0). Rodzaj palety bez pozycji magazynowania w cenniku w ogóle nie trafia do siatki, zgodnie z regułą opisywanego wdrożenia.

paletodoby AS (
    SELECT dzien, numer_palety FROM stan_wczoraj
    UNION
    SELECT dzien, numer_palety FROM przyjete
),
liczniki AS (
    SELECT pd.dzien, pl.rodzaj_palety, COUNT(*) AS liczba_palet
    FROM paletodoby AS pd
    JOIN palety AS pl ON pl.numer_palety = pd.numer_palety
    GROUP BY pd.dzien, pl.rodzaj_palety
),
siatka AS (
    SELECT d.dzien, c.rodzaj_palety, COALESCE(l.liczba_palet, 0) AS liczba_palet
    FROM dni AS d
    CROSS JOIN (SELECT rodzaj_palety
                FROM dbo.cennik
                WHERE id_wlasciciela = @id_wlasciciela AND kategoria = 'M') AS c
    LEFT JOIN liczniki AS l
           ON l.dzien = d.dzien AND l.rodzaj_palety = c.rodzaj_palety
)
SELECT dzien, rodzaj_palety, liczba_palet
FROM siatka
ORDER BY rodzaj_palety, dzien
OPTION (MAXRECURSION 400);

Przykład ilustracyjny. Wynik ma dwanaście wierszy: sześć dni dla EUR i sześć dla POL.

Dla EUR suma paletodób wynosi 10, a dla POL 4. Wartości zgadzają się z tabelą z początku artykułu, a paleta PAL-000104 daje paletodobę od 3 marca, bo tak wskazuje jej data na fakturę.

Cena z cennika i wiersz bez ceny

Wiersz cennika wyznaczają trzy cechy: kontrahent, kategoria i rodzaj palety. Niesie on trzy wartości: cenę, walutę i jednostkę. W opisywanym wdrożeniu procedura wstawia wiersze szczegółowe, a potem dopisuje cenę po identyfikatorze pozycji cennika. Wartość wiersza ogólnego liczy jako sumę wartości jego wierszy szczegółowych.

Przykład idzie inną drogą, bo ma pokazać tę regułę wprost w jednym zapytaniu. Wiersze szczegółowe i ogólny daje GROUPING SETS z dwoma zestawami grupowania: rodzaj palety z dniem oraz sam rodzaj. Wiersz ogólny ma pusty dzień. Cenę dołącza LEFT JOIN, a wartość to paletodoby razy cena. Wzór ilości razy cena jest decyzją autora przykładu, a nie opisem wdrożenia. Aby go zastosować, ostatni SELECT z poprzedniego bloku zastępujemy poniższym wyrażeniem i zapytaniem, a wyrażenie siatka kończymy przecinkiem.

wynik AS (
    SELECT rodzaj_palety, dzien, SUM(liczba_palet) AS paletodoby
    FROM siatka
    GROUP BY GROUPING SETS ((rodzaj_palety, dzien), (rodzaj_palety))
)
SELECT w.rodzaj_palety, w.dzien, w.paletodoby, c.cena,
       w.paletodoby * c.cena AS wartosc,
       CASE WHEN c.cena IS NULL THEN N'Brak pozycji cennika.' END AS uwagi
FROM wynik AS w
LEFT JOIN dbo.cennik AS c
       ON c.id_wlasciciela = @id_wlasciciela
      AND c.kategoria = 'M'
      AND c.rodzaj_palety = w.rodzaj_palety
ORDER BY w.rodzaj_palety, w.dzien
OPTION (MAXRECURSION 400);

Przykład ilustracyjny. Zamiast ostatniego SELECT z poprzedniego bloku.

Dla rodzaju EUR wiersz ogólny pokazuje 10 paletodób, cenę 1,20 i wartość 12,00. Dla POL jest to 4 paletodoby i wartość 2,80.

Przyjęcia palet

W przyjęciach bierzemy zatwierdzone dokumenty, których rodzaj zaczyna się od PZ, a jedna paleta to jeden wiersz po unikalnym numerze. Wiersze grupujemy po dokumencie, dacie rozliczeniowej, magazynie i rodzaju palety, a cenę dobieramy po rodzaju palety. Zbiór rodzajów dokumentów jest tu inny niż przy paletodobach, bo kategoria przyjęć ma własną definicję.

Cennik nie ma pozycji przyjęcia dla rodzaju POL, więc LEFT JOIN zostawia wiersz dokumentu PZ-26-0003 bez ceny, a wyrażenie CASE dopisuje uwagę „Brak pozycji cennika.”. W przykładzie wiersz bez ceny pozostaje widoczny, a brak ceny nie zamienia się po cichu w zero. To rozwiązanie przykładu, a nie sprawdzona cecha opisywanego wdrożenia.

DECLARE @id_wlasciciela bigint     = 1,
        @od             date       = '2026-03-01',
        @do             date       = '2026-03-06',
        @oddzial        varchar(5) = '',
        @magazyn        varchar(5) = '';

WITH dni AS (
    SELECT @od AS dzien
    UNION ALL
    SELECT DATEADD(DAY, 1, dzien) FROM dni WHERE dzien < @do
),
ruchy AS (
    SELECT r.numer_dokumentu, r.rodzaj_dokumentu, r.kierunek, r.numer_palety,
           r.rodzaj_palety, r.ilosc, r.magazyn,
           COALESCE(r.data_na_fakturze, r.data_dokumentu) AS data_rozliczeniowa
    FROM dbo.ruchy_palet AS r
    WHERE r.czy_zatwierdzony = 1
      AND r.id_wlasciciela = @id_wlasciciela
      AND (@magazyn = '' OR r.magazyn = @magazyn)
      AND (@oddzial = '' OR r.oddzial = @oddzial)
),
palety AS (
    SELECT p.numer_palety, x.rodzaj_palety
    FROM (SELECT DISTINCT numer_palety FROM ruchy) AS p
    CROSS APPLY (SELECT TOP (1) r.rodzaj_palety
                 FROM ruchy AS r
                 WHERE r.numer_palety = p.numer_palety AND r.kierunek = 'P'
                 ORDER BY r.data_rozliczeniowa, r.numer_dokumentu) AS x
),
przyjecia AS (
    SELECT r.numer_dokumentu, r.data_rozliczeniowa, r.magazyn, pl.rodzaj_palety,
           COUNT(DISTINCT r.numer_palety) AS liczba_palet
    FROM ruchy AS r
    JOIN palety AS pl ON pl.numer_palety = r.numer_palety
    WHERE r.kierunek = 'P'
      AND r.rodzaj_dokumentu LIKE 'PZ%'
      AND r.data_rozliczeniowa BETWEEN @od AND @do
    GROUP BY r.numer_dokumentu, r.data_rozliczeniowa, r.magazyn, pl.rodzaj_palety
)
SELECT p.numer_dokumentu, p.data_rozliczeniowa, p.rodzaj_palety, p.liczba_palet,
       MAX(c.cena) AS cena,
       p.liczba_palet * MAX(c.cena) AS wartosc,
       CASE WHEN MAX(c.cena) IS NULL THEN N'Brak pozycji cennika.' END AS uwagi
FROM przyjecia AS p
LEFT JOIN dbo.cennik AS c
       ON c.id_wlasciciela = @id_wlasciciela
      AND c.kategoria = 'PP'
      AND c.rodzaj_palety = p.rodzaj_palety
GROUP BY p.numer_dokumentu, p.data_rozliczeniowa, p.magazyn, p.rodzaj_palety, p.liczba_palet
ORDER BY p.numer_dokumentu;

Przykład ilustracyjny. Zapytanie dołącza się do wyrażeń ruchy i palety.

Wynik zawiera trzy wiersze. Dokument PZ-26-0001 ma dwie palety EUR, PZ-26-0002 jedną paletę EUR, a PZ-26-0003 jedną paletę POL bez ceny. Wartość przyjęć EUR to 9,00, czyli trzy palety po 3,00.

Wydania palet

Paleta wchodzi do opłaty za wydanie wyłącznie wtedy, gdy suma jej ilości do końca dnia wydania wynosi zero. Paleta wydana częściowo nie generuje opłaty za wydanie, a rodzaj palety pochodzi z jej ruchu przychodowego. Stan na dzień liczy funkcja skalarna, którą wywołujemy dla każdej pary dokument i paleta.

CREATE FUNCTION dbo.funkcja_stan_palety_na_dzien (@numer_palety varchar(20), @dzien date)
RETURNS decimal(13,3)
AS
BEGIN
    RETURN (SELECT COALESCE(SUM(r.ilosc), 0)
            FROM dbo.ruchy_palet AS r
            WHERE r.numer_palety = @numer_palety
              AND r.czy_zatwierdzony = 1
              AND COALESCE(r.data_na_fakturze, r.data_dokumentu) <= @dzien);
END;

Przykład ilustracyjny. Funkcja czyta zatwierdzone ruchy palety do końca podanego dnia.

Gdy dwa dokumenty wydania z tego samego dnia zerują tę samą paletę, przykład liczy ją raz, przy dokumencie o większym numerze. Pomaga w tym funkcja ROW_NUMBER.

DECLARE @id_wlasciciela bigint     = 1,
        @od             date       = '2026-03-01',
        @do             date       = '2026-03-06',
        @oddzial        varchar(5) = '',
        @magazyn        varchar(5) = '';

WITH dni AS (
    SELECT @od AS dzien
    UNION ALL
    SELECT DATEADD(DAY, 1, dzien) FROM dni WHERE dzien < @do
),
ruchy AS (
    SELECT r.numer_dokumentu, r.rodzaj_dokumentu, r.kierunek, r.numer_palety,
           r.rodzaj_palety, r.ilosc, r.magazyn,
           COALESCE(r.data_na_fakturze, r.data_dokumentu) AS data_rozliczeniowa
    FROM dbo.ruchy_palet AS r
    WHERE r.czy_zatwierdzony = 1
      AND r.id_wlasciciela = @id_wlasciciela
      AND (@magazyn = '' OR r.magazyn = @magazyn)
      AND (@oddzial = '' OR r.oddzial = @oddzial)
),
palety AS (
    SELECT p.numer_palety, x.rodzaj_palety
    FROM (SELECT DISTINCT numer_palety FROM ruchy) AS p
    CROSS APPLY (SELECT TOP (1) r.rodzaj_palety
                 FROM ruchy AS r
                 WHERE r.numer_palety = p.numer_palety AND r.kierunek = 'P'
                 ORDER BY r.data_rozliczeniowa, r.numer_dokumentu) AS x
),
wydania AS (
    SELECT z.numer_dokumentu, z.data_rozliczeniowa, z.numer_palety,
           ROW_NUMBER() OVER (PARTITION BY z.numer_palety, z.data_rozliczeniowa
                              ORDER BY z.numer_dokumentu DESC) AS lp
    FROM (SELECT DISTINCT numer_dokumentu, data_rozliczeniowa, numer_palety
          FROM ruchy
          WHERE rodzaj_dokumentu LIKE 'WZ%'
            AND data_rozliczeniowa BETWEEN @od AND @do) AS z
    WHERE dbo.funkcja_stan_palety_na_dzien(z.numer_palety, z.data_rozliczeniowa) = 0
)
SELECT w.numer_dokumentu, w.data_rozliczeniowa, pl.rodzaj_palety,
       COUNT(*) AS liczba_palet,
       MAX(c.cena) AS cena,
       COUNT(*) * MAX(c.cena) AS wartosc,
       CASE WHEN MAX(c.cena) IS NULL THEN N'Brak pozycji cennika.' END AS uwagi
FROM wydania AS w
JOIN palety AS pl ON pl.numer_palety = w.numer_palety
LEFT JOIN dbo.cennik AS c
       ON c.id_wlasciciela = @id_wlasciciela
      AND c.kategoria = 'WP'
      AND c.rodzaj_palety = pl.rodzaj_palety
WHERE w.lp = 1
GROUP BY w.numer_dokumentu, w.data_rozliczeniowa, pl.rodzaj_palety
ORDER BY w.numer_dokumentu;

Przykład ilustracyjny. Zapytanie dołącza się do wyrażeń ruchy i palety.

Zerują paletę trzy wydania: WZ-26-0001 z 5 marca, WZ-26-0003 z 6 marca oraz WZ-26-0004 z 4 marca. Wydanie WZ-26-0002 nie generuje opłaty, bo po nim PAL-000102 ma jeszcze 30 sztuk. Wartość wydań EUR wynosi 9,00.

Zatwierdzenie z numerem i kontrola nakładania okresów

Zanim powstanie rozliczenie, procedura sprawdza, czy okres kontrahenta nie nakłada się na istniejące rozliczenie. W opisywanym wdrożeniu funkcja zwraca znacznik T, gdy dla kontrahenta istnieje niewykasowane rozliczenie, którego okres zachodzi na podany na jeden z trzech sposobów: początek mieści się w zakresie, koniec mieści się w zakresie albo okres obejmuje cały zakres. Istnieje drugi wariant tej kontroli, dla magazynu i oddziału.

Trzy warunki sprowadzają się do jednego. Okresy zachodzą na siebie, gdy początek istniejącego rozliczenia nie jest późniejszy niż koniec nowego okresu, a koniec istniejącego nie jest wcześniejszy niż początek nowego. Przykład zwraca bit zamiast znacznika, żeby wynik można było wprost podstawić do warunku.

CREATE TABLE dbo.rozliczenia (
    id_rozliczenia int IDENTITY(1,1) PRIMARY KEY,
    id_wlasciciela bigint      NOT NULL,
    data_od        date        NOT NULL,
    data_do        date        NOT NULL,
    status         char(1)     NOT NULL,
    numer          varchar(20) NULL
);

CREATE TABLE dbo.wiersze_rozliczen (
    id_wiersza     int IDENTITY(1,1) PRIMARY KEY,
    id_rozliczenia int        NOT NULL REFERENCES dbo.rozliczenia (id_rozliczenia),
    kategoria      varchar(2) NOT NULL,
    status         char(1)    NOT NULL
);

CREATE TABLE dbo.historia_rozliczen (
    id_wpisu       int IDENTITY(1,1) PRIMARY KEY,
    id_rozliczenia int           NOT NULL,
    zdarzenie      nvarchar(100) NOT NULL,
    czas           datetime2(0)  NOT NULL DEFAULT SYSDATETIME(),
    login_nazwa    sysname       NOT NULL DEFAULT ORIGINAL_LOGIN()
);

CREATE SEQUENCE dbo.numer_rozliczenia AS int START WITH 1 INCREMENT BY 1;

Przykład ilustracyjny. Nagłówek rozliczenia, jego wiersze, historia zdarzeń i sekwencja numerów.

CREATE FUNCTION dbo.funkcja_okres_zajety (@id_wlasciciela bigint, @od date, @do date)
RETURNS bit
AS
BEGIN
    RETURN CASE WHEN EXISTS (SELECT 1
                             FROM dbo.rozliczenia AS z
                             WHERE z.id_wlasciciela = @id_wlasciciela
                               AND z.status <> 'X'
                               AND z.data_od <= @do
                               AND z.data_do >= @od)
                THEN 1 ELSE 0 END;
END;

Przykład ilustracyjny. Status X oznacza rozliczenie wykasowane.

Funkcję wywołujemy na początku procedury rozliczenia i przerywamy z komunikatem, gdy wynik to 1.

IF dbo.funkcja_okres_zajety(@id_wlasciciela, @od, @do) = 1
    THROW 50002, N'Okres nakłada się na istniejące rozliczenie.', 1;

Fragment procedury, przykład ilustracyjny.

Wyniki sprawdziliśmy na istniejącym rozliczeniu obejmującym okres od 15 lutego do 10 marca 2026.

Przykład. Wyniki funkcji dla istniejącego rozliczenia od 2026-02-15 do 2026-03-10.

Zatwierdzenie jest osobną procedurą i wykonuje trzy czynności: ustawia status zatwierdzony we wszystkich wierszach dokumentu rozliczenia, nadaje numer rozliczenia i zapisuje wpis w historii zdarzeń. W opisywanym wdrożeniu numer składa się z roku, miesiąca, numeru kolejnego i oznaczenia typu dokumentu rozliczeniowego, a przykład używa własnego, uproszczonego numeru, na przykład 2026-03-000001.

Cała procedura działa w jednej transakcji z opcją SET XACT_ABORT ON, więc błąd w dowolnym kroku cofa wszystkie trzy zmiany naraz. Numer pochodzi z sekwencji, a sekwencja nie wraca po wycofanej transakcji i może zostawić lukę w numeracji. Gdy numeracja ma być ciągła, potrzebny jest licznik w tabeli, jak w artykule o indeksie z licznika w formularzu i API.

CREATE PROCEDURE dbo.zatwierdz_rozliczenie @id_rozliczenia int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @numer varchar(20);

    BEGIN TRANSACTION;

    SELECT @numer = CONCAT(CONVERT(char(7), z.data_do, 126), '-',
                           RIGHT(CONCAT('000000', NEXT VALUE FOR dbo.numer_rozliczenia), 6))
    FROM dbo.rozliczenia AS z
    WHERE z.id_rozliczenia = @id_rozliczenia AND z.status = 'B';

    IF @numer IS NULL
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50001, N'Rozliczenie nie istnieje albo nie jest w buforze.', 1;
    END;

    UPDATE dbo.wiersze_rozliczen SET status = 'Z' WHERE id_rozliczenia = @id_rozliczenia;
    UPDATE dbo.rozliczenia SET status = 'Z', numer = @numer WHERE id_rozliczenia = @id_rozliczenia;
    INSERT INTO dbo.historia_rozliczen (id_rozliczenia, zdarzenie)
    VALUES (@id_rozliczenia, CONCAT(N'Zatwierdzono rozliczenie ', @numer));

    COMMIT TRANSACTION;
END;

Przykład ilustracyjny. Procedura zatwierdza rozliczenie z bufora.

Wynik rozliczenia i ruchy, z których powstał, trafiają później do raportów. Architekturę osobnego modelu raportowego opisujemy w artykule o Power BI nad bazą WMS, a wskaźniki, które da się policzyć z samych ruchów, w tekście o wskaźnikach magazynu z danych WMS. Od strony użytkownika programu rozliczenie opisuje strona o rozliczeniu kosztów magazynowych, a układ wielu właścicieli towaru w jednym systemie omawia artykuł o WMS dla operatora 3PL.

Pytania przed budową własnego rozliczenia

Przed przeniesieniem reguły do własnego rozliczenia odpowiedz na cztery pytania.

• Data: czy dzień rozliczenia ma wynikać z daty dokumentu, czy z daty zapisanej osobno, na przykład na fakturę.
• Przyjęcie i wydanie: czy paleta przyjęta i wydana tego samego dnia ma być paletodobą.
• Brak ceny: czy wiersz bez pozycji w cenniku ma pozostać widoczny w wyniku, czy zatrzymać rozliczenie.
• Okresy: czy jeden kontrahent może mieć dwa rozliczenia obejmujące ten sam dzień, także w różnych magazynach.