Procedure With Joining with Multiple Tables

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter procedure sp_FranchisewiseCollection_Report
(
@FromDate varchar(4000),
@ToDate varchar(4000)
)
as
begin
declare @sqlStr varchar(4000)
set @sqlStr=''

set @sqlstr='select FR.FranchiseCode,
FM.FranchiseName,
(select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''Cash''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103) and FranchiseCode=FR.FranchiseCode) Cash_Collection ,

(select SUM(CurrentPaid) from FeeReceipt where PaymentMode IN (''Cheque'',''CREDIT CARD'')
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103) and FranchiseCode=FR.FranchiseCode) Bank_Collection ,

(select SUM(CurrentPaid) from FeeReceipt where PaymentMode IN (''Cheque'',''CREDIT CARD'',''Cash'')
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103) and FranchiseCode=FR.FranchiseCode) Total_Collection ,

(select SUM(Fine) from FeeReceipt where
 ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103) and FranchiseCode=FR.FranchiseCode) Fine_Collected ,

 CR.Amount as Chq_Return

from FeeReceipt FR
left join

(select FranchiseCode,ISNULL(SUM(AMOUNT),0) as "Amount" FROM CHEQUERETURN WHERE vDATE between
 CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
group by FranchiseCode ) CR
on FR.FranchiseCode=CR.FranchiseCode

left join

(select Code,FranchiseName from FranchiseMaster) FM

on FR.FranchiseCode=FM.Code
group by FR.FranchiseCode,CR.Amount,FM.FranchiseName
order by FR.FranchiseCode'

exec(@sqlstr)
 
end