adding each row incremental

Question

adding each row incremental

Siten0308

SSCarpal Tunnel

Points: 4404
More actions
December 2, 2018 at 10:34 pm

#369214

Hello,
sadly i am at my witts end with this complicated problem, and i know you guys are smart than i am, 🙁 i have a ordering table like so:
CREATE TABLE #F ( part_id varchar(56) null ,part_Location varchar(56) null ,Project varchar(128) null ,[Order] int null ,OnHand int null ,RequiredQuantity int null )
insert into #F(part_id,part_Location,Project,[order],OnHand,RequiredQuantity) values('12-3','USA','FuzzySquarl',1,4,3)
insert into #F(part_id,part_Location,Project,[order],OnHand,RequiredQuantity) values('12-3','USA','FuzzySquarl',2,4,6)
insert into #F(part_id,part_Location,Project,[order],OnHand,RequiredQuantity) values('12-3','USA','FuzzySquarl',3,4,2)
insert into #F(part_id,part_Location,Project,[order],OnHand,RequiredQuantity) values('12-3','USA','FuzzySquarl',4,4,5)
insert into #F(part_id,part_Location,Project,[order],OnHand,RequiredQuantity) values('12-3','USA','FuzzySquarl',5,4,1)
so the problem is i am trying to decrement the onhand from the required qty, so it would be like:
onhandQty - requiredQuantity and if results is 0 or less, then make it 0, else onhand - requiredQuantity
of course again want it to decrement per row, per part number, project and location,of course doesnt matter how many orders there are, just trying to figure out how much onhand can meet the RequiredQuantity before it gets to 0.
also vise versa for the new row which would be called i guess allocated or required Overload whatever... but to show how much requiredQuantity is outstanding from the calculation:
so for the above example, it would be nice to show it like:
PART_ID | ORDER | ONHAND | RequiredQuantity | LEFT_OVER_ONHAND | RequiredQuantityTotal
12-3 | 1 | 4 | 3 | 1 | 0
12-3 | 2 | 1 | 6 | 0 | 5
12-3 | 3 | 0 | 2 | 0 | 7
12-3 | 4 | 0 | 5 | 0 | 12
etc.etc.
i tried using a CTE and doing a left join and making a new column called row_Number() over etc., however when trying to join it up, it shows null, and i join it to part_ID, part_Location, and project and i know without a doubt it should have dont it, and does, i see the rownumber -1 which is the previous row for the new order, however the calculation doesn't work and doesn't bring the number from row 1 to row 2 or row 2 to row 3 etc. etc.
any help would be great :*(

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

I don't have access to a 2016 box just now but I believe this can be done using Lead or Lag. I believe that Books Online has an example.

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

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 2

Hey Jeff,

thanks for the LAG function, it does seem to work for the first and second row, however when it gets 2 the 3rd and 4th etc., it doesn't continue to increment the totals from the previous row 🙁

but that LAG function is really cool though 🙂

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Try breaking it into pieces:
with runningtotals as ( select part_id, [order],onhand, RequiredQuantity, sum (requiredQuantity) over (partition by part_id order by [order] Rows unbounded preceding) runningqty from #f) select *, case when onhand>runningqty then onhand-runningqty else 0 end remainingOnHand, case when onhand<runningqty then runningqty-onhand else 0 end missingqty from runningtotals

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 4

select f.*, IIF(T.LEFT_OVR_ONHAND<0,0,T.LEFT_OVR_ONHAND) LEFT_OVR_ONHAND, IIF(T.LEFT_OVR_ONHAND>0,0,-T.LEFT_OVR_ONHAND) RequiredQuantityTotal from #f f cross apply(values (f.OnHand-f.RequiredQuantity)) T(LEFT_OVR_ONHAND)

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 5

Jeff Moden - Monday, December 3, 2018 6:06 AM
I don't have access to a 2016 box just now but I believe this can be done using Lead or Lag. I believe that Books Online has an example.

If you feel it is secure you can use online sql server 2017 or 2016 using https://dbfiddle.uk/

Saravanan

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

Ah... just can't do it... there are a few things wrong with that site including their statement of "you agree to license everything you submit by Creative Commons CC0".

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

autoexcrement SSCertifiable Points: 5951 More actions · Answer 7

Not sure this is 100% relevant to your issue or not, but you might also want to check out SUM...
https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-2017

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura