ex.sql

;
WITH CTE AS 
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY [product_id], shop_code 
  ORDER BY
    [doc_date]) - ROW_NUMBER() OVER (PARTITION BY [product_id], shop_code, mark_1 
  ORDER BY
    [doc_date]) AS grp 
  FROM
    ao
)
,
CTE1 AS 
(
  SELECT
    * 
  FROM
    CTE c OUTER APPLY ( 
    SELECT
      TOP 1 grp AS prvgrp 
    FROM
      CTE 
    WHERE
      mark_1 = 0 
      AND doc_date <= c.doc_date 
      AND [product_id] = c.[product_id] 
      AND shop_code = c.shop_code 
    ORDER BY
      doc_date DESC ) prev OUTER APPLY ( 
      SELECT
        TOP 1 grp AS nxtgrp 
      FROM
        CTE 
      WHERE
        mark_1 = 0 
        AND [product_id] = c.[product_id] 
        AND shop_code = c.shop_code 
        AND doc_date >= c.doc_date 
      ORDER BY
        doc_date) nxt
)
SELECT
  c.product_id,
  c.shop_code,
  c.doc_date,
  c.ship_count,
  c.mark_1,
  CASE
    WHEN
      med < ship_count 
    THEN
      med 
    WHEN
      ship_count < 0 
    THEN
      0 
    ELSE
      ship_count 
  END
  AS OUTPUT, c.ship_count - 
  CASE
    WHEN
      med < ship_count 
    THEN
      med 
    WHEN
      ship_count < 0 
    THEN
      0 
    ELSE
      ship_count 
  END
  AS output1 INTO test2 
FROM
  CTE1 c OUTER APPLY ( 
  SELECT
    MAX(med) AS med 
  FROM
    (
      SELECT
        PERCENTILE_DISC(0.5) WITHIN GROUP( 
      ORDER BY
        CASE
          WHEN
            ship_count <= 0 
          THEN
            0 
          ELSE
            ship_count 
        END
) OVER (PARTITION BY [product_id], shop_code, grp) AS med 
      FROM
        CTE 
      WHERE
        grp IN 
        (
          nxtgrp, prvgrp
        )
        AND [product_id] = c.[product_id] 
        AND shop_code = c.shop_code 
    )
    r)c2 
  ORDER BY
    doc_date