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