CREATE Procedure sp_FeeReciptReport
(
@FranchiseCode nvarchar(10),
@Course nvarchar(50),
@Batch nvarchar(15)
)
as
begin
if @Course='' and @Batch=''
begin
select row_number() over (order by (SELECT (1))) AS SNo, SM.ADMNNO,SM.STDFIRSTNAME as "Student Name",
SM.PRESENT_CLASS,SM.PRESENT_SEC,
SM.ACTIVE,SM.CourseFee as "Bill Amt.",ISNULL(FR.CurrentPaid,0) as "Paid Amt",
ISNULL(CR.Amount,0) as "chq. Return",
ISNULL(FC.Amount,0) as "Concession",ISNULL(FRF.Amount,0) AS Refund
,courseFee-ISNULL(FR.CurrentPaid,0)+ISNULL(CR.Amount,0)-ISNULL(FC.Amount,0)-ISNULL(FRF.Amount,0) as Balance,SM.FormNumber
from STUDENTMASTER SM
left join
(select ADMNNO,ISNULL(sum(CurrentPaid),0) as CurrentPaid FROM FeeReceipt group by ADMNNO) FR
ON FR.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM CHEQUERETURN group by ADMNNO) CR
on CR.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM FeeConcession group by ADMNNO) FC
on FC.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM FeeRefund group by ADMNNO) FRF
on FRF.ADMNNO=SM.ADMNNO
WHERE SM.FranchiseCode=@FranchiseCode
end
else
begin
select row_number() over (order by (SELECT (1))) AS SNo, SM.ADMNNO,SM.STDFIRSTNAME as "Student Name",
SM.PRESENT_CLASS,SM.PRESENT_SEC,
SM.ACTIVE,SM.CourseFee as "Bill Amt.",ISNULL(FR.CurrentPaid,0) as "Paid Amt",
ISNULL(CR.Amount,0) as "chq. Return",
ISNULL(FC.Amount,0) as "Concession",ISNULL(FRF.Amount,0) AS Refund
,courseFee-ISNULL(FR.CurrentPaid,0)+ISNULL(CR.Amount,0)-ISNULL(FC.Amount,0)-ISNULL(FRF.Amount,0) as Balance,SM.FormNumber
from STUDENTMASTER SM
left join
(select ADMNNO,ISNULL(sum(CurrentPaid),0) as CurrentPaid FROM FeeReceipt group by ADMNNO) FR
ON FR.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM CHEQUERETURN group by ADMNNO) CR
on CR.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM FeeConcession group by ADMNNO) FC
on FC.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM FeeRefund group by ADMNNO) FRF
on FRF.ADMNNO=SM.ADMNNO
WHERE SM.FranchiseCode=@FranchiseCode and
SM.PRESENT_CLASS=@Course
AND SM.PRESENT_SEC=@Batch
end
end
(
@FranchiseCode nvarchar(10),
@Course nvarchar(50),
@Batch nvarchar(15)
)
as
begin
if @Course='' and @Batch=''
begin
select row_number() over (order by (SELECT (1))) AS SNo, SM.ADMNNO,SM.STDFIRSTNAME as "Student Name",
SM.PRESENT_CLASS,SM.PRESENT_SEC,
SM.ACTIVE,SM.CourseFee as "Bill Amt.",ISNULL(FR.CurrentPaid,0) as "Paid Amt",
ISNULL(CR.Amount,0) as "chq. Return",
ISNULL(FC.Amount,0) as "Concession",ISNULL(FRF.Amount,0) AS Refund
,courseFee-ISNULL(FR.CurrentPaid,0)+ISNULL(CR.Amount,0)-ISNULL(FC.Amount,0)-ISNULL(FRF.Amount,0) as Balance,SM.FormNumber
from STUDENTMASTER SM
left join
(select ADMNNO,ISNULL(sum(CurrentPaid),0) as CurrentPaid FROM FeeReceipt group by ADMNNO) FR
ON FR.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM CHEQUERETURN group by ADMNNO) CR
on CR.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM FeeConcession group by ADMNNO) FC
on FC.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM FeeRefund group by ADMNNO) FRF
on FRF.ADMNNO=SM.ADMNNO
WHERE SM.FranchiseCode=@FranchiseCode
end
else
begin
select row_number() over (order by (SELECT (1))) AS SNo, SM.ADMNNO,SM.STDFIRSTNAME as "Student Name",
SM.PRESENT_CLASS,SM.PRESENT_SEC,
SM.ACTIVE,SM.CourseFee as "Bill Amt.",ISNULL(FR.CurrentPaid,0) as "Paid Amt",
ISNULL(CR.Amount,0) as "chq. Return",
ISNULL(FC.Amount,0) as "Concession",ISNULL(FRF.Amount,0) AS Refund
,courseFee-ISNULL(FR.CurrentPaid,0)+ISNULL(CR.Amount,0)-ISNULL(FC.Amount,0)-ISNULL(FRF.Amount,0) as Balance,SM.FormNumber
from STUDENTMASTER SM
left join
(select ADMNNO,ISNULL(sum(CurrentPaid),0) as CurrentPaid FROM FeeReceipt group by ADMNNO) FR
ON FR.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM CHEQUERETURN group by ADMNNO) CR
on CR.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM FeeConcession group by ADMNNO) FC
on FC.ADMNNO=SM.ADMNNO
left join
(select ADMNNO,ISNULL(sum(Amount),0) as "Amount" FROM FeeRefund group by ADMNNO) FRF
on FRF.ADMNNO=SM.ADMNNO
WHERE SM.FranchiseCode=@FranchiseCode and
SM.PRESENT_CLASS=@Course
AND SM.PRESENT_SEC=@Batch
end
end
No comments:
Post a Comment