carry forward balance as opening/calculate balance in ledger

Question

Hi!

I have two tables 1-ledgertable (which contains all the purchasing and issuing details of an inventory) 2- openingstock (contains opening quantity of each item of current session).

I want to create a ledger report using both above given table but I am facing problem to carry forward the balance into opening. I have also read the solution on https://forums.asp.net/t/2070270.aspx?carry+forward+closing+balance+for+next+day+opening+in+sql but if i am using this to my tables it gives me right result in first two rows but it is not giving me correct result in 3 row onwards.

Ledgertable contains fields:

trandate, voucherno, itemno, ITEMNAME, recieve, issue, returnback, to_dept, remarks
2016-06-20 20160000000259 1 Ac Box 1.00 0.00 0.00 NULL

openingstock contains fields:

itemno itemname opening, unit department
219 Street Light 20 Watt 0.00 NOS QMSTORE

Here is my code and result:

;With CTE1 AS
(SELECT p.trandate,p.voucherno,p.itemno,p.itemname,SUM(isnull(O.opening,0)) opening,SUM(isnull(p.recieve,0)) Recieve,
SUM(isnull(p.issue,0)) Issue,SUM(isnull(p.returnback,0)) Returnback,p.to_dept,p.remarks,
ROW_NUMBER() OVER (Partition BY p.itemno Order by p.itemno,p.trandate) Row_Num FROM ledgertable p LEFT JOIN
openingstock O ON O.itemno = p.itemno GROUP BY p.itemno,p.ITEMNAME,p.trandate,p.voucherno,p.to_dept,p.remarks
),

CTE2 AS
( SELECT N.trandate,N.voucherno,N.itemno,N.itemname,N.recieve,N.issue,N.returnback,N.to_dept,o.opening,N.remarks,C.balance FROM CTE1 N
LEFT JOIN
CTE1 P ON P.itemno = N.itemno AND N.Row_Num = P.Row_Num + 1
CROSS APPLY
(
SELECT COALESCE(P.opening+(ISNULL(P.Recieve,0)+isnull(P.returnback,0)) - ISNULL(P.Issue,0),N.opening) opening
) O
CROSS APPLY
(
SELECT O.opening + (ISNULL(N.Recieve,0)+isnull(N.Returnback,0)) - ISNULL(N.Issue,0) balance)
C)
select * from CTE2

Output:

trandate	voucherno	itemno	itemname	recieve	issue	returnback	to_dept	opening	remarks	balance
6/20/2016	20160000000259	1	Ac Box	1	0	0	NULL	2		3
6/22/2016	20160000000235	1	Ac Box	6	0	0	NULL	3		9
7/20/2016	20160000000333	1	Ac Box	5	0	0	NULL	8		13
9/15/2017	2017000000461	1	Ac Box	1	0	0	NULL	7		8
9/16/2017	2017916005	1	Ac Box	0	1	0	III DORM	3	for Commen Room, Collected by Kishan	2
10/6/2017	2017000000526	1	Ac Box	1	0	0	NULL	1		2
10/7/2017	2017107003	1	Ac Box	0	1	0	III DORM	3	Collected by Sanjay	2
1/12/2018	20180000000044	1	Ac Box	1	0	0	NULL	1		2