We help IT Professionals succeed at work.

Running total between 2 sql tables in Sql

136 Views
1 Endorsement
Last Modified: 2017-12-24
The first table looks like this


StockCode      Warehouse      AvailQty                     SalesQty                   Remaining
0422 2001      BR-DROP      5200000.000000      1981299.230000      3218700.770000



The 2nd table looks like this
Ser3      StockCode      Ser                                  Warehouse   TrnType      Bin               Qty                               CusInvoice      VendGrn      Job
201508      0422 2001      2015080313165079      BR-DROP      A            BR-DROP       100000.000000                   
201509      0422 2001      2015091413551294      BR-DROP      A            BR-DROP       100000.000000                   
201511      0422 2001      2015111216063103      BR-DROP      A            BR-DROP       2000000.000000                   
201602      0422 2001      2016021214424950      BR-DROP      A            BR-DROP       3000000.000000            

What I need in table2 is to show where the 3218700.77000 will be subtracted....The first and second record will have 0 qty..but the 3rd record will have 218700.770000 and the 4th record will show 3000000.00000...

Hopefully this makes sense
Comment
Watch Question

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
I come out with this but i guess there are better solutions out there (yea, can't think for a better one)

Declare @Table1 table
(
	StockCode varchar(10),
	Warehouse varchar(10),
	AvailQty decimal(20,6),
	SalesQty decimal(20,6),
	Remaining decimal(20,6)
)
insert into @Table1
values
('0422 2001', 'BR-DROP', 5200000.000000, 1981299.230000, 3218700.770000);

Declare @Table2 table
(
	Ser3 varchar(10),
	StockCode varchar(10),
	Ser varchar(30),
	Warehouse varchar(10),
	TrnType varchar(1),
	Bin varchar(10),
	Qty decimal(20,6),
	CusInvoice varchar(10)
)

insert into @Table2
values
('201508', '0422 2001', '2015080313165079', 'BR-DROP', 'A', 'BR-DROP', 100000.000000, NULL),                   
('201509', '0422 2001', '2015091413551294', 'BR-DROP', 'A', 'BR-DROP', 100000.000000, NULL),  
('201511', '0422 2001', '2015111216063103', 'BR-DROP', 'A', 'BR-DROP', 2000000.000000, NULL),                  
('201602', '0422 2001', '2016021214424950', 'BR-DROP', 'A', 'BR-DROP', 3000000.000000, NULL);

;with cte
as
(
   select *, row_number() Over (Order By Ser3) idx
   from @Table2
)
, cte2
as
(
	select a.*, b.AvailQty, b.SalesQty, b.Remaining, (select sum(qty) total from cte a2 where a2.idx <= a.idx) CumulativeTotal
	from cte a inner join @Table1 b
	on a.StockCode = b.StockCode
)
select Ser3, StockCode, Ser, Warehouse, TrnType, Bin, Qty, CusInvoice,
case when CumulativeTotal-SalesQty = Remaining then
	Qty
else
	case when SalesQty >= CumulativeTotal then
		0
	else
		CumulativeTotal- SalesQty 
	end
end	CalculatedTotal
from cte2

Open in new window

Author

Commented:
getting a sql error
Msg 8120, Level 16, State 1, Line 1
Column 'cte.Ser3' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Author

Commented:
Got past this error..

But i have this now.. After the edit to the script I made you get another error (added grouping)


select a.*, b.AvailQty, b.SalesQty, b.Remaining, (select sum(SalesQty) total from cte a2 where a2.idx <= a.idx) CumulativeTotal
from cte a inner join cte2 b
      on a.StockCode = b.StockCode
      Group By a.Ser3, a.StockCode,a.Ser,a.Warehouse,a.TrnType,a.Bin,a.Qty,a.CusInvoice,a.VendGrn,a.Job,a.idx, b.AvailQty,
      b.SalesQty, b.Remaining


error: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Software Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
@Mikeyman_01

do you still need further assistance based on your original question?
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
I think this was a good question... but shall we close this question if there's no further issue?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.