• ZZartin - Tuesday, February 6, 2018 10:38 AM

    Well your sample data doesn't seem to match the data you described explanation order 5 is actually a new group and doesn't sum out to more than 10 until order 7.  But based on your sample data this might work, although it's fairly messy and i'm sure someone can do better.  Also what do you expect to happen if you jump more than 10 in between 2 orders, is that two baskets?


    WITH TEMP_CTE AS(
    SELECT *, (SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 10) + 1 AS BASKET_ID
    FROM #TestTable
    ), TEMP_CTE_TWO AS(
    SELECT groupID, MAX(BASKET_ID) AS MAX_BASKET FROM TEMP_CTE
    GROUP BY groupID
    )
    SELECT *, TEMP_CTE.BASKET_ID + PREV_BASKET_ID AS RUNNING_BUCKET FROM TEMP_CTE
    CROSS APPLY(
    SELECT groupID AS groupID_SUM, SUM(MAX_BASKET) OVER(ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - MAX_BASKET AS PREV_BASKET_ID FROM TEMP_CTE_TWO
    ) PREVIOUS_BASKET
    WHERE
    TEMP_CTE.groupID = PREVIOUS_BASKET.groupID_SUM

    I will give this a go when I have my laptop.
    Inhave pre conditioned the order, therefore if an order has >10 items it splits the order into two, or if greater than 30 it's three groups. I have assigned a letter code to that order for example order1_A order1_B and so on. So in that list you won't find an order greater than 10.
    Sorry about the data being incorrect .I am working with a large dataset and quickly typed that up.

    On another note. If a new wave of orders come through, is there a way to add the groupid and current number of items in groupid to a temp table so on the new wave of orders, it checks for the current number of items against if new wave groupid is same as groupid in temp table and brings back the value, so let's say groupid 3 currently has 8 items, the first order in new list happens to be for groupid 3, that order has 3 items therefore current total is now 8 and so on...