Friday, 26 July 2013

Left Join with Multiple Tables in Sqlserver

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

No comments:

Post a Comment