Zliczanie rabatów - cena końcowa - MS SQL

Witam,
Potrzebuję wyliczyć cenę Końcową.
Są dwa rodzaje rabatów:
cennik_typ = 1 - rabat się dodaje
cennik_typ = 2 - rabat się mnoży

Kolejność stosowania rabatu = lp

http://sqlfiddle.com/#!18/2f097/7

napisałem zapytanie ale jest mało wydajne przy milionie rekordów

Wynik:

DECLARE @kontrahent_id INT = 0;
DECLARE @i INT = 0;
DECLARE @count INT = 0;

SET @kontrahent_id = (SELECT TOP 1 kontrahent_id FROM test1);
SET @count =  (SELECT COUNT(*) FROM test1 WHERE kontrahent_id = @kontrahent_id) * 2;

WHILE @i <= @count
BEGIN

    IF (SELECT TOP 1 lp FROM test1 WHERE lp = 1 AND kwota_rabatu = 0 AND cena_obliczona = 0 AND kontrahent_id = @kontrahent_id ORDER BY kontrahent_id, lp, towar_id) = 1
    UPDATE t1
    SET
    t1.kwota_rabatu         = t1.cena_katalogowa - ((t1.cena_katalogowa * 100) - ((t1.cena_katalogowa * (100 + t1.rabat)) - (t1.cena_katalogowa * 100))) / 100,
    t1.cena_obliczona       = ((t1.cena_katalogowa * 100) - ((t1.cena_katalogowa * (100 + t1.rabat)) - (t1.cena_katalogowa * 100))) / 100,
    t1.kwota_rabatu_sum     = t1.cena_katalogowa - ((t1.cena_katalogowa * 100) - ((t1.cena_katalogowa * (100 + t1.rabat)) - (t1.cena_katalogowa * 100))) / 100,
    t1.cena_obliczona_sum   = ((t1.cena_katalogowa * 100) - ((t1.cena_katalogowa * (100 + t1.rabat)) - (t1.cena_katalogowa * 100))) / 100   
    FROM test1 AS t1
    JOIN (SELECT TOP 1 t2.kontrahent_id, t2.lp, t2.towar_id FROM test1 t2 WHERE t2.kwota_rabatu = 0 AND t2.cena_obliczona = 0 AND t2.kontrahent_id = @kontrahent_id ORDER BY t2.kontrahent_id, t2.lp) t2 ON t1.kontrahent_id = t2.kontrahent_id AND t1.lp = t2.lp AND t1.towar_id = t2.towar_id
    WHERE t1.kontrahent_id = @kontrahent_id

    SET @i = @i + 1

    IF (SELECT TOP 1 lp FROM test1 WHERE kwota_rabatu = 0 AND cena_obliczona = 0 AND kontrahent_id = @kontrahent_id ORDER BY kontrahent_id, lp, towar_id) > 1

    UPDATE t1
    SET
    t1.kwota_rabatu         = CASE WHEN t1.cennik_typ = 1 THEN t1.cena_katalogowa - ((t1.cena_katalogowa * 100) - ((t1.cena_katalogowa * (100 + t1.rabat)) - (t1.cena_katalogowa * 100))) / 100
                                ELSE
                              CASE WHEN t1.cennik_typ = 2   THEN (t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp  AND t2.kontrahent_id = @kontrahent_id)) - (((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * 100) - (((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * (100 + t1.rabat)) - ((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * 100))) / 100
                                END
                                END,
    t1.kwota_rabatu_sum     = CASE WHEN t1.cennik_typ = 1 THEN t1.cena_katalogowa - ((t1.cena_katalogowa * 100) - ((t1.cena_katalogowa * (100 + t1.rabat)) - (t1.cena_katalogowa * 100))) / 100 + (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)
                                ELSE
                              CASE WHEN t1.cennik_typ = 2 THEN ((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) - (((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * 100) - (((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * (100 + t1.rabat)) - ((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * 100))) / 100) + (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)
                                END
                                END,
    t1.cena_obliczona       = CASE WHEN t1.cennik_typ = 1 THEN ((t1.cena_katalogowa * 100) - ((t1.cena_katalogowa * (100 + t1.rabat)) - (t1.cena_katalogowa * 100))) / 100
                                ELSE
                              CASE WHEN t1.cennik_typ = 2 THEN (((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * 100) - (((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * (100 + t1.rabat)) - ((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * 100))) / 100
                                END
                                END,
    t1.cena_obliczona_sum   = CASE WHEN t1.cennik_typ = 1 THEN t1.cena_katalogowa - (t1.cena_katalogowa - ((t1.cena_katalogowa * 100) - ((t1.cena_katalogowa * (100 + t1.rabat)) - (t1.cena_katalogowa * 100))) / 100 + (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id))
                                ELSE
                              CASE WHEN t1.cennik_typ = 2 THEN (((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * 100) - (((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * (100 + t1.rabat)) - ((t1.cena_katalogowa - (SELECT t2.kwota_rabatu_sum FROM test1 t2 WHERE t1.kontrahent_id = t2.kontrahent_id AND t1.towar_id = t2.towar_id AND (t1.lp - 1) = t2.lp AND t2.kontrahent_id = @kontrahent_id)) * 100))) / 100
                                END
                                END

    FROM test1 AS t1
    JOIN (SELECT TOP 1 t2.kontrahent_id, t2.lp, t2.towar_id FROM test1 t2 WHERE t2.kwota_rabatu = 0 AND t2.cena_obliczona = 0 AND t2.kontrahent_id = @kontrahent_id ORDER BY t2.kontrahent_id, t2.lp) t2 ON t1.kontrahent_id = t2.kontrahent_id AND t1.lp = t2.lp AND t1.towar_id = t2.towar_id
    WHERE  t1.kontrahent_id = @kontrahent_id

    SET @i = @i + 1

END

--obliczenia        
SELECT * FROM test1 ORDER BY kontrahent_id, towar_id, lp

--wynik końcowy
SELECT a.kontrahent_id, a.towar_id, a.cena_katalogowa, CAST(a.kwota_rabatu_sum AS DECIMAL(10, 2)) AS kwota_rabatu, CAST(a.cena_obliczona_sum AS DECIMAL(10, 2)) AS cena_obliczona
, CAST(((a.cena_katalogowa - CAST(a.cena_obliczona_sum AS DECIMAL(10, 2))) / a.cena_katalogowa) * 100 AS DECIMAL(10, 2)) AS rabat_efektywny
FROM test1 a
JOIN (SELECT MAX(b.lp) AS lp, b.towar_id FROM test1 b GROUP BY towar_id) AS b ON a.lp = b.Lp AND a.towar_id = b.towar_id
ORDER BY a.kontrahent_id, a.towar_id

Czytam to twoje zapytanie i nie mogę dojś do zamysłu jaki temu przyświeca.
Szybkie na milionie to nie będzie, patrząc na liczbę zapytan jaką ta pętla to generuj,
Opisz co definiuje to lp, albo jak to policzyć.

Lp to id cennika tylko oryginalnie w bazie mam tam liczby 100, 196, 217, dla ułatwienia robię insert do tabeli temp zamieniając id cennika na lp korzystając z ROW_NUMBER().
Liczenie dla towar_id = 47444

http://sqlfiddle.com/#!18/2f097/18

lp = 1 wynik = (cena_katalogowa - rabat)
lp = 2 wynik = (wynik - rabat)

Nie rozumiem przykładu dla towar_id=47444.

lp=1 -> cena_katalogowa = 46, rabat=28,41 i cennik_typ=1 => Zakładam, że wynik = cena katalogowa - rabat = 17,59 (mimo, że pisałeś, że rabat się dodaje, ale chyba się go odejmuje?)
lp=2 -> cennik_typ=2, rabat = 3.5 i pisałeś że się mnoży, więc 17,59*3,5 => 61,565? Chyba, że to mnożnik procentowy?

Zgaduję, że po rabatach kwota to: (46-28,41)*(1-0.035) ~ 16,97 ?

Pisząc
cennik_typ = 1 - rabat się dodaje
cennik_typ = 2 - rabat się mnoży (wyliczamy cenę po zastosowaniu poprzednich rabatów i od niej odejmujemy rabat)

miałem na myśli sposób łącznie kolejnych rabatów dla danego cennik_typ
rabat zawsze się odejmuje (na to czy będzie się dodawał ma wpływ wartość rabatu ze znakiem -)

Przykład: Dany towar ma zdefiniowane dwa cenniki 11% i 2% oraz cenę katalogową 100zł. Przy naliczaniu cennika (lp=1) „Dodaj” wyliczy się 13% ze 100 zł. Cena po rabacie będzie wynosiła: 100 zł – 13 zł = 87 zł. Przy cenniku (lp=2) „Mnóż”, uwzględniamy cennik jeden po drugim: 11% z 100 zł, czyli 89 zł, kolejno 2% z 89 zł, co daje cenę po rabacie: 87,22 zł.

Może zapytanie rekrusywne? Nie wiem jak z wydajnością. Proste ćwiczenie dla czytelnika mającego pod ręką tabelę z milionem rekordów ;-)

Idea jest taka:

1. Naliczamy rabaty dla lp=1
2. Naliczamy rabaty rekursywnie wybierająca parametry rabatu z kolejnego wiersza
3. Numerujemy wiersze malejąco względem lp w obrębie kontrahenta/towaru
4. Wybieramy pierwszy wiersz

Google: recursive sql query mssql

To szkic, więc wymaga dopracowania.

WITH naliczony_rabat(kontrahent_id,toward_id,...,wynik) as (
  select
    t.kontrahent_id,
    t.towar_id,
    ...
    case
      when t.cennik_typ=1 then t.cena_katalogowa+t.kwota_rabatu
      when t.cennik_typ=2 then t.cena_katalogowa*t.kwota_rabatu
    end wynik
  from test1 t where t.lp=1
  union all 
  select
    t.kontrahent_id,
    t.towar_id,
    ...
    case
      when t.cennik_typ=1 then p.wynik+t.kwota_rabatu
      when t.cennik_typ=2 then p.wynik*t.kwota_rabatu
    end wynik
  from test1  t
  join naliczony_rabat p  on p.kontrahent_id=... and p.lp+1=t.lp /* wybieramy kolejny poziom rekrusji, tj. lp+1  */
),
kroki_obliczen as (
  select row_number() over (partition by x.kontrahent_id,x.towar_id order by x.lp desc) rn,x.* from naliczony_rabat x
)
select p.* from kroki_obliczen p where p.rn=1;

Nie wiem czy rekurencyjne ma sens, ja chyba nie myślę, albo te dane przykładowe nie ułatwiają mi zadania, jedno jest pewne musisz odejśc od pętli i zmiennych.
Generalnie dojście do twoich wyników można osiągnąc tak:

update na cenniku o typie 1:

update 
	test1 
set 
	kwota_rabatu = cena_katalogowa - ((cena_katalogowa * 100) - ((cena_katalogowa * (100 + sumrabat.rabat)) - (cena_katalogowa * 100))) / 100,
	cena_obliczona = ((cena_katalogowa * 100) - ((cena_katalogowa * (100 + sumrabat.rabat)) - (cena_katalogowa * 100))) / 100
from
	test1
	inner join (select 
					kontrahent_id
					,towar_id
					,SUM(rabat) rabat
				from 
					test1
				where 
					cennik_typ=1
				group by 
					kontrahent_id
					,towar_id
			) sumrabat on sumrabat.kontrahent_id = test1.kontrahent_id and sumrabat.towar_id = test1.towar_id		
where 
	test1.cennik_typ = 1

Później to już wykodzystanie funkcji LAG:

with rpt as (
SELECT
  lag(kwota_rabatu,1,kwota_rabatu) over(partition by towar_id order by lp) pkwota_rabatu,
  lag(cena_obliczona,1,cena_obliczona) over(partition by towar_id order by lp) pcena_obliczona,
  * 
 FROM 
    test1 
)
SELECT
  lp
  ,cennik_typ
  ,kontrahent_id
  ,towar_id
  ,rabat
  ,cena_katalogowa
  ,case when cennik_typ=1 then 
    kwota_rabatu
  else
    cast(pkwota_rabatu + pcena_obliczona - ((pcena_obliczona * 100) - ((pcena_obliczona * (100 + rabat)) - (pcena_obliczona * 100))) / 100 as decimal(18,2))
  end 
    kwota_rabatu
  ,case when cennik_typ=1 then 
    cena_obliczona
  else
    CAST(((pcena_obliczona * 100) - ((pcena_obliczona * (100 + rabat)) - (pcena_obliczona * 100))) / 100 as decimal(18,2))
  end 
    cena_obliczona
FROM 
  rpt

http://sqlfiddle.com/#!18/1a97e/3

Teoretycznie najwyższe lp w ramach towaru i klienta da oczekiwany wynik.

Nie sprawdzi sie w przypadku gty cenników o typie 2 będzię wiecej niż jeden w ramach towaru i kontrahenta, to do poprawienia, tylko chce wiedzieć czy dobrze zrozumiałem ideę?

Nie możemy sumować wszystkich cenników typ = 1 w ramach towaru i kontrahenta, chyba że w ramach towaru i kontrahenta są tylko cenniki typ = 1, albo zapamiętamy cenę obliczoną przez cennik, cenniki przed cennikiem typ = 2.
Dla towar_id 47444, 47446, 47447 policzyło dobrze bo jest tylko po jednym cenniku w kolejności lp = 1, 2. Dla towar_id = 54675 jest źle policzone.
Cenniki w ramach towaru i kontrahenta mam z kilkoma wystąpieniami typ 1 i typ 2, (np 1, 1, 2, 1, 1, 2).

W moim sposobie wyliczania najpierw w ramach towaru i kontrahenta sprawdzam pierwszy cennik nie sprawdzając jaki jest jego typ, potem kolejne obliczam i w zależności od typu liczę rabat od ceny katalogowej lub od ceny obliczonej w poprzednim rekordzie.

Wynik mojego liczenia.

Zostańmy przy przykładzie z towarem 54675

Który ma sekwencje cenników 1,1,2,1 i dla uproszczenia kwoty rabatu R1,R2,R3,R4 (numeracja zgodna z lp, pomijając R3 to sprawa jest prosta bo liczę od ceny katalogowej, to pytanie o wzór policzenia R3
bo jak rozumiem liczę od od cena_katalogowa - (R1+R2)?

@yarel: miał racje przy takim założeniu zostaje rekursywne cte:

with cteR as (
SELECT
  lp
  ,cennik_typ
  ,kontrahent_id
  ,towar_id
  ,rabat
  ,cena_katalogowa
  ,cast(rabat/100*cena_katalogowa as money) kwota_rabatu
  ,cast(rabat/100*cena_katalogowa as money)kwota_rabatu_sum
  ,cast(cena_katalogowa  as money) cena_do_obliczen
from 
	test1
where
	lp=1
union all
SELECT
  t.lp
  ,t.cennik_typ
  ,t.kontrahent_id
  ,t.towar_id
  ,t.rabat
  ,t.cena_katalogowa
  ,cast(case when t.cennik_typ=1 then t.cena_katalogowa else t.cena_katalogowa - cteR.kwota_rabatu_sum end *  t.rabat/100
	as money) kwota_rabatu 
  ,cast(cteR.kwota_rabatu_sum  + (case when t.cennik_typ=1 then t.cena_katalogowa else t.cena_katalogowa - cteR.kwota_rabatu_sum end *  t.rabat/100)
   as money) kwota_rabatu_sum
   ,cast(case when t.cennik_typ=1 then t.cena_katalogowa else t.cena_katalogowa - cteR.kwota_rabatu_sum end 
	as money) cena_do_obliczen
from 
	test1 t
	inner join cteR on t.towar_id = cteR.towar_id and t.kontrahent_id = cteR.kontrahent_id and t.lp-1 = cteR.lp
)

select * from cter order by kontrahent_id ,towar_id,lp

http://sqlfiddle.com/#!18/2f097/61

A teraz zaproś kogoś z biznesu i zapytaj czy o tym myślał.

SQL się nadaje do customizacji oprogramowania jak [wulgaryzm]