Zakresy od-do w cenniku: wykrywanie nakładania i wybór najbliższego zakresu w T-SQL

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

Walidacja zakresów od-do w cenniku T-SQL sprowadza się do jednego warunku: dwa zakresy zamknięte nakładają się wtedy, gdy początek każdego jest nie większy niż koniec drugiego. Ten sam cennik musi jeszcze dla każdego dnia dać dokładnie jedną cenę, także gdy liczba palet nie mieści się w żadnym zakresie.

Warunek a.od <= b.do AND b.od <= a.do wykrywa każde nakładanie: częściowe, obejmujące, identyczne i stykające się granicą. Cenę dla liczby palet wybiera OUTER APPLY z TOP (1), w którym zakres obejmujący liczbę ma pierwszeństwo przed najbliższym, a dwa dalsze klucze sortowania rozstrzygają remisy.

W cennikach z zakresami liczby palet zakres obejmujący liczbę palet z danego dnia wyznacza cenę całego dnia, a nie progresywnie. Gdy żaden zakres nie obejmuje liczby palet, wybierany jest zakres o najbliższej górnej granicy (przykład reguły). Zapytania w artykule napisaliśmy od nowa z nazwami i cenami przykładowymi. Uruchomiliśmy je na SQL Server 2022 Express przy dwóch poziomach zgodności, 130 i 150, a wyniki były identyczne.

Zakres cennika jako przedział zamknięty

Przykładowa tabela cennik_zakresow wskazuje kontrahenta i kod nośnika, a zakres opisują kolumny zakres_dolny i zakres_gorny. Do tego dochodzą cena oraz znacznik aktywności. Zakres jest przedziałem zamkniętym: obie granice należą do zakresu, więc 1-9 obejmuje liczby od 1 do 9 włącznie. Druga tabela, stan_dnia, trzyma liczbę palet kontrahenta dla każdego dnia.

Dane testowe dają kontrahentowi 1 pięć aktywnych zakresów z malejącą ceną oraz jeden zakres nieaktywny, który posłuży do sprawdzenia, że wiersz nieaktywny nie blokuje zapisu. Kontrahent 2 ma jeden szeroki zakres, a kontrahent 3 dwa rozłączne. Kontrahent 9 nie ma cennika wcale.

CREATE TABLE dbo.cennik_zakresow (
    cennik_id     int IDENTITY(1,1) PRIMARY KEY,
    kontrahent_id int           NOT NULL,
    kod_nosnika   varchar(10)   NOT NULL,
    zakres_dolny  int           NOT NULL,
    zakres_gorny  int           NOT NULL,
    cena          decimal(10,2) NOT NULL,
    aktywny       bit           NOT NULL DEFAULT 1);
CREATE TABLE dbo.stan_dnia (
    dzien         date        NOT NULL,
    kontrahent_id int         NOT NULL,
    kod_nosnika   varchar(10) NOT NULL,
    liczba_palet  int         NOT NULL);

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

INSERT dbo.cennik_zakresow (kontrahent_id, kod_nosnika, zakres_dolny, zakres_gorny, cena, aktywny) VALUES
 (1,'EUR', 1, 9,10.00,1), (1,'EUR',11,30, 8.50,1), (1,'EUR',31,39, 7.00,1), (1,'EUR',40,60, 6.00,1),
 (1,'EUR',61,80, 5.00,1), (1,'EUR',91,95, 3.00,0),            -- ostatni zakres jest nieaktywny
 (2,'EUR', 1,100,5.00,1), (3,'EUR', 1, 1,20.00,1), (3,'EUR', 6,20,12.00,1);
INSERT dbo.stan_dnia VALUES
 ('2026-03-01',1,'EUR',5), ('2026-03-02',1,'EUR',11), ('2026-03-03',1,'EUR',33),
 ('2026-03-04',1,'EUR',100), ('2026-03-05',3,'EUR',4), ('2026-03-06',3,'EUR',20),
 ('2026-03-07',9,'EUR',4);

Przykład ilustracyjny. Liczby i ceny są wymyślone.

Jeden warunek nakładania dwóch przedziałów

Dwa przedziały zamknięte nachodzą na siebie, gdy a.od <= b.do AND b.od <= a.do. Warunek obejmuje zakres częściowo wspólny, obejmujący, identyczny i stykający się granicą. W teście zakres 9-10 został odrzucony wobec 1-9 wyłącznie przez wspólną granicę 9, bo liczba 10 jest wolna. Przy liczbach całkowitych zakresy 1-9 i 11-30 sąsiadują bez nakładania.

Zakresy zamknięte nakładają się, gdy początek każdego jest nie większy niż koniec drugiego; sprawdzenie samych końców tego nie gwarantuje.

Kuszące bywa sprawdzenie, czy któryś koniec istniejącego zakresu mieści się w nowym: zakres_dolny BETWEEN @od AND @do lub zakres_gorny BETWEEN @od AND @do. Taki warunek nie widzi zakresu obejmującego. Dla istniejącego 1-100 i nowego 20-30 daje 0 trafień, a test przecięcia daje 1.

-- kontrahent 2 ma zakres 1-100, nowy zakres to 20-30
DECLARE @k int = 2, @r varchar(10) = 'EUR', @od int = 20, @do int = 30;
SELECT 'sprawdzenie końców (BETWEEN)' AS wariant, COUNT(*) AS znalezione
FROM dbo.cennik_zakresow AS c
WHERE c.kontrahent_id = @k AND c.kod_nosnika = @r AND c.aktywny = 1
  AND (c.zakres_dolny BETWEEN @od AND @do OR c.zakres_gorny BETWEEN @od AND @do)
UNION ALL
SELECT 'test przecięcia przedziałów', COUNT(*)
FROM dbo.cennik_zakresow AS c
WHERE c.kontrahent_id = @k AND c.kod_nosnika = @r AND c.aktywny = 1
  AND c.zakres_dolny <= @do AND @od <= c.zakres_gorny;

Przykład ilustracyjny. Sprawdzenie końców zwraca 0, test przecięcia zwraca 1.

Zapis z NOT EXISTS i wyłączeniem edytowanego wiersza

Zapis zakresu to jedna instrukcja INSERT ... SELECT ... WHERE NOT EXISTS (...), a edycja to UPDATE ... WHERE ... AND NOT EXISTS (...). Warunek NOT EXISTS porównuje nowy zakres tylko z zakresami aktywnymi tego samego kontrahenta i kodu nośnika. Zakres nieaktywny nie blokuje: w teście zakres 91-95 zapisano obok nieaktywnego 91-95, a zakresy dla innego kodu nośnika nie przeszkadzają w ogóle.

Wartość @@ROWCOUNT równa 0 oznacza, że zakres odrzucono. Warunek do >= od sprawdza osobna instrukcja przed zapisem, bo zakres odwrócony nie jest nakładaniem: w teście zakres 20-15 odrzucił komunikat o niepoprawnych granicach.

Przy edycji wiersz edytowany wyłącza się z porównania warunkiem cennik_id <> @cennik_id, inaczej zakres kolidowałby sam ze sobą. Zmiana samej ceny w zakresie 11-30 przechodzi, a rozszerzenie do 9-30 jest odrzucone jako nakładające się na 1-9. Procedura zakłada, że kontrahent i kod nośnika przekazane przy edycji są takie same jak w edytowanym wierszu; edycja zmienia tylko zakres i cenę.

CREATE OR ALTER PROCEDURE dbo.zapisz_zakres_ceny
    @cennik_id int, @kontrahent_id int, @kod_nosnika varchar(10), @od int, @do int, @cena decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    IF @do < @od BEGIN SELECT N'Górna granica nie może być mniejsza od dolnej.' AS komunikat; RETURN; END;
    IF @cennik_id = 0
        INSERT dbo.cennik_zakresow (kontrahent_id, kod_nosnika, zakres_dolny, zakres_gorny, cena)
        SELECT @kontrahent_id, @kod_nosnika, @od, @do, @cena
        WHERE NOT EXISTS (SELECT 1 FROM dbo.cennik_zakresow AS c
                          WHERE c.kontrahent_id = @kontrahent_id AND c.kod_nosnika = @kod_nosnika
                            AND c.aktywny = 1
                            AND c.zakres_dolny <= @do AND @od <= c.zakres_gorny);
    ELSE
        UPDATE dbo.cennik_zakresow SET zakres_dolny = @od, zakres_gorny = @do, cena = @cena
        WHERE cennik_id = @cennik_id
          AND NOT EXISTS (SELECT 1 FROM dbo.cennik_zakresow AS c
                          WHERE c.kontrahent_id = @kontrahent_id AND c.kod_nosnika = @kod_nosnika
                            AND c.aktywny = 1
                            AND c.cennik_id <> @cennik_id           -- edytowany wiersz nie koliduje sam ze sobą
                            AND c.zakres_dolny <= @do AND @od <= c.zakres_gorny);
    SELECT CASE WHEN @@ROWCOUNT = 1 THEN N'Zapisano.'
                ELSE N'Zakres zachodzi na istniejący zakres.' END AS komunikat;
END;

Przykład ilustracyjny. Procedura zwraca komunikat o zapisie albo o odrzuceniu zakresu.

EXEC dbo.zapisz_zakres_ceny 0, 1, 'EUR', 5, 10, 9;     -- w środku 1-9: odrzucone
EXEC dbo.zapisz_zakres_ceny 0, 1, 'EUR', 9, 10, 9;     -- granica 9 wspólna z 1-9 (10 jest wolne): odrzucone
EXEC dbo.zapisz_zakres_ceny 0, 1, 'EUR', 0, 100, 9;    -- obejmuje wszystkie: odrzucone
EXEC dbo.zapisz_zakres_ceny 0, 1, 'EUR', 20, 15, 9;    -- górna granica mniejsza od dolnej: odrzucone
EXEC dbo.zapisz_zakres_ceny 0, 1, 'EUR', 81, 90, 4;    -- wolne: zapisane
EXEC dbo.zapisz_zakres_ceny 0, 1, 'EUR', 91, 95, 3;    -- nieaktywny zakres 91-95 nie blokuje: zapisane
EXEC dbo.zapisz_zakres_ceny 0, 1, 'KRAT', 1, 10, 9;    -- inny kod nośnika: zapisane
EXEC dbo.zapisz_zakres_ceny 2, 1, 'EUR', 11, 30, 8.60; -- edycja: ten sam zakres, inna cena: zapisane
EXEC dbo.zapisz_zakres_ceny 2, 1, 'EUR', 9, 30, 8.60;  -- edycja: rozszerzenie na 1-9: odrzucone

Przykład ilustracyjny. Dziewięć wywołań na danych testowych.

Przykład. Wynik uruchomienia na SQL Server 2022 Express.

Przy równoległych zapisach sprawdzenie i wstawienie powinny stać w jednej transakcji z podpowiedzią UPDLOCK i HOLDLOCK w podzapytaniu. Pojedyncza instrukcja INSERT ... SELECT ... WHERE NOT EXISTS ma znacznie krótsze okno wyścigu, ale go nie eliminuje; gwarancję daje podpowiedź blokady.

Test, w którym druga sesja startuje po wstawieniu zakresu przez pierwszą, wyścigu nie odtwarza: druga sesja trafia na zablokowany wiersz i czeka także bez podpowiedzi. Wyścig odtworzył dopiero test z rozdzielonym sprawdzeniem i wstawieniem, w którym obie sesje najpierw sprawdzają brak nakładania i czekają 4 sekundy, po czym wstawiają zakres. Bez podpowiedzi w tabeli zostały dwa nakładające się zakresy. Z podpowiedzią druga sesja czekała na zatwierdzenie pierwszej i nie wstawiła nic.

DECLARE @kontrahent_id int = 1, @kod_nosnika varchar(10) = 'EUR', @od int = 200, @do int = 210, @cena decimal(10,2) = 2.00;
BEGIN TRANSACTION;
INSERT dbo.cennik_zakresow (kontrahent_id, kod_nosnika, zakres_dolny, zakres_gorny, cena)
SELECT @kontrahent_id, @kod_nosnika, @od, @do, @cena
WHERE NOT EXISTS (SELECT 1 FROM dbo.cennik_zakresow AS c WITH (UPDLOCK, HOLDLOCK)
                  WHERE c.kontrahent_id = @kontrahent_id AND c.kod_nosnika = @kod_nosnika
                    AND c.aktywny = 1
                    AND c.zakres_dolny <= @do AND @od <= c.zakres_gorny);
SELECT @@ROWCOUNT AS wstawiono;
COMMIT TRANSACTION;

Przykład ilustracyjny. Sprawdzenie i wstawienie w jednej transakcji z podpowiedzią blokady.

Cena dla liczby palet w danym dniu

Cenę dla liczby palet n wybiera OUTER APPLY z podzapytaniem TOP (1) i sortowaniem w czterech krokach. Pierwszy klucz, CASE WHEN n BETWEEN od AND do THEN 0 ELSE 1 END, daje pierwszeństwo zakresowi obejmującemu liczbę. Kolejne to ABS(do - n), czyli odległość od górnej granicy, a potem od i cennik_id, które rozstrzygają remisy. Koszt dnia to liczba palet razy cena.

SELECT d.dzien, d.kontrahent_id, d.liczba_palet, p.cena, d.liczba_palet * p.cena AS koszt_dnia
FROM dbo.stan_dnia AS d
OUTER APPLY (SELECT TOP (1) c.cena
             FROM dbo.cennik_zakresow AS c
             WHERE c.kontrahent_id = d.kontrahent_id
               AND c.kod_nosnika = d.kod_nosnika AND c.aktywny = 1
             ORDER BY CASE WHEN d.liczba_palet BETWEEN c.zakres_dolny AND c.zakres_gorny THEN 0 ELSE 1 END,
                      ABS(c.zakres_gorny - d.liczba_palet), c.zakres_dolny, c.cennik_id) AS p
ORDER BY d.dzien;

Przykład ilustracyjny. Cena dnia z zakresu obejmującego, a poza zakresami z najbliższej górnej granicy.

Dla kontrahenta 1 pięć palet daje cenę 10,00 i koszt 50,00, jedenaście palet 8,50 i 93,50, a trzydzieści trzy palety 7,00 i 231,00. Sto palet leży poza każdym zakresem, więc obowiązuje najbliższa górna granica, czyli zakres 61-80 z ceną 5,00 i kosztem 500,00. Dzień kontrahenta 9, który nie ma cennika, zostaje w wyniku z ceną NULL.

Przykład. Zakresy i ceny są wymyślone.

Najbliższy zakres i wybór metryki

Słowo „najbliższy” jest decyzją, którą trzeba zapisać w regule, bo dwie naturalne metryki dają różne ceny. Dla 4 palet i zakresów 1-1 z ceną 20,00 oraz 6-20 z ceną 12,00 odległość do górnej granicy wybiera 20,00, bo odległości wynoszą 3 i 16. Odległość od przedziału wybiera 12,00, bo odległości wynoszą 3 i 2. W przykładzie reguły przyjęto pierwszą metrykę, czyli najbliższą górną granicę.

-- 4 palety u kontrahenta 3 (zakresy 1-1 i 6-20): odległość do górnej granicy a odległość od przedziału
SELECT d.liczba_palet, g.cena AS wg_gornej_granicy, w.cena AS wg_odleglosci_od_przedzialu
FROM dbo.stan_dnia AS d
OUTER APPLY (SELECT TOP (1) c.cena FROM dbo.cennik_zakresow AS c
             WHERE c.kontrahent_id = d.kontrahent_id AND c.kod_nosnika = d.kod_nosnika AND c.aktywny = 1
             ORDER BY CASE WHEN d.liczba_palet BETWEEN c.zakres_dolny AND c.zakres_gorny THEN 0 ELSE 1 END,
                      ABS(c.zakres_gorny - d.liczba_palet), c.zakres_dolny, c.cennik_id) AS g
OUTER APPLY (SELECT TOP (1) c.cena FROM dbo.cennik_zakresow AS c
             WHERE c.kontrahent_id = d.kontrahent_id AND c.kod_nosnika = d.kod_nosnika AND c.aktywny = 1
             ORDER BY CASE WHEN d.liczba_palet < c.zakres_dolny THEN c.zakres_dolny - d.liczba_palet
                           WHEN d.liczba_palet > c.zakres_gorny THEN d.liczba_palet - c.zakres_gorny
                           ELSE 0 END,
                      c.zakres_dolny, c.cennik_id) AS w
WHERE d.dzien = '2026-03-05';

Przykład ilustracyjny. Wynik: 20,00 wobec 12,00.

Druga decyzja dotyczy rodzaju złączenia. OUTER APPLY zachowuje dzień kontrahenta bez cennika z ceną NULL, a CROSS APPLY go pomija. W teście było 7 dni wobec 6. Dzień bez ceny widać wtedy w wyniku, zamiast zniknąć z sumy.

-- dzień kontrahenta bez cennika: OUTER APPLY go zachowuje, CROSS APPLY pomija
SELECT COUNT(*) AS dni_outer_apply
FROM dbo.stan_dnia AS d
OUTER APPLY (SELECT TOP (1) c.cena FROM dbo.cennik_zakresow AS c
             WHERE c.kontrahent_id = d.kontrahent_id AND c.kod_nosnika = d.kod_nosnika
               AND c.aktywny = 1 ORDER BY c.cennik_id) AS p;
SELECT COUNT(*) AS dni_cross_apply
FROM dbo.stan_dnia AS d
CROSS APPLY (SELECT TOP (1) c.cena FROM dbo.cennik_zakresow AS c
             WHERE c.kontrahent_id = d.kontrahent_id AND c.kod_nosnika = d.kod_nosnika
               AND c.aktywny = 1 ORDER BY c.cennik_id) AS p;

Przykład ilustracyjny. Siedem dni wobec sześciu.

Pułapki zakresów otwartych i nawiasów

Warunek z OR bez nawiasów wciąga wiersze innych kontrahentów, bo AND wiąże silniej niż OR. W teście dwa trafienia bez nawiasów pochodziły z zakresów kontrahentów 1 i 3, a z nawiasami wynik wynosił 0.

DECLARE @k int = 2, @od int = 20, @do int = 30;
SELECT 'bez nawiasów' AS wariant, COUNT(*) AS znalezione
FROM dbo.cennik_zakresow AS c
WHERE c.kontrahent_id = @k AND c.aktywny = 1
  AND c.zakres_dolny BETWEEN @od AND @do OR c.zakres_gorny BETWEEN @od AND @do
UNION ALL
SELECT 'z nawiasem', COUNT(*)
FROM dbo.cennik_zakresow AS c
WHERE c.kontrahent_id = @k AND c.aktywny = 1
  AND (c.zakres_dolny BETWEEN @od AND @do OR c.zakres_gorny BETWEEN @od AND @do);

Przykład ilustracyjny. Dwa trafienia wobec zera.

Zakres otwarty, czyli brak górnej granicy zapisany jako NULL, wymyka się walidacji. Porównanie z NULL daje UNKNOWN, więc test przecięcia zwraca 0, a z ISNULL(zakres_gorny, 2147483647) zwraca 1. Zapisuj jawną wartość największą albo użyj ISNULL.

-- zakres otwarty: 50 i więcej (NULL w górnej granicy), nowy zakres 60-70
CREATE TABLE dbo.cennik_otwarty (id int IDENTITY(1,1) PRIMARY KEY, zakres_dolny int NOT NULL, zakres_gorny int NULL);
INSERT dbo.cennik_otwarty (zakres_dolny, zakres_gorny) VALUES (50, NULL);
DECLARE @od int = 60, @do int = 70;
SELECT 'bez ISNULL' AS wariant, COUNT(*) AS znalezione
FROM dbo.cennik_otwarty AS c WHERE c.zakres_dolny <= @do AND @od <= c.zakres_gorny
UNION ALL
SELECT 'z ISNULL', COUNT(*)
FROM dbo.cennik_otwarty AS c WHERE c.zakres_dolny <= @do AND @od <= ISNULL(c.zakres_gorny, 2147483647);

Przykład ilustracyjny. Bez ISNULL 0 trafień, z ISNULL 1.

Istniejące dane sprawdza samozłączenie z warunkiem b.cennik_id > a.cennik_id i tym samym testem przecięcia. Po ręcznym wstawieniu zakresu 25-45 dla kontrahenta 1, z pominięciem procedury, zapytanie zwróciło trzy pary, każdą z tym zakresem. Bez ręcznego wiersza wynik jest pusty.

INSERT dbo.cennik_zakresow (kontrahent_id, kod_nosnika, zakres_dolny, zakres_gorny, cena) VALUES (1, 'EUR', 25, 45, 1.00);
SELECT a.kontrahent_id, a.kod_nosnika, a.cennik_id AS id_a, a.zakres_dolny AS od_a, a.zakres_gorny AS do_a,
       b.cennik_id AS id_b, b.zakres_dolny AS od_b, b.zakres_gorny AS do_b
FROM dbo.cennik_zakresow AS a
JOIN dbo.cennik_zakresow AS b ON b.kontrahent_id = a.kontrahent_id AND b.kod_nosnika = a.kod_nosnika
                    AND a.aktywny = 1 AND b.aktywny = 1 AND b.cennik_id > a.cennik_id
                    AND a.zakres_dolny <= b.zakres_gorny AND b.zakres_dolny <= a.zakres_gorny;

Przykład ilustracyjny. Trzy pary, w każdej zakres 25-45.

Przykład: cennik zakresowy kontrahenta 1

Przykład: kontrahent 1 ma w cenniku dla kodu nośnika EUR zakresy 1-9, 11-30, 31-39, 40-60 i 61-80 z malejącą ceną. Próba dopisania zakresu 5-10 kończy się komunikatem „Zakres zachodzi na istniejący zakres.”, a zakres 81-90 zapisuje się bez problemu. Dla dnia z 11 paletami zapytanie z przykładu wybiera zakres 11-30 i liczy 11 razy 8,50. Dla 100 palet nie ma zakresu obejmującego, więc obowiązuje 61-80 jako najbliższy po górnej granicy. Zapytanie kontrolne z samozłączeniem sprawdza później, czy ręczne poprawki w bazie nie wprowadziły nakładających się zakresów. Liczby w tym akapicie są przykładowe.

Stan palet na każdy dzień okresu liczy inny wzorzec, opisany w artykule o paletodobie w T-SQL, a tu zajmujemy się wyłącznie ceną dla znanej liczby palet. Dwa sąsiednie opisy z tej serii dotyczą raportu z numerami w jednej kolumnie oraz salda lokalizacji w T-SQL, a trzeci omawia indeks filtrowany dla numerów. Szersze tło o zapytaniach raportowych daje opis raportowania w SQL, a pełną listę artykułów znajdziesz w spisie artykułów o SQL Server.

Pytania przed zaprojektowaniem własnego cennika zakresowego

Zanim zaprojektujesz cennik z zakresami, odpowiedz sobie na cztery pytania.

• Cena: czy cała liczba palet z danego dnia ma być liczona po jednej cenie, czy progresywnie.
• Poza zakresem: czy liczba palet poza wszystkimi zakresami ma dostać cenę najbliższego zakresu, czy ma zakończyć się błędem.
• Najbliższy: czy „najbliższy” ma oznaczać odległość do górnej granicy, czy odległość od przedziału.
• Otwarty: czy dopuszczasz zakres otwarty i jak go zapisujesz w bazie.