Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL-Server , +
I have created a database with two fields "EmpID" (int) and "LeaveDate (DateTime) using SQL Server 2005.
 
How Do I modify the below Stored Procedure to find the total working days for an employee in a specified Month and Year(Eg: August, 2012)?
 
The Total Working Days is as follows;
 
Total Working Days = (Total days in a month) - ( (Weekends: Sat and Sun) + (Number of days' Leave taken by the employee) ).
 
The database fields may be changed as per the requirements.
 
How to change the below specified Stored Procedure to able able to find the Total Working Days for a specified Month and Year (Eg: April, 2011)?
 
 
/*
select dbo.fnGetBusinessDaysInMonth(getdate())
*/
 
CREATE FUNCTION dbo.fnGetBusinessDaysInMonth(@currentDate datetime)
returns int
as
begin
 
declare @dateRange int
declare @beginningOfMonthDate datetime, @endOfMonthDate datetime
 
-- Get the beginning of the month
set @beginningOfMonthDate = dateadd(month, -1, dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1)))
 
-- Get the the beginning date of the next month
set @endOfMonthDate = dateadd(day, -1, dateadd(month, datediff(month, 0, @currentDate) + 1, 1))
 
-- Get the date range between the beginning and the end of the month
set @dateRange = datediff(day, @beginningOfMonthDate, @endOfMonthDate)
 
return
(
-- Get the number of business days by getting the number
-- of full weeks * 5 days a week plus the number days remaining
-- minus any days from the remaining days that are a weekend day
select	@dateRange / 7 * 5 + @dateRange % 7 - 
(
select	count(*)
from
(
select 1 as d
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
) weekdays
where	d <= @dateRange % 7
and	 datename(weekday, dateadd(day, -1, @endOfMonthDate) - d) in ('Saturday', 'Sunday')
)
)
 
end
 

Then write select query as follows:
 
select dbo.fnGetBusinessDaysInMonth(getdate()) - (select count(*) from EmpTable Where EmpID = 123)
Posted 28-Feb-13 18:37pm
Edited 28-Feb-13 18:50pm
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi IamFahhad,
 
Try This .....
-- Number of working days between two dates
DECLARE @fromDate DATETIME, @toDate DATETIME , @ReqMonth SMALLINT, @ReqYear SMALLINT
 
SELECT @ReqMonth=11,@ReqYear =2010
 
SELECT @fromDate = CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01' 
SELECT @toDate = DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01' ))
 
SELECT @fromDate , @toDate 
 
SELECT (DATEDIFF(DAY, @fromDate, @toDate) + 1) 
        - (DATEDIFF(WEEK, @fromDate, @toDate) * 2)  
        - (CASE WHEN DATENAME(weekday, @fromDate) = 'Sunday' THEN 1 ELSE 0 END) 
        - (CASE WHEN DATENAME(weekday, @toDate) = 'Saturday' THEN 1 ELSE 0 END)
	   -- No of Holidays also we need to deduct.

Regards,
GVPrabu
  Permalink  
v3
Comments
iamFahhad at 1-Mar-13 0:23am
   
Hi Prabu,
 
Where do I insert that piece of code?
 
What do you think about using MONTH(08) and YEAR(2012)?
 
I do not want to hard-code the date into the Stored Procedure. I should be able to enter the date as an input.
gvprabu at 1-Mar-13 0:30am
   
Hi,
I will try this and give u the solution
gvprabu at 1-Mar-13 0:42am
   
Hi,
 
I updated my Solution... check like this. I think it will help u
 
:-)
iamFahhad at 1-Mar-13 1:27am
   
Hi,
 
Your sql code gets successful. I put it in a procedure "cat". But it does not give any output. When I select it as follows:
select dbo.cat
 
I'm getting an error as follows:
 
Msg 4104, Level 16, State 1, Line 124
The multi-part identifier "dbo.cat" could not be bound.
gvprabu at 1-Mar-13 1:48am
   
I am not getting your error, Share your Procedure.
If "select dbo.cat" means you need to specify the column names also right...
like "select Column_Name FROM dbo.cat".
iamFahhad at 1-Mar-13 5:12am
   
Yes, exactly. I need to specify the "Month" and "Year" as input.
 
Here's the procedure:
 
alter proc cat
as
begin
-- Number of working days between two dates
DECLARE @fromDate DATETIME, @toDate DATETIME , @ReqMonth SMALLINT, @ReqYear SMALLINT

SELECT @ReqMonth=11,@ReqYear =2010

SELECT @fromDate = CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01'
SELECT @toDate = DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01' ))

SELECT @fromDate , @toDate

SELECT (DATEDIFF(DAY, @fromDate, @toDate) + 1)
- (DATEDIFF(WEEK, @fromDate, @toDate) * 2)
- (CASE WHEN DATENAME(weekday, @fromDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(weekday, @toDate) = 'Saturday' THEN 1 ELSE 0 END)
-- No of Holidays also we need to deduct.
end
gvprabu at 1-Mar-13 6:02am
   
Hi,
 
Check the following code
 
CREATE proc cat
@DateSample DATETIME
as
begin
-- Number of working days between two dates
DECLARE @fromDate DATETIME, @toDate DATETIME , @ReqMonth SMALLINT, @ReqYear SMALLINT
SELECT @ReqMonth=MONTH(@DateSample),@ReqYear =YEAR(@DateSample)
 
SELECT @fromDate = CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01'
SELECT @toDate = DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01' ))
 
SELECT @fromDate , @toDate
 
SELECT (DATEDIFF(DAY, @fromDate, @toDate) + 1)
- (DATEDIFF(WEEK, @fromDate, @toDate) * 2)
- (CASE WHEN DATENAME(weekday, @fromDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(weekday, @toDate) = 'Saturday' THEN 1 ELSE 0 END)
-- No of Holidays also we need to deduct.
END
 
EXEC dbo.cat @DateSample='2013-03-01'
iamFahhad at 1-Mar-13 6:41am
   
Your code is working. But how do I modify it to deduct the holidays by the employee in that particular month? Can you add to your code please?
gvprabu at 1-Mar-13 6:49am
   
Hi Friend,
That details U need to keep in your DB for all Employees Leave Details table.
Without leave Details how to deduct?... How u are storing Leave details?
iamFahhad at 2-Mar-13 0:52am
   
I have table "EmpTab" with two fields "EmpID", "LeaveDate".
So how do I deduct the holidays by the employee for a particular month, say for example, September 2009?
iamFahhad at 2-Mar-13 0:55am
   
The "EmpID" is of type int, "LeaveDate" is of type DateTime.
gvprabu at 2-Mar-13 1:00am
   
Hi,
 
Try this....
 
Declare @LevDays INT,@YourMonth TINYINT=3, @YourYear SMALLINT=2013
SELECT EmpID, ISNULL(COUNT(LeaveDate),0)'LevDays'
FROM EmpLevDtls
WHERE MONTH(LeaveDate)=@YourMonth AND YEAR(LeaveDate)=@YourYear
GROUP BY EmpID
 
Using Join you can make join with above Query... then u ll get the list of days based on EmpID
iamFahhad at 2-Mar-13 1:22am
   
But Joins can be used only between two tables, right? Here we have only one table, "EmpTab". So how do we use Joins in this scenario? Moreover, since I'm using Stored Procedures, do I have to include the above queries in different Procedures, or in the same procedure? Can you please combine it all within a single Stored Procedure?
gvprabu at 2-Mar-13 1:24am
   
Hi,
 
Its simple .... do all work in single procedure only....
 
SELECT E.EmpID, T.LevDays
FROM EmployeeDtls E
INNER JOIN (SELECT EmpID, ISNULL(COUNT(LeaveDate),0)'LevDays'
FROM EmpLevDtls WHERE MONTH(LeaveDate)=@YourMonth AND YEAR(LeaveDate)=@YourYear GROUP BY EmpID) T ON T.EmpID=E.EmpID
gvprabu at 2-Mar-13 1:25am
   
Hi Send your procedure code to my mail...
My email ID : venkateshprabu.g@gmail.com or Upload your SP Script in Code Project.
I will help u.
gvprabu at 2-Mar-13 1:28am
   
In my post I gave some sample Links for SQL JOINs.... check This Post
 
http://www.codeproject.com/Answers/554876/SQLplusjoinplusandpluswhereplusclauseplusdoubt#answer3
gvprabu at 2-Mar-13 1:30am
   
why still now you didn't accept my solution...? :-)
iamFahhad at 2-Mar-13 2:04am
   
Hi Venkatesh,
 
Please check your eMail. I have already sent you my complete code along with the database table design and the Function - UDF. Please modify it to be able to accept the Month and Year (Ex: June, 2011).
 
Thanks.
iamFahhad at 2-Mar-13 2:08am
   
Can I use the below code in my function?
 

create function dbo.GetFirstWorkdayOfMonth(@Year INT, @Month INT)
returns DATETIME
as begin
declare @firstOfMonth VARCHAR(20)
SET @firstOfMonth = CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR) + '-01'
 
declare @currDate DATETIME
set @currDate = CAST(@firstOfMonth as DATETIME)
 
declare @weekday INT
set @weekday = DATEPART(weekday, @currdate)
 
-- 7 = saturday, 1 = sunday
while @weekday = 1 OR @weekday = 7
begin
set @currDate = DATEADD(DAY, 1, @currDate)
set @weekday = DATEPART(weekday, @currdate)
end
 
return @currdate
end
iamFahhad at 2-Mar-13 4:13am
   
I'm getting these errors when I executed your code:
 
Msg 139, Level 15, State 1, Procedure cat, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure cat, Line 164
Must declare the scalar variable "@YourMonth".
 

This is your code:
 
alter proc cat
@DateSample DATETIME
as
begin
-- Number of working days between two dates
Declare @LevDays INT, @YourMonth TINYINT=3, @YourYear SMALLINT=2013
DECLARE @fromDate DATETIME, @toDate DATETIME , @ReqMonth SMALLINT, @ReqYear SMALLINT
SELECT @ReqMonth=MONTH(@DateSample),@ReqYear =YEAR(@DateSample)
 
SELECT @fromDate = CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01'
SELECT @toDate = DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01' ))
 
SELECT @fromDate , @toDate
 
SELECT (DATEDIFF(DAY, @fromDate, @toDate) + 1)
- (DATEDIFF(WEEK, @fromDate, @toDate) * 2)
- (CASE WHEN DATENAME(weekday, @fromDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(weekday, @toDate) = 'Saturday' THEN 1 ELSE 0 END)
-- No of Holidays also we need to deduct.
 

 
SELECT E.EmpID, T.LevDays
FROM EmployeeDtls E
INNER JOIN (SELECT EmpID, ISNULL(COUNT(LeaveDate),0)'LevDays'
FROM EmpTab WHERE MONTH(LeaveDate)=@YourMonth AND YEAR(LeaveDate)=@YourYear GROUP BY EmpID) T ON T.EmpID=E.EmpID
 
END
gvprabu at 2-Mar-13 6:09am
   
Hi You are using SQL 2005 Right.... so Variable Declare and Assignment of variables are separate statements... like
-- In 2005
Declare @LevDays INT, @YourMonth TINYINT, @YourYear SMALLINT
SELECT @YourMonth =3, @YourYear =2013
-- Only in 2008
Declare @LevDays INT, @YourMonth TINYINT=3, @YourYear SMALLINT=2013
gvprabu at 2-Mar-13 7:03am
   
hi... I sent that script
iamFahhad at 3-Mar-13 23:39pm
   
Thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.150326.1 | Last Updated 1 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100