Skip to content

Instantly share code, notes, and snippets.

@hrbrmstr

hrbrmstr/ex.sql

Created Oct 18, 2018
Embed
What would you like to do?
;
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment