Running Sum Calculation

[swiny]

Hi I have the 2 tables below:

TableA

ReceiptNo ItemNumber Quantity

111 A 100
222 A 200
333 A 300
333 B 400
444 A 250
555 B 300
666 A 100
666 B 350
777 A 450

TableB

ItemNumber QuantityTotal
A 900
B 850

I need to list everything in TableA to get the total of A, that comprises of the total in TableB (same with itemB)

Meaning
I need to list
777 A 450
666 A 100
444 A 250
333 A 100 (not 300)

666 B 450
555 B 300
333 B 100

What would be the easiest way to inquire on it?
To creating a running sum?
Your feedback is appreciated.
Thanks.

 

[lbass]

This isn't clear. Can you explain why you are looking for these amounts? I'm not sure your example is accurate.

-LBa

 

[swiny]

Well, I need to backtrack an inventory on hand, meaning.. I need to start from the latest receipt number I had..
So, I need get the total of 900 on hand quantity, but, had to start from the latest receipt..
Example for A: I had to go to 777, and pick up 450, since 450 is still less than 900,I had to go to 666 and pick up 100, my total is still 550, and then, I go to 444,pick up 250 the total is only 800, until I get to 333, since 333 has 300, I only need to get the 100 to get my total of 900.
Hope this makes sense.
I just realized my B example is wrong.
I need to list

666 B 350
555 B 300
333 B 200

 

[lbass]

Link the tables on the itemnumber and then group on itemnumber and sort your report in descending order by receipt number. Then create a formula like this to use for the line item amount display:

//{@detailformula}:
whileprintingrecords;
numbervar amt;
numbervar curramt;
if amt + {tableA.quantity} < {tableB.qtytotal} then (
curramt := {tableA.quantity};
amt := amt + {tableA.quantity}
);
if amt + {tableA.quantity} >= {tableB.qtytotal} then (
curramt := {tableB.qtytotal} - amt;
amt := {tableB.qtytotal}
);
curramt

Then suppress details in the section expert using:

whileprintingrecords;
numbervar amt;
amt + {tableA.quantity} > {tableB.qtytotal};

Use a reset formula in the item no group header:
whileprintingrecords;
numbervar amt;
numbervar curramt;
if not inrepeatedgroupheader then (
amt := 0;
curramt := 0
);

-LB

 

[swiny]

Thanks for your reply..
If I want to do this in t-sql, can you create an easier script?
Thanks...

 

[lbass]

I don't know what you mean, so no, sorry.

-LB