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