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