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