SQL query to SUM previous and current row in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

tblFeesCollection

AdmissionNo

Recievable

Arrears

NetBal

Paid

RemBal

FeesMonth

R-001

700

0

700

500

200

Aug

R-001

0

200

200

100

100

Aug

R-001

500

0

500

0

0

Sep

Required

AdmissionNo

NetBal  (Previous Month Balance (Aug) + New Month generated Fees(Sep))

Paid

RemBal

R-001

100 + 500

0

600

select fg.AdmissionNo,SName,FName,FPhone,NetBal,Paid
			
			,ISNULL((SELECT sum(RemBal) FROM tblFeesCollection where AdmissionNo='R-000002'),0) 'RemBal'
            ,(NetBal + ISNULL((SELECT sum(RemBal) FROM tblFeesCollection where AdmissionNo='R-000002'),0)) 'Total Fees'
            from tblFeesGenerate as fg inner join tblFeesCollection as fc on fg.AdmissionNo=fc.AdmissionNo 
            inner join tblStdReg as sr on fg.AdmissionNo=sr.AdmissionNo
			where fc.FeesMonth='9-Sep' 
            and fg.AdmissionNo='R-000002' 
            group by fg.AdmissionNo,SName,FName,FPhone,FeesMonth,NetBal,Paid,RemBal order by fg.AdmissionNo

this showing wrong sum

AdmissionNo

NetBal  (Previous Month Balance (Aug) + New Month generated Fees(Sep))

Paid

RemBal

R-001

300 + 500

0

800

Posted 3 months ago

Hi smile,

Refer below test query.

SQL

DECLARE @Test AS TABLE(AdmissionNo CHAR(5),Recievable INT,Arrears INT,NetBal INT,Paid INT,RemBal INT,FeesMonth CHAR(3))

INSERT INTO @Test VALUES('R-001',700,0,700,500,200,'Aug')
INSERT INTO @Test VALUES('R-001',0,200,200,100,100,'Aug')
INSERT INTO @Test VALUES('R-001',500,0,500,0,0,'Sep')

SELECT DISTINCT AdmissionNo,
(SELECT TOP 1 CAST(RemBal AS VARCHAR(20)) FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Aug' ORDER BY t.RowNum DESC) 
+  ' + '  + (SELECT TOP 1 CAST(NetBal AS VARCHAR(20)) FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Sep' ORDER BY t.RowNum ASC) 'NetBal',
(SELECT TOP 1 Paid FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Sep' ORDER BY t.RowNum DESC) 'Paid',
(SELECT TOP 1 RemBal FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Aug' ORDER BY t.RowNum DESC) 
+ (SELECT TOP 1 NetBal FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Sep' ORDER BY t.RowNum ASC) 'RemBal'
FROM @Test

Output

AdmissionNo NetBal Paid RemBal
R-001 100 + 500 0 600