Dear Team,
My requirement is - I need to fetch yearly records of students attendance from SQL Server Stored Procedure.
For that, I have made the Stored Procedure. But, the SP is taking lot of time while executing.
I am sharing my Stored PRocedure -
alter procedure [dbo].[SP_Student_Attendance]
(
@FromDate Date , @ToDate Date , @Class int
)
as
Begin
set nocount on
declare @Date Date
declare @Day int
declare @Dayloop int
declare @strString nvarchar(max)
declare @strString1 nvarchar(max)
declare @Yr int
declare @YrPlus int
declare @FD date
set @Date = @FromDate
set @Day=day(@Date)
set @FD = @FromDate
create table #temp_Student_Attendance(AM_ID bigint, Class varchar(100), Division varchar(100), Roll_No varchar(20),
[Name] varchar(255), CM_ID bigint,Gender char(50),Category char(250),Caste char(250),SubCaste char(250),Status varchar(250),
Admission_Category varchar(100))
begin
set @strString='insert into #temp_Student_Attendance(AM_ID, Class, Division,Roll_No,[Name],CM_ID,Gender,Category,Caste,SubCaste,Status,Admission_Category)
select distinct a.AM_ID, b.Class, b.Division, b.Roll_No, (case when a.Candidate_FName is not null then a.Candidate_FName else '''' end +
case when isnull(a.Candidate_MName,'''')<>'''' then '' ''+a.Candidate_MName else '''' end +
case when isnull(a.Candidate_LName,'''')<>'''' then '' ''+a.Candidate_LName else '''' end) [Name],b.SrNo,
Gender,
c.description as Category,
a.Caste,a.SubCaste,
e.description , a.Admission_Category
from dbo.Admission_Master a, dbo.Class_Master b,
TBLMainMaster c ,
TBLMainMaster e , DAV_Student_Attendance f
where a.AM_ID=b.AM_ID and a.active=1 and b.active=1
and format( cast(f.Date as date),''dd-MMM-yyyy'') between ''' + format( cast(@FromDate as date),'dd-MMM-yyyy') + ''' and ''' + format( cast(@ToDate as date),'dd-MMM-yyyy') + '''
and f.Active = 1
and f.CM_ID in (select srno from DAV_Class_Master d where d.Active = 1 and a.AM_ID = d.AM_ID)
and a.category=c.sub_code and c.Main_Code=3
and e.sub_code=a.status and e.main_code=9
and b.Division is not null and a.Status=4 and b.Period='''+cast(@Period as varchar)+''' '
set @strString1='insert into #temp_Student_Attendance(AM_ID, Class, Division,Roll_No,[Name],CM_ID,Gender,Category,Caste,SubCaste,Status,Admission_Category)
Select distinct a.AM_ID, b.Class, b.Division, b.Roll_No, (case when a.Candidate_FName is not null then a.Candidate_FName else '''' end +
Case when isnull(a.Candidate_MName,'''')<>'''' then '' ''+a.Candidate_MName else '''' end +
Case when isnull(a.Candidate_LName,'''')<>'''' then '' ''+a.Candidate_LName else '''' end)[Name],b.SrNo,
Gender,a.Caste,a.SubCaste,
--c.description as Category,case when a.Category=21 then Category_Others else '''' end Category_Others,
c.description as Category,
e.description , a.Admission_Category
from dbo.DAV_Admission_Master a inner join DAV_Class_Master b on a.AM_ID=b.AM_ID and a.active=1 and b.active=1
inner join TBLMainMaster c on a.category=c.sub_code And c.Main_Code=3
inner join dav_student_tc_details d on a.am_id=d.AM_ID
inner join TBLMainMaster e on e.sub_code=a.status and e.main_code=9 where
format( cast(d.[created_dt] as date),''dd-MMM-yyyy'') between ''' + format( cast(@FromDate as date),'dd-MMM-yyyy') + ''' and ''' + format( cast(@ToDate as date),'dd-MMM-yyyy') + '''
and b.Division is not null and a.Status=4 and b.Period='''+cast(@Period as varchar)+''' '
print @strString;
print @strString1;
if @Class is not null
Begin
set @strString=@strString+' and b.Class='+cast(@Class as varchar)
set @strString1=@strString1+' and b.Class='+cast(@Class as varchar)
End
exec sp_executesql @strString
exec sp_executesql @strString1
print @strString;
print @strString1;
set @Dayloop=1
while @Date<=@ToDate
Begin
set @strString='alter table #temp_Student_Attendance add ['+convert(varchar,@Date,106)+'] varchar(50)'
print @strString;
exec sp_executesql @strString
set @strString='update #temp_Student_Attendance set ['+convert(varchar,@Date,106)+']=
(select case when b.Attendance_Status=1 then ''P'' when b.Attendance_Status=0 then ''A'' else '''' end
from #temp_Student_Attendance a, DAV_Student_Attendance b
where a.CM_ID=b.CM_ID and b.active=1
and convert(varchar,format( cast(b.Date as date),''dd-MMM-yyyy'')) = ''' + convert(varchar, format( cast(@FromDate as date),'dd-MMM-yyyy')) + ''')'
print @strString ;
exec sp_executesql @strString
set @Dayloop=@Dayloop+1
set @Date = DATEADD(day,1,@Date)
set @FromDate = DATEADD(day,1,@FromDate)
End
set @Date = @FD
update #temp_Student_Attendance set Class=b.Description
from #temp_Student_Attendance a, TBLMainMaster b
where a.Class=b.Sub_Code and b.Main_Code=1 and b.active=1
update #temp_Student_Attendance set Division=b.Description
from #temp_Student_Attendance a, TBLMainMaster b
where a.Division=b.Sub_Code and b.Main_Code=13 and b.active=1
alter table #temp_Student_Attendance add Current_Total int, Previous_Total int, Grand_Total int
set @strString='update #temp_Student_Attendance set Current_Total=b.Total
from #temp_Student_Attendance a,
(select CM_ID, sum(case when b.Attendance_Status=1 then 1 else 0 end) Total from DAV_Student_Attendance b
where b.active='+ @Act +' and
format( cast(b.Date as date),''dd-MMM-yyyy'') between '''+format( cast(@FD as date),'dd-MMM-yyyy')+''' and '''+format( cast(@ToDate as date),'dd-MMM-yyyy')+'''
group by CM_ID) b
where a.CM_ID=b.CM_ID'
print @strString
exec sp_executesql @strString
set @strString='update #temp_Student_Attendance set Previous_Total=isnull(b.Total,0)
from #temp_Student_Attendance a,
(select CM_ID, sum(case when b.Attendance_Status=1 then 1 else 0 end) Total from DAV_Student_Attendance b
where b.active='+ @Act +'
and format( cast(b.Date as date),''dd-MMM-yyyy'') between ''' + format( cast(@FD as date),'dd-MMM-yyyy') + ''' and ''' + format( cast(@ToDate as date),'dd-MMM-yyyy') + '''
group by CM_ID) b where a.CM_ID=b.CM_ID'
print @strString;
exec sp_executesql @strString
set @strString='update #temp_Student_Attendance set Grand_Total=isnull(Current_Total,0)+isnull(Previous_Total,0)'
exec sp_executesql @strString
set @strString='select AM_ID,Class,Division,Roll_No,[Name],Gender,Caste,SubCaste,Status,
(Select Description from TBLMainMaster where Main_Code=28 and Sub_Code = Admission_Category) "Admission_Category" '
set @Dayloop=1
set @Date = @FD
while @Date<=@ToDate
Begin
set @strString=@strString+',['+convert(varchar,@Date,106)+']'
set @Dayloop=@Dayloop+1
set @Date = DATEADD(day,1,@Date)
End
set @strString=@strString+', Current_Total, Previous_Total, Grand_Total from #temp_Student_Attendance order by Class, Division, [Name]'
exec sp_executesql @strString
End