Test 2 prism krótkie linie

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;