How to get the All the records from a Table while using the sum and other calculations

Question

How to get the All the records from a Table while using the sum and other calculations

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 1

You may have to clarify what you need, as I am not certain about your exepected end result. I assume you want the "RegNo", along with the four calculated columns.

The other important criteria, in order to determine the SQL, is how many rows per RegNo are present in each table. I created a sample for you to review to give you an idea of what can be done. There are other alternatives, but it all depends on what and how these tables are populated. If you perform an inner or outer join and both tables (or FeeReceiptMaster) have multiple rows per "RegNo", then the SUM values will be incorrect.

The sample below uses an OUTER APPLY to return all rows from your FeeReceiptMaster table and any matching rows from the FeeChequeStatus.

select frm.RegNo

, sum(frm.Amount)as Paid

, sum(frm.BalanceAmt)as Balance

, ISNULL(sum(ca.Amt),0)as Cheque

, sum(frm.BalanceAmt)-( sum (frm.Amount)+ sum(ISNULL(ca.Amt,0)) ) as Outstanding

from FeeRecieptMaster frm

outer apply (select SUM(fcs.Amt)as Amt from FeeChequeStatus fcs where fcs.RegNo=frm.RegNo and fcs.ProcessStatus='Cleared') ca

group by frm.RegNo

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

Please provide a sample of the expected result along with sample data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SameerKKhan SSC-Addicted Points: 435 More actions · Answer 3

Hi friends

thanks for the reply.

I have tried the query posted by John and giving me the expected result.

Im in the testing process of that,if required i would post my further issue on same Query,right now prior to testing process its looking great.

Thanks again for the help so quickly.:Wow:

SameerKKhan SSC-Addicted Points: 435 More actions · Answer 4

Hi frnds,

Im trying something like this for the same issue.

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.

My i want these results for all the regNo's and not only for the Provided regNo.

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 5

I would love to help, but do not quite understand what your expect from this new query. What is your desired output? Also, what are the table relationships between FeeChequeStatus and FeeReceiptMaster?

Check out the link for samples of how to post your questions a little better.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 6

After reviewing your SQL again, I can offer up a similar solution as what I previously provided. Without knowing much more, I cannot say this would provide the most optimal performance. But, it is my best guess based on your query.

This query sample is solely on the basis that you are trying to pull information for only 1 RegNo. The reason I used an "outer apply" instead of the subquery directly in the select clause was to perform the action once versus twice. This would make a bigger difference in large data sets.

select frm.BalanceAmt

, ISNULL(f.SumAmt, 0) as ChequeAmt

, frm.BalanceAmt - ISNULL(f.SumAmt,0)

from FeeRecieptMaster frm

OUTER APPLY (

select SumAmt=SUM(f.Amt)

from FeeChequeStatus f

where f.ProcessStatus='Cleared' and f.RegNo=frm.RegNo) fcs

where frm.VoucherNo=(select max(cfm.VoucherNo) from FeeRecieptMaster cfm where cfm.RegNo=frm.RegNo)

and frm.RegNo={Any RegNo}