Month-to-month function

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