L Riley L Riley - 1 month ago 6
SQL Question

SQL Server 2008 version of OVER(... Rows Unbounded Preceeding)

Looking for help in converting this to SQL Server 2008 friendly as I just can't work it out. I've tried cross applies and inner joins (not saying I did them right) to no avail... Any suggestions?

What this essentially does is have a table of stock and a table of orders.
and combine the two to show me what to pick once the stock is taken away (see my previous question for more details More Details)

WITH ADVPICK
AS (SELECT 'A' AS PlaceA,
placeb,
CASE
WHEN picktime = '00:00' THEN '07:00'
ELSE ISNULL(picktime, '12:00')
END AS picktime,
Cast(product AS INT) AS product,
prd_description,
-qty AS Qty
FROM t_pick_orders
UNION ALL
SELECT 'A' AS PlaceA,
placeb,
'0',
Cast(code AS INT) AS product,
NULL,
stock
FROM t_pick_stock),
STOCK_POST_ORDER
AS (SELECT *,
Sum(qty)
OVER (
PARTITION BY placeb, product
ORDER BY picktime ROWS UNBOUNDED PRECEDING ) AS new_qty
FROM ADVPICK)
SELECT *,
CASE
WHEN new_qty > qty THEN new_qty
ELSE qty
END AS order_shortfall
FROM STOCK_POST_ORDER
WHERE new_qty < 0
ORDER BY placeb,
picktime,
product


Now the whole sum over partition by order by is SQL Server 2012+ however I have two servers that run on 2008 and so need it converted...

EDIT: Expected Results format

+--------+--------+----------+---------+-----------+-------+---------+-----------------+
| PlaceA | PlaceB | Picktime | product | Prd_Descr | qty | new_qty | order_shortfall |
+--------+--------+----------+---------+-----------+-------+---------+-----------------+
| BW | AMES | 16:00 | 1356 | Product A | -1330 | -17 | -17 |
| BW | AMES | 16:00 | 17 | Product B | -48 | -42 | -42 |
| BW | AMES | 17:00 | 1356 | Product A | -840 | -857 | -840 |
| BW | AMES | 18:00 | 1356 | Product A | -770 | -1627 | -770 |
| BW | AMES | 18:00 | 17 | Product B | -528 | -570 | -528 |
| BW | AMES | 19:00 | 1356 | Product A | -700 | -2327 | -700 |
| BW | AMES | 20:00 | 1356 | Product A | -910 | -3237 | -910 |
| BW | AMES | 20:00 | 8009 | Product C | -192 | -52 | -52 |
| BW | AMES | 20:00 | 897 | Product D | -90 | -10 | -10 |
+--------+--------+----------+---------+-----------+-------+---------+-----------------+

Answer

One straight-forward way to do it is to use a correlated sub-query in CROSS APPLY.

If your table is more or less large, then your next question would be how to make it fast. Index on PlaceB, Product, PickTime INCLUDE (Qty) would help. But, if your table is really large, cursor would be better.

WITH
ADVPICK
AS
(
    SELECT 'A' as PlaceA,PlaceB, case when PickTime = '00:00' then '07:00' else isnull(picktime,'12:00') end as picktime, cast(Product as int) as product, Prd_Description, -Qty AS Qty FROM t_pick_orders
    UNION ALL
    SELECT 'A' as PlaceA,PlaceB, '0', cast(Code as int) as product, NULL, Stock FROM t_pick_stock
)
,stock_post_order
AS
(
    SELECT
        *
    FROM
        ADVPICK AS Main
        CROSS APPLY
        (
            SELECT SUM(Sub.Qty) AS new_qty
            FROM ADVPICK AS Sub
            WHERE
                Sub.PlaceB = Main.PlaceB
                AND Sub.Product = Main.Product
                AND T.PickTime <= Main.PickTime
        ) AS A
)
SELECT
    *,
    CASE WHEN new_qty > qty THEN new_qty ELSE qty END AS order_shortfall
FROM
    stock_post_order
WHERE
    new_qty < 0
ORDER BY PlaceB, picktime, product;

Oh, and if PickTime is not unique, you'll get somewhat different results to original query with SUM() OVER. If you need exactly same results, you need to use some extra column (like ID) to resolve the ties.