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

Question

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...

Expected Results:

+--------+--------+----------+---------+-----------+-------+---------+-----------------+
| 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 1

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) should 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 (PlaceB, Product, 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.