SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Increament id if running total for group exceeds value


Increament id if running total for group exceeds value

Author
Message
Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 254
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)

ZZartin
ZZartin
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24277 Visits: 16445
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

Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)

Group: General Forum Members
Points: 158088 Visits: 22523
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



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 254
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...

Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 254
Luis Cazares - Tuesday, February 6, 2018 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

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?

Bert-701015
Bert-701015
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1152 Visits: 1210
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
Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 254
Luis Cazares - Tuesday, February 6, 2018 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

How would you restart the basketID after each group?

Talvin Singh
Talvin Singh
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 254
Talvin Singh - Friday, February 16, 2018 3:16 AM
Luis Cazares - Tuesday, February 6, 2018 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

How 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

george_at_sql
george_at_sql
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1155 Visits: 1717
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search