Need to find max items within each set
-
In batches a certain action is done on each order# following specified sequence. Action is marked as +1 (in) and -1(out), within life of each batch there are situations when all orders are out. I need to know max number of items processed within each set, where the set is interval while some orders are in, please see below my input visually broken into sets.
I decided to get a running sum of item counts during the processing, so my SUM_CNT = 0 would signify and end of each set. Now I cannot figure out how to get max of counts within each set, any pointers will be appreciated.
In the remark below the code I show result and desired max count MAX_CNT for each set
declare @T table (
BATCH_ID int
, SEQ_NO int
, ORDER_ID int
, ITEM_CNT int
);
--5 sets,
insert into @T(BATCH_ID, SEQ_NO, ORDER_ID, ITEM_CNT)
values
(1,1,100,1)
,(1,2,100,-1)
,(1,3,101,1)
,(1,4,101,-1)
,(1,5,110,1)
,(1,6,111,1)
,(1,7,111,-1)
,(1,8,110,-1)
,(2,1,200,1)
,(2,2,201,1)
,(2,3,201,-1)
,(2,4,202,1)
,(2,5,200,-1)
,(2,6,202,-1)
,(2,7, 210,1)
,(2,8, 211,1)
,(2,9, 212,1)
,(2,10,212,-1)
,(2,11,211,-1)
,(2,12,212,-1)
;
with ITEMS_PER_BATCH as (
select
PRV.BATCH_ID[B#]
,PRV.SEQ_NO[S#]
,PRV.ORDER_ID[O#]
,sum(PRV.ITEM_CNT) over (partition by PRV.BATCH_ID order by PRV.BATCH_ID, PRV.SEQ_NO) [SUM_CNT]
from@T PRV
join@T NXT onNXT.BATCH_ID = PRV.BATCH_ID andNXT.SEQ_NO = PRV.SEQ_NO + 1
)
select *
from ITEMS_PER_BATCHThe result with desired MAX count
/*
B# S# O# SUM_CNT MAX_CNT
1 1 100 1 1
1 2 100 0 1
1 3 101 1 1
1 4 101 0 1
1 5 110 1 2
1 6 111 2 2
1 7 111 1 2
2 1 200 1 2
2 2 201 2 2
2 3 201 1 2
2 4 202 2 2
2 5 200 1 2
2 6 202 0 2
2 7 210 1 3
2 8 211 2 3
2 9 212 3 3
2 10 212 2 3
2 11 211 1 3
*/
sorry for ugly look of this I can't preserve tabs when I paste from SSMS
- This topic was modified 1 year, 4 months ago by migurus.
-
Deleted... I misread the problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The last group of data in your example data looks like this...
,(2,7, 210,1)
,(2,8, 211,1)
,(2,9, 212,1)
,(2,10,212,-1)
,(2,11,211,-1)
,(2,12,212,-1) --<---<<< LOOK HERE!
Shouldn't that last line have an Order_ID of 210 instead of 212?
If so, the following works just fine after making the change to the data above...
with cteSUM_CNT as
(--==== This creates the running total of INs/OUTs and saves it in the SUM_CNT column
select *
,SUM_CNT = sum(ITEM_CNT) over (partition by BATCH_ID order by BATCH_ID, SEQ_NO rows unbounded preceding)
from @t
)
,cteGrpMarker AS
(--==== This creates wherever the running total was 0 and "smears" it upward to mark the entire group.
-- The order of this GrpMarker isn't important. It's only important that each group must be distinctly marked.
select *
,GrpMarker = sum(iif(SUM_CNT=0,1,0)) over (order by BATCH_ID DESC, SEQ_NO DESC rows unbounded preceding)
from cteSUM_CNT
)--==== Now, calculate the max count for each group is trivial.
select B# = BATCH_ID
,S# = SEQ_NO
,O# = ORDER_ID
,SUM_CNT
,MAX_CNT = max(SUM_CNT) OVER (PARTITION BY GrpMarker)
,GrpMarker -- Comment this out if you don't want it. It's here for "proof" only.
from cteGrpMarker
order by BATCH_ID, SEQ_NO
;Here's the output. Comment out the GrpMarker column if you don't want to see it. It's there just for "proof". The Blue "cell" is where the original data was repaired as previously stated.
If it works for you, send money... I'm trying to retire. 😀
p.s. I followed your "casing" in my code but the "prettifier" in the code window changed all the SQL Code to upper case for display. If you "quote" this post and look at the code window, you'll see my original code where all the lower casing is done for you. Not that it matters to you but that's not my first choice for formatting. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. I followed your "casing" in my code but the "prettifier" in the code window changed all the SQL Code to upper case for display.
The SQL code is not all uppercase when viewed on my browser (Chrome):
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Thank you very much, it works
- Phil Parkin wrote:
The SQL code is not all uppercase when viewed on my browser (Chrome):
Interesting. Thanks for the feedback, Phil.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - migurus wrote:
Thank you very much, it works
Thank you for the feedback!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply