Zliczanie rabatów qwerty

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