
--select * from StudentFeeDueList
Alter Procedure ProcFeeReport

@AcademicyearId uniqueIdentifier
,@Schoolid uniqueIdentifier

as 

exec [StudentFeeDetail] @AcademicyearId,@Schoolid,null,null 

delete from studentfeeduelist 
--Declare @temp table (FOrder int, studentname uniqueidentifier, feename varchar(50),FeeType Varchar(50), FeeIndex varchar(50), FeeAmount numeric(14,2)) 
insert into StudentFeeDueList(Oid, StudentName,FeeNamestr,FeeType,FeeIndex,FeeAmount)	
select NEWID(), StudentName,feename,FeeType,FeeIndex,FeeAmount
from

(select 1 as Forder, StudentName
,feename='1-'+ (select feename from feemaster where oid=a.feename)
,FeeType
,FeeIndex
,FeeAmount
from StudentFeeDetails a

Union All
select 2,studentname
,FeeName='2-Total Fee' 
, 0
,FeeIndex=null
, SUM(FeeAmount)
from StudentFeeDetails a
Group by StudentName

Union All
select 3,studentname
,FeeName='3-Exclusion' 
, 0
,FeeIndex=null
, SUM(ExclusionAmount)
from StudentFeeDetails a
Group by StudentName

Union All
select 4,studentname
,FeeName='4-Concession' 
, 0
,FeeIndex=null
, SUM(ConcessionAmt )
from StudentFeeDetails a
Group by StudentName

Union All
select 5,studentname
,FeeName='5-NetAmount' 
, 0
,FeeIndex=null
, SUM(Feeamount-ExclusionAmount-ConcessionAmt )
from StudentFeeDetails a
Group by StudentName


Union All
select 6,studentname
,FeeName='6-Paid' 
, 0
,FeeIndex=null
, SUM(PaidAmount )
from StudentFeeDetails a
Group by StudentName


Union All
select 7,studentname
,FeeName='7-Balace Fee' 
, 0
,FeeIndex=null
, SUM(Feeamount-PaidAmount-ExclusionAmount-ConcessionAmt )
from StudentFeeDetails a
Group by StudentName
)X

go


Exec ProcFeeReport '87423148-616B-4FFD-BB23-6850876BC9F5','08ACDB07-FBA6-4B07-AD14-C63F991E6ED0'

select * from StudentFeeDueList