A
Abhinav Tripathi9
Guest
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 tried below given query to get the result but it is not giving me right result in each row for a particular item.
With CTE1 AS
(SELECT p.trandate,p.voucherno,p.itemno,p.itemname,SUM(isnull(
pening,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
(
SELECTpening + (ISNULL(N.Recieve,0)+isnull(N.Returnback,0)) - ISNULL(N.Issue,0) balance)
C)
select * from CTE2
table details are given below:
1-ledger table
trandate
voucherno
itemno
ITEMNAME
recieve
issue
returnback
to_dept
remarks
1/12/2018
20180000000044
1
Ac Box
1
NULL
NULL
NULL
9/16/2017
2017916005
1
Ac Box
NULL
1
NULL
III DORM
for Commen Room, Collected by Kishan
10/7/2017
2017107003
1
Ac Box
NULL
1
NULL
III DORM
Collected by Sanjay
6/22/2016
20160000000235
1
Ac Box
6
NULL
NULL
NULL
6/20/2016
20160000000259
1
Ac Box
1
NULL
NULL
NULL
7/20/2016
20160000000333
1
Ac Box
5
NULL
NULL
NULL
9/15/2017
2017000000461
1
Ac Box
1
NULL
NULL
NULL
10/6/2017
2017000000526
1
Ac Box
1
NULL
NULL
NULL
10/25/2016
20160000000666
3
Adopter
100
NULL
NULL
NULL
7/20/2016
20160000000333
3
Adopter
1
NULL
NULL
NULL
7/20/2016
20160000000333
3
Adopter
2
NULL
NULL
NULL
5/8/2017
201758002
3
Adopter
NULL
1
NULL
ESTATE MAZAAR GROUND
Collected by Sonu Kumar
6/2/2017
201762022
3
Adopter
NULL
14
NULL
ESTATE
Collected by Deepak
9/26/2017
2017926001
3
Adopter
NULL
9
NULL
ESTATE
for LED Repair, Collected by Vijay
9/26/2017
2017926001
3
Adopter
NULL
7
NULL
ESTATE
for LED Repair, Collected by Vijay
8/12/2016
20160000000453
3
Adopter
55
NULL
NULL
NULL
Electric Lari Adopter
4/11/2016
20160000000214
12
Bell Switch
20
NULL
NULL
NULL
8/29/2017
2017829005
12
Bell Switch
NULL
1
NULL
ESTATE ELECTRICIAN
Collected by Vijay
8/22/2017
2017822023
12
Bell Switch
NULL
1
NULL
Mr. Varden Regidency
Collected by Suneel 2
10/3/2017
2017103004
12
Bell Switch
NULL
1
NULL
ARA MACHINE
Collected by Suneel
7/27/2017
2017727009
12
Bell Switch
NULL
1
NULL
II DORM
Collected by Suneel 2
8/9/2017
201789010
12
Bell Switch
NULL
2
NULL
COLLEGE OFFICE
Collected by Vijay
8/25/2017
2017825013
12
Bell Switch
NULL
1
NULL
7 No. Jal Nigam
Collected by Deepak
8/25/2017
2017825014
12
Bell Switch
NULL
1
NULL
IV DORM
Collected by Sanjay
12/21/2017
20171221012
12
Bell Switch
NULL
1
NULL
VICE PRINCIPAL REGIDENCY
Collected by Deepak
6/14/2017
2017614015
12
Bell Switch
NULL
1
NULL
PRINCIPAL REGIDENCY
Collected by Vijay
7/29/2016
20160000000412
12
Bell Switch
2
NULL
NULL
NULL
2/2/2018
201822028
12
Bell Switch
NULL
1
NULL
HOSPITAL
Collected by Vijay
8/8/2017
2017000000411
12
Bell Switch
2
NULL
NULL
NULL
2- openingstock
itemno
itemname
opening
unit
department
1
Ac Box
2
NOS
QMSTORE
3
Adopter
0
NOS
QMSTORE
12
Bell Switch
25
NOS
QMSTORE
and i want to generate a report like given below:
trandate
voucherno
itemno
itemname
opening
recieve
issue
returnback
balance
to_dept
remarks
6/20/2016
20160000000259
1
Ac Box
2
1
0
0
3
NULL
6/22/2016
20160000000235
1
Ac Box
3
6
0
0
9
NULL
7/20/2016
20160000000333
1
Ac Box
9
5
0
0
14
NULL
9/15/2017
2017000000461
1
Ac Box
14
1
0
0
15
NULL
9/16/2017
2017916005
1
Ac Box
15
0
1
0
14
III DORM
for Commen Room, Collected by Kishan
10/6/2017
2017000000526
1
Ac Box
14
1
0
0
15
NULL
10/7/2017
2017107003
1
Ac Box
15
0
1
0
14
III DORM
Collected by Sanjay
1/12/2018
20180000000044
1
Ac Box
14
1
0
0
15
NULL
7/20/2016
20160000000333
3
Adopter
0
3
0
0
3
NULL
8/12/2016
20160000000453
3
Adopter
3
55
0
0
58
NULL
Electric Lari Adopter
10/25/2016
20160000000666
3
Adopter
58
100
0
0
158
NULL
5/8/2017
201758002
3
Adopter
158
0
1
0
157
ESTATE MAZAAR GROUND
Collected by Sonu Kumar
6/2/2017
201762022
3
Adopter
157
0
14
0
143
ESTATE
Collected by Deepak
9/26/2017
2017926001
3
Adopter
143
0
16
0
159
ESTATE
for LED Repair, Collected by Vijay
4/11/2016
20160000000214
12
Bell Switch
25
20
0
0
45
NULL
7/29/2016
20160000000412
12
Bell Switch
45
2
0
0
47
NULL
6/14/2017
2017614015
12
Bell Switch
47
0
1
0
46
PRINCIPAL REGIDENCY
Collected by Vijay
7/27/2017
2017727009
12
Bell Switch
46
0
1
0
45
II DORM
Collected by Suneel 2
8/8/2017
2017000000411
12
Bell Switch
45
2
0
0
47
NULL
8/9/2017
201789010
12
Bell Switch
47
0
2
0
45
COLLEGE OFFICE
Collected by Vijay
8/22/2017
2017822023
12
Bell Switch
45
0
1
0
44
Mr. Varden Regidency
Collected by Suneel 2
8/25/2017
2017825013
12
Bell Switch
44
0
1
0
43
7 No. Jal Nigam
Collected by Deepak
8/25/2017
2017825014
12
Bell Switch
43
0
1
0
42
IV DORM
Collected by Sanjay
8/29/2017
2017829005
12
Bell Switch
42
0
1
0
41
ESTATE ELECTRICIAN
Collected by Vijay
10/3/2017
2017103004
12
Bell Switch
41
0
1
0
40
ARA MACHINE
Collected by Suneel
12/21/2017
20171221012
12
Bell Switch
40
0
1
0
39
VICE PRINCIPAL REGIDENCY
Collected by Deepak
2/2/2018
201822028
12
Bell Switch
39
0
1
0
38
HOSPITAL
Collected by Vijay
Please help me to generate this kind of report.
Continue reading...
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 tried below given query to get the result but it is not giving me right result in each row for a particular item.
With CTE1 AS
(SELECT p.trandate,p.voucherno,p.itemno,p.itemname,SUM(isnull(
pening,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
pening + (ISNULL(N.Recieve,0)+isnull(N.Returnback,0)) - ISNULL(N.Issue,0) balance)C)
select * from CTE2
table details are given below:
1-ledger table
trandate
voucherno
itemno
ITEMNAME
recieve
issue
returnback
to_dept
remarks
1/12/2018
20180000000044
1
Ac Box
1
NULL
NULL
NULL
9/16/2017
2017916005
1
Ac Box
NULL
1
NULL
III DORM
for Commen Room, Collected by Kishan
10/7/2017
2017107003
1
Ac Box
NULL
1
NULL
III DORM
Collected by Sanjay
6/22/2016
20160000000235
1
Ac Box
6
NULL
NULL
NULL
6/20/2016
20160000000259
1
Ac Box
1
NULL
NULL
NULL
7/20/2016
20160000000333
1
Ac Box
5
NULL
NULL
NULL
9/15/2017
2017000000461
1
Ac Box
1
NULL
NULL
NULL
10/6/2017
2017000000526
1
Ac Box
1
NULL
NULL
NULL
10/25/2016
20160000000666
3
Adopter
100
NULL
NULL
NULL
7/20/2016
20160000000333
3
Adopter
1
NULL
NULL
NULL
7/20/2016
20160000000333
3
Adopter
2
NULL
NULL
NULL
5/8/2017
201758002
3
Adopter
NULL
1
NULL
ESTATE MAZAAR GROUND
Collected by Sonu Kumar
6/2/2017
201762022
3
Adopter
NULL
14
NULL
ESTATE
Collected by Deepak
9/26/2017
2017926001
3
Adopter
NULL
9
NULL
ESTATE
for LED Repair, Collected by Vijay
9/26/2017
2017926001
3
Adopter
NULL
7
NULL
ESTATE
for LED Repair, Collected by Vijay
8/12/2016
20160000000453
3
Adopter
55
NULL
NULL
NULL
Electric Lari Adopter
4/11/2016
20160000000214
12
Bell Switch
20
NULL
NULL
NULL
8/29/2017
2017829005
12
Bell Switch
NULL
1
NULL
ESTATE ELECTRICIAN
Collected by Vijay
8/22/2017
2017822023
12
Bell Switch
NULL
1
NULL
Mr. Varden Regidency
Collected by Suneel 2
10/3/2017
2017103004
12
Bell Switch
NULL
1
NULL
ARA MACHINE
Collected by Suneel
7/27/2017
2017727009
12
Bell Switch
NULL
1
NULL
II DORM
Collected by Suneel 2
8/9/2017
201789010
12
Bell Switch
NULL
2
NULL
COLLEGE OFFICE
Collected by Vijay
8/25/2017
2017825013
12
Bell Switch
NULL
1
NULL
7 No. Jal Nigam
Collected by Deepak
8/25/2017
2017825014
12
Bell Switch
NULL
1
NULL
IV DORM
Collected by Sanjay
12/21/2017
20171221012
12
Bell Switch
NULL
1
NULL
VICE PRINCIPAL REGIDENCY
Collected by Deepak
6/14/2017
2017614015
12
Bell Switch
NULL
1
NULL
PRINCIPAL REGIDENCY
Collected by Vijay
7/29/2016
20160000000412
12
Bell Switch
2
NULL
NULL
NULL
2/2/2018
201822028
12
Bell Switch
NULL
1
NULL
HOSPITAL
Collected by Vijay
8/8/2017
2017000000411
12
Bell Switch
2
NULL
NULL
NULL
2- openingstock
itemno
itemname
opening
unit
department
1
Ac Box
2
NOS
QMSTORE
3
Adopter
0
NOS
QMSTORE
12
Bell Switch
25
NOS
QMSTORE
and i want to generate a report like given below:
trandate
voucherno
itemno
itemname
opening
recieve
issue
returnback
balance
to_dept
remarks
6/20/2016
20160000000259
1
Ac Box
2
1
0
0
3
NULL
6/22/2016
20160000000235
1
Ac Box
3
6
0
0
9
NULL
7/20/2016
20160000000333
1
Ac Box
9
5
0
0
14
NULL
9/15/2017
2017000000461
1
Ac Box
14
1
0
0
15
NULL
9/16/2017
2017916005
1
Ac Box
15
0
1
0
14
III DORM
for Commen Room, Collected by Kishan
10/6/2017
2017000000526
1
Ac Box
14
1
0
0
15
NULL
10/7/2017
2017107003
1
Ac Box
15
0
1
0
14
III DORM
Collected by Sanjay
1/12/2018
20180000000044
1
Ac Box
14
1
0
0
15
NULL
7/20/2016
20160000000333
3
Adopter
0
3
0
0
3
NULL
8/12/2016
20160000000453
3
Adopter
3
55
0
0
58
NULL
Electric Lari Adopter
10/25/2016
20160000000666
3
Adopter
58
100
0
0
158
NULL
5/8/2017
201758002
3
Adopter
158
0
1
0
157
ESTATE MAZAAR GROUND
Collected by Sonu Kumar
6/2/2017
201762022
3
Adopter
157
0
14
0
143
ESTATE
Collected by Deepak
9/26/2017
2017926001
3
Adopter
143
0
16
0
159
ESTATE
for LED Repair, Collected by Vijay
4/11/2016
20160000000214
12
Bell Switch
25
20
0
0
45
NULL
7/29/2016
20160000000412
12
Bell Switch
45
2
0
0
47
NULL
6/14/2017
2017614015
12
Bell Switch
47
0
1
0
46
PRINCIPAL REGIDENCY
Collected by Vijay
7/27/2017
2017727009
12
Bell Switch
46
0
1
0
45
II DORM
Collected by Suneel 2
8/8/2017
2017000000411
12
Bell Switch
45
2
0
0
47
NULL
8/9/2017
201789010
12
Bell Switch
47
0
2
0
45
COLLEGE OFFICE
Collected by Vijay
8/22/2017
2017822023
12
Bell Switch
45
0
1
0
44
Mr. Varden Regidency
Collected by Suneel 2
8/25/2017
2017825013
12
Bell Switch
44
0
1
0
43
7 No. Jal Nigam
Collected by Deepak
8/25/2017
2017825014
12
Bell Switch
43
0
1
0
42
IV DORM
Collected by Sanjay
8/29/2017
2017829005
12
Bell Switch
42
0
1
0
41
ESTATE ELECTRICIAN
Collected by Vijay
10/3/2017
2017103004
12
Bell Switch
41
0
1
0
40
ARA MACHINE
Collected by Suneel
12/21/2017
20171221012
12
Bell Switch
40
0
1
0
39
VICE PRINCIPAL REGIDENCY
Collected by Deepak
2/2/2018
201822028
12
Bell Switch
39
0
1
0
38
HOSPITAL
Collected by Vijay
Please help me to generate this kind of report.
Continue reading...