Get the max value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get the max value

Author

Topic

Ehtesham Siddiqui
Starting Member

10 Posts

Posted - 2011-11-05 : 01:45:22

Hi friends,
I have two tables FeeRecieptMaster,FeeChequeStatus
FeeRecieptMaster has the Balance Amount column and FeeChequeStatus has the Amt column


select BalanceAmt,(select Amt from FeeChequeStatus where ProcessStatus='Cleared' and RegNo=101106001)as ChequeAmt
,BalanceAmt- (select Amt from FeeChequeStatus where ProcessStatus='Cleared' and RegNo=101106001)
 from FeeRecieptMaster where VoucherNo=(select max(VoucherNo) from FeeRecieptMaster where RegNo=Any RegNo)
and RegNo=Any RegNo

Please have a look,
here my table structure is such that the Max(voucherNo) in the FeeRecieptMaster has the latest BalanceAmt for that particular RegNo.
Now im taking that latest BalanceAmt and Substracting it from the sum(Amt) where ProcessStatus='Cleared' this is probably giving me the correct output.
Now i want these results for all the regNo's and not only for the Provided regNo.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:09:10

are you using sql 2005 or above? if yes, use


SELECT fr.regNo,fr.BalanceAmt,fcs.TotalAmt AS CheckAmt,fr.BalanceAmt - fcs.TotalAmt 
FROM FeeRecieptMaster fr
INNER JOIN (SELECT RegNo,MAX(voucherNo) AS LastVoucher
            FROM FeeRecieptMaster 
            GROUP BY RegNo)fr1
ON fr1.RegNo = fr.RegNo
AND fr1.LastVoucher = fr.voucherNo
INNER JOIN (SELECT RegNo,SUM(Amt) AS TotalAmt
            FROM FeeChequeStatus 
            WHERE ProcessStatus='Cleared'
            GROUP BY RegNo) fcs
ON fcs.RegNo = fr.RegNo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources