Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September:
@.CurrentDate = 9/10/2007
@.EndDate = last working day 9/30/2007
Total# of working days = 15
October:
@.CurrentDate = 10/1/2007
@.EndDate = last working day 10/31/2007
Total# of working days = 23
November:
@.CurrentDate = 11/1/2007
@.EndDate = last working day 11/30/2007
Total# of working days = 22
etc.
Any ideas of how i can approch this?
Thanks in advance.
If we just count out weekends, you can use this script. This will not take into account holidays.
Code Snippet
DECLARE @.startDate DATETIME,
@.dateTest DATETIME,
@.workDays INT
SET @.startDate = getDate()
SET @.dateTest = @.startDate
SET @.workDays = 0
WHILE( MONTH(@.startDate) = MONTH(@.dateTest) )
BEGIN
IF( DATENAME(dw, @.dateTest) != 'SATURDAY' AND DATENAME(dw, @.dateTest) != 'Sunday')
SET @.workDays = @.workDays + 1
SET @.dateTest = @.dateTest + 1
END
SELECT @.workDays
|||You might try to search this forum on "Working Days". Also, give a look to this article about using a "calendar table:"
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
|||
Code Snippet
create function udf_WeekdayCounter
(
@.dtFrom datetime
, @.dtThrough Datetime
)
returns smallint
as
begin
if @.dtThrough <= @.dtFrom
return 0
declare @.iCounter int
set @.iCounter = 0
/*
declare @.dtFrom datetime, @.dtThrough datetime
set @.dtFrom = '11/1/2007'
set @.dtThrough = '11/30/2007'
*/
while @.dtFrom < = @.dtThrough
begin
if datepart(weekday,@.dtFrom) not in (datepart(weekday,'December 30, 2006'), datepart(weekday,'December 31, 2006'))
set @.iCounter = @.iCounter + 1
set @.dtFrom = dateadd(d, 1, @.dtFrom)
end
return @.iCounter
end
GO
select dbo.udf_WeekdayCounter( '11/1/2007', '11/30/2007')
sql