Apr 17, 2020 02:56 AM|Yang Shen|LINK
employee monthly report with start date and end date in which it should come empid empname attendancedate punchin punchout and remark of absent or present
I want the Stored Procedure which i can use in my code
Custom funtion can also be used just like the SP and even easier to use, check below SP demo if you insist:
create proc SelectAttendance @EmpID varchar(10),@start date,@end date
select @EmpID as EmpID,
(select EmpName from employee_details where EmpID=@EmpID) as EmpName,
s.punchdate as [Attendance Date],ss.PunchIn,ss.PunchOut,
(case when ss.EmpID is null then 'Absent' when DATEPART(DW, s.punchdate)=1 or DATEPART(DW, s.punchdate)=7 then 'WO' else 'Present' end) as [attendance]
from (select distinct punchdate from empshift_details) s left join (select * from empshift_details where EmpID=@EmpID) ss on s.punchdate=ss.punchdate
left join employee_details d on ss.EmpID=d.EmpID where s.punchdate BETWEEN @start AND @end
exec SelectAttendance @EmpID ='MT1020',@start='2020-03-31',@end='2020-04-04'
As you can see, this SP accepts 3 parameters and return all the fields you want.
Check SQL Stored Procedures for SQL Server for more information about Stored Procedure With Multiple Parameters.