Calculate number of leaves taken by Employee in a month in SQL Server

rifayeem
 
on Mar 18, 2013 05:20 AM
11392 Views

i wrote a stored procedure to display the number of days in the particular month.if the person is taking a leave from 26jan to 03feb,using this stored procedure i will split the days as per the months..now i want to pass the employee id in the stored procedure and retrive the total days of the january month this is my code

 this is my stored procedure

ALTER PROCEDURE datecheck
@EmpId int,@startdate datetime,@enddate datetime
as
begin
set nocount on
--here get fdate and tdate of that empid and other thing are same which you are using
  
Select startdate,enddate as totdays from finaldatecheck where EmpId = @EmpId
  
SELECT TOP (DATEDIFF(MONTH, @startdate, @enddate)+1) ROW_NUMBER() OVER
(ORDER BY [object_id])-1 FROM sys.all_objects
,
 
(
SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
FROM n, (SELECT DATEADD(DAY, 1-DAY(@startdate), @startdate)) AS m(m)
) Mon(n,fd,ld) 
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld)
- CASE WHEN @startdate > fd THEN (DATEDIFF(DAY, fd, @startdate)+1) ELSE 0 END
- CASE WHEN @startdate < ld THEN (DATEDIFF(DAY, @enddate, ld)-1) ELSE 0 END
FROM mon
end

this is my table

 

empid   startdate                                     enddate
1            01/25/2013 12:00:00 AM          02/03/2013 12:00:00 AM
2            01/25/2013 12:00:00 AM          02/05/2013 12:00:00 AM
3            01/02/2013 12:00:00 AM          01/09/2013 12:00:00 AM
1            02/02/2013 12:00:00 AM          02/05/2013 12:00:00 AM

pls help me...

i am having dropdownlist in my webpage,if i select the employee id and month january it will retrive the january days for the particular employee.....

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Mudassar
 
on Mar 18, 2013 06:53 AM

Well it is lot diffiicult and I feel you are totally messing up with it. You need to pass EmployeeId and Month but I see that you are passing Start and End Date which is incorrect.

Anyways. I have created something that will help

--GetMonthLeaves 1, 3
CREATE PROCEDURE GetMonthLeaves
@Month int, @EmpId int
AS
BEGIN
	SET NOCOUNT ON;
	declare @Start datetime, @End Datetime, @FirstDayInMonth DateTime, @LastDayInMonth datetime

	select @Start = StartDate, @End = EndDate from FinalDateCheck where Id = @EmpId
	if(Month(@Start) = @Month)
	begin
		SELECT @LastDayInMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Start)+1,0))
		
		if @LastDayInMonth <= @End
		begin
			select DateDiff(Day, @Start, @LastDayInMonth)
		end
		else
		begin
			select DateDiff(Day, @Start, @End)
		end
	end
	else
		begin
			SELECT @FirstDayInMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@End)-1),@End),101)
			select DateDiff(Day, @FirstDayInMonth, @End)
		end
END
GO