BAQ running subtraction starting with parameter

I have a need for a BAQ to emulate BackFlush behavior. I will be using this to generate issuing for an integration that I am working on. This will be used for warehouse and bin selection on the from part of the issue. Now if I need to, I can loop through the rows and do this all in C# when doing the issueing, (or I can make a post processing BPM on GetList that does the loop and sets the values that way :thinking:) , however, I would prefer that the BAQ returns the values that need to be issued right in the grid using SQL. And I think it’s an interesting problem to solve anyways.

What I want is all of the bins with that part number to be in the list, ordered by the smallest amount to the largest amount. I have that part. I am passing in a parameter that will be the total amount that needs to get issued. (that shows up in the picture as 200, it’s not 200 each, it’s 200 total). What I would like to have in the grid is for the first row to have the lesser of the two values (issued or in the bin) to show up in a column. This will be the amount that gets issued from that bin. In the second row, I need the lesser of the remainder of what’s left to be issued or what’s in the bin. This will continue until either I run out of material in bins, or the full amount is issued.

This is something that relatively easy in excel because I can have a different formula in the first cell to kick things off. I’ve tried playing around with Lead() and Lag() windowing functions (as shown in the crossed out cells) but I can’t quite think of how I would get this to work in SQL.

Anyone want to take a crack at it? @knash?

Can’t you just create a calculated field and do (starting # - On Hand) OVER(PARTITION BY Bin)

That doesn’t make any sense, there’s no aggregate function there, so an over(partion by x) doesn’t work.

If I add a sum() in there, it will add the starting quantity-bin qty for each row…

This is the calculated field based on what you suggested.

sum(@QtyToIssue - Bins.PartBin_OnhandQty) over (partition by Bins.PartBin_PartNum order by Bins.PartBin_OnhandQty asc)

I also want to play around with the lag function, but the problem that I keep running into is that I can’t reference the same column in the lag, It has to be a different one. So if I do some math and then need that value to calculate the next row, I can’t reference it.

lag(Bins.PartBin_OnhandQty,1,0) over (partition by Bins.PartBin_PartNum order by Bins.PartBin_OnhandQty asc

Yeah, my fingers go faster than my mind sometimes, the SUM was implied in my mind. :rofl:

So, make the On Hand column negative and then do a SUM.

Yeah, but if that parameter is in there, it’s going to account for that in every row. That’s a total amount, not each row.

I figured it out.

I start with a binqty running sum

sum(Bins.PartBin_OnhandQty) over (partition by Bins.PartBin_PartNum order by Bins.PartBin_OnhandQty asc)

then I subtract that number from by total input

sum(Bins.PartBin_OnhandQty) over (partition by Bins.PartBin_PartNum order by Bins.PartBin_OnhandQty asc)

Then do a case statement to figure out how much should be issued from each bin.

case when RunningSumBack > 0 then Bins.PartBin_OnhandQty
  when RunningSumBack + Bins.PartBin_OnhandQty > 0 then RunningSumBack + Bins.PartBin_OnhandQty
  else 0

You were on the right track @jkane, I just wasn’t getting it in my head what you were saying.