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

rifayeem

SOLVED CLOSED ACTIVE ANSWERED
User: rifayeem
Posted: on Mar 18, 2013 05:20 AM
Forum: IIS Configuration
Answer: 1
Views: 15100

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.....

Mudassar

0 3

Reply
ANSWER
User: Mudassar
Replied: on Mar 18, 2013 06:53 AM
Report

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

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

Company

Explore

Follow us

Contact Us