asked
How to create Store Procedure for employee attendance?
Hello Everyone, I have table of Attendance of Employee. ATNSHT_ID (Attendance ID) ATNSHT_ATTEN_DATE(Attendance Date) ATNSHT_EMPLOYEE (Employee) ATNSHT_IN_TIME(In-time) ATNSHT_OUT_TIME(Out-time) ATNSHT_TOTAL_HRS(Total Hours) ATNSHT_ATT_STATUS(Status means Absent(A) or Present(P)) I am using SQL server 2008 R2 I want to create an SP which display the Employee Attendance ,When i enter the particular month and year. The result I want is Employee Name in Row and Month Days in Column and in that column A for Absent and P for Present . Please Can anyone help me???
To confirm your output, you want something like this? EmpName 01 02 03 ... 29 30 31 Fred A A A P P P Jim A P P A A P Sheila P P P P P P yes?
Here's what you do: (1) Write a query to present the data you need in this format: EmpName DayNum AttendanceStatus Fred 01 P Jim 01 P Sheila 02 H (2) Use a [PIVOT][1] [1]:
http://msdn.microsoft.com/en-gb/library/ms177410(v=sql.105).aspx
ALTER procedure [dbo].[Attendance_Rpt] @month int,@year int as begin create TABLE #tmp2(empid int,emp varchar(15),day1 char(1),day2 char(1),day3 char(1), day4 char(1),day5 char(1),day6 char(1), day9 char(1),day8 char(1),day7 char(1), day10 char(1),day20 char(1),day31 char(1), day11 char(1),day21 char(1),day30 char(1), day12 char(1),day22 char(1),day17 char(1), day13 char(1),day23 char(1),day18 char(1), day14 char(1),day24 char(1),day19 char(1), day15 char(1),day25 char(1),day27 char(1), day16 char(1),day26 char(1),day28 char(1), day29 char(1)) --declare @month int --declare @year int --set @year = 2011 --set @month = 01 Declare @Employee_Name varchar(15) Declare @Day int Declare @Status Char(1) Declare @EmployeeId int --set @year = 2013 --set @month = 02 Declare ATTENDANCE_CUR CURSOR Static FOR Select e.EMPMST_ID,E.EMPMST_FIRST_NAME From Emp_master E OPEN ATTENDANCE_CUR IF @@CURSOR_ROWS > 0 BEGIN FETCH NEXT FROM ATTENDANCE_CUR INTO @EmployeeId,@Employee_Name WHILE @@Fetch_status = 0 BEGIN Insert into #tmp2 (empid,emp) Values ( @EmployeeId,@Employee_Name); FETCH NEXT FROM ATTENDANCE_CUR INTO @EmployeeId,@Employee_Name END END CLOSE ATTENDANCE_CUR DEALLOCATE ATTENDANCE_CUR Declare ATTENDANCE_CUR CURSOR Static FOR Select e.EMPMST_ID,E.EMPMST_FIRST_NAME,Day(A.ATNSHT_ATTEN_DATE) as DayOfATTENDANCE ,A.ATNSHT_ATT_STATUS From Emp_master E inner join Atte_sheet A on e.EMPMST_ID = A.ATNSHT_EMPLOYEE Where MONTH(A.ATNSHT_ATTEN_DATE) = @month and YEAR(A.ATNSHT_ATTEN_DATE) = @year Order By E.EMPMST_FIRST_NAME,DAY(A.ATNSHT_ATTEN_DATE) OPEN ATTENDANCE_CUR IF @@CURSOR_ROWS > 0 BEGIN FETCH NEXT FROM ATTENDANCE_CUR INTO @EmployeeId,@Employee_Name,@Day,@Status WHILE @@Fetch_status = 0 BEGIN Declare @Sql nvarchar(2000) Set @Sql = 'Update #tmp2 Set Day'+CONVERT(char(1),@Day) + '='''+ @Status +''' Where empid = ' + CONVERT(Char(1),@EmployeeId) Print @Sql exec sp_executesql @Sql FETCH NEXT FROM ATTENDANCE_CUR INTO @EmployeeId,@Employee_Name,@Day,@Status END END CLOSE ATTENDANCE_CUR DEALLOCATE ATTENDANCE_CUR Declare @nSql nvarchar(2000) Declare @nSql1 nvarchar(2000) Declare @totaldays int set @totaldays=31 if @month=4 or @month=6 begin set @totaldays = 30 end else if @month = 2 and (@year%4)=0 Begin set @totaldays=29 End else if @month = 2 and (@year%4)<>0 Begin set @totaldays=28 end Declare @Counter int set @Counter = 1 set @nSql1 = '' While @Counter <= @totaldays Begin set @nSql1 = @nSql1+' ,Day'+ CONVERT(char(2),@Counter) set @Counter = @Counter + 1 End Set @nSql = 'Select empid, emp ' Set @nSql = @nSql + @nSql1+ ' From #tmp2' exec sp_executesql @nSql Drop Table #tmp2 end
Now I only need "A" on Absent date.
13 People are following this question.
