February 6, 2018 at 9:39 am
hi all,
as you can see below, i want to add an incrementing basket id if running total exceeds 10 for that group.
order 1 , 2 and 3 are for group id 3 but order 3 would exceed the running total of 10, therefore it goes into the next basket id.
order 4 is for a new group therefore next basket id.
order 5 is for group 7 by running total would exceed 10 therefore assigned next basket id.
orders 6 and 7 are for same group but different from previous group therefore will get next basket id.
and so on
SELECT *
INTO #TestTable
FROM (VALUES
('Order1',4,3)
,('Order2',3,3)
,('Order3',4,3)
,('Order4',5,7)
,('Order5',3,8)
,('Order6',4,8)
,('Order7',5,8)
)v(OrderID,items,groupID)
;
-- RESULTS
OrderID, items, groupID, RunningTotalInGroup, BasketID
Order1, 4, 3, 4, 1
Order2, 3, 3, 7, 1
Order3, 4, 3, 4, 2
Order4, 5, 7, 5, 3
Order5, 6, 7, 6, 4
Order6, 4, 8, 4, 5
Order7, 5, 8, 9, 5
i am looking at the following, but i am unable to add the >= 10 clause and increment to next basketID.
SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
February 6, 2018 at 10:38 am
Well your sample data doesn't seem to match the data you described in your 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
February 6, 2018 at 11:27 am
I actually used the Quirky Update which is great for this sort of things. I would test it as I'm not sure about updating 2 columns at once. Check the full explanation (with tests and alternatives) in here: Solving the Running Total and Ordinal Rank Problems (Rewritten) - SQLServerCentral
SELECT IDENTITY(INT,1,1) AS RowID,
v.OrderID,
v.items,
v.groupID,
0 AS RunningTotalInGroup,
0 AS BasketID
INTO #TestTable
FROM (VALUES
('Order1',4,3)
,('Order2',3,3)
,('Order3',4,3)
,('Order4',5,7)
,('Order5',6,8)
,('Order6',4,8)
,('Order7',5,8)
)v(OrderID,items,groupID)
ORDER BY v.groupID, v.OrderID
;
CREATE CLUSTERED INDEX ci_testTable ON #TestTable (groupID, OrderID)
DECLARE @RunningTotal INT = 0, @GroupID INT = 0, @BasketID INT = 0;
UPDATE t WITH(TABLOCKX)
SET @RunningTotal = RunningTotalInGroup = items + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN @RunningTotal ELSE 0 END,
@BasketID = BasketID = @BasketID + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END,
@GroupID = groupID
FROM #TestTable AS t
OPTION (MAXDOP 1);
SELECT *
FROM #TestTable AS tt
GO
DROP TABLE #TestTable
February 6, 2018 at 12:05 pm
ZZartin - Tuesday, February 6, 2018 10:38 AMWell 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...
February 6, 2018 at 12:08 pm
Luis Cazares - Tuesday, February 6, 2018 11:27 AMI actually used the Quirky Update which is great for this sort of things. I would test it as I'm not sure about updating 2 columns at once. Check the full explanation (with tests and alternatives) in here: Solving the Running Total and Ordinal Rank Problems (Rewritten) - SQLServerCentral
SELECT IDENTITY(INT,1,1) AS RowID,
v.OrderID,
v.items,
v.groupID,
0 AS RunningTotalInGroup,
0 AS BasketID
INTO #TestTable
FROM (VALUES
('Order1',4,3)
,('Order2',3,3)
,('Order3',4,3)
,('Order4',5,7)
,('Order5',6,8)
,('Order6',4,8)
,('Order7',5,8)
)v(OrderID,items,groupID)
ORDER BY v.groupID, v.OrderID
;CREATE CLUSTERED INDEX ci_testTable ON #TestTable (groupID, OrderID)
DECLARE @RunningTotal INT = 0, @GroupID INT = 0, @BasketID INT = 0;
UPDATE t WITH(TABLOCKX)
SET @RunningTotal = RunningTotalInGroup = items + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN @RunningTotal ELSE 0 END,
@BasketID = BasketID = @BasketID + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END,
@GroupID = groupID
FROM #TestTable AS t
OPTION (MAXDOP 1);SELECT *
FROM #TestTable AS ttGO
DROP TABLE #TestTable
Not heard of quirky update. I will try this too. I have posted a reply to another reply above, that is the next step I would like to look into .will quirky update support that?
February 6, 2018 at 12:41 pm
select
OrderID,
Items,
GroupID,
RunningTotalInGroup,
SUM(IncrBasket) OVER(ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as BasketID
from (
select
OrderID,
Items,
GroupID,
case lag(groupid) over (order by groupid)
when GroupID then
case
when SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) >= 10 then 1
else 0
end
else 1
end as IncrBasket,
SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RunningTotalInGroup
from #TestTable
) Rslt
February 16, 2018 at 3:16 am
Luis Cazares - Tuesday, February 6, 2018 11:27 AMI actually used the Quirky Update which is great for this sort of things. I would test it as I'm not sure about updating 2 columns at once. Check the full explanation (with tests and alternatives) in here: Solving the Running Total and Ordinal Rank Problems (Rewritten) - SQLServerCentral
SELECT IDENTITY(INT,1,1) AS RowID,
v.OrderID,
v.items,
v.groupID,
0 AS RunningTotalInGroup,
0 AS BasketID
INTO #TestTable
FROM (VALUES
('Order1',4,3)
,('Order2',3,3)
,('Order3',4,3)
,('Order4',5,7)
,('Order5',6,8)
,('Order6',4,8)
,('Order7',5,8)
)v(OrderID,items,groupID)
ORDER BY v.groupID, v.OrderID
;CREATE CLUSTERED INDEX ci_testTable ON #TestTable (groupID, OrderID)
DECLARE @RunningTotal INT = 0, @GroupID INT = 0, @BasketID INT = 0;
UPDATE t WITH(TABLOCKX)
SET @RunningTotal = RunningTotalInGroup = items + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN @RunningTotal ELSE 0 END,
@BasketID = BasketID = @BasketID + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END,
@GroupID = groupID
FROM #TestTable AS t
OPTION (MAXDOP 1);SELECT *
FROM #TestTable AS ttGO
DROP TABLE #TestTable
How would you restart the basketID after each group?
February 16, 2018 at 5:09 am
Talvin Singh - Friday, February 16, 2018 3:16 AMLuis Cazares - Tuesday, February 6, 2018 11:27 AMI actually used the Quirky Update which is great for this sort of things. I would test it as I'm not sure about updating 2 columns at once. Check the full explanation (with tests and alternatives) in here: Solving the Running Total and Ordinal Rank Problems (Rewritten) - SQLServerCentral
SELECT IDENTITY(INT,1,1) AS RowID,
v.OrderID,
v.items,
v.groupID,
0 AS RunningTotalInGroup,
0 AS BasketID
INTO #TestTable
FROM (VALUES
('Order1',4,3)
,('Order2',3,3)
,('Order3',4,3)
,('Order4',5,7)
,('Order5',6,8)
,('Order6',4,8)
,('Order7',5,8)
)v(OrderID,items,groupID)
ORDER BY v.groupID, v.OrderID
;CREATE CLUSTERED INDEX ci_testTable ON #TestTable (groupID, OrderID)
DECLARE @RunningTotal INT = 0, @GroupID INT = 0, @BasketID INT = 0;
UPDATE t WITH(TABLOCKX)
SET @RunningTotal = RunningTotalInGroup = items + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN @RunningTotal ELSE 0 END,
@BasketID = BasketID = @BasketID + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END,
@GroupID = groupID
FROM #TestTable AS t
OPTION (MAXDOP 1);SELECT *
FROM #TestTable AS ttGO
DROP TABLE #TestTableHow would you restart the basketID after each group?
I added an outer case statement which achieves the result!
from:
@BasketID = BasketID = @BasketID + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END,
to:
@BasketID = BasketID = CASE WHEN groupID = @GroupID THEN (@BasketID + CASE WHEN item + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END) ELSE 1 END,
Many thanks
February 16, 2018 at 7:11 am
You can make use of recursive cte to get the output. The expected results as mentioned by you is slightly incorrect.
with data
as (select row_number() over(order by orderid) as rnk
,*
from #testtable
)
,cte(orderid1,items1,groupid1,prev_groupid1,runningtotal1,basketid1,rnk1)
as (select a.orderid,a.items,a.groupid,a.groupid as prev_groupid,a.items as runningtotal,1 as basketid,a.rnk
from data a
where a.rnk=1
union all
select a.orderid,a.items,a.groupid
,a.groupid as prev_groupid
,case when b.runningtotal1+a.items<=10 and a.groupid=b.prev_groupid1 then
b.runningtotal1+a.items
else a.items
end as runningtotal
,case when b.runningtotal1+a.items<=10 and a.groupid=b.prev_groupid1 then
b.basketid1
else b.basketid1+1
end as baskedid
,a.rnk
from data a
join cte b
on a.rnk=b.rnk1+1
)
select orderid1,items1,groupid1,basketid1
from cte
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply