February 24, 2004 at 8:57 am
Please help; I need a script to calculate the number of working days from the first day of the month to the current day, excluding Saturdays and Sundays. I need this calc in a formula. MTD Budget = (Budget for the month/Number of working days in the month)*number of working days up to current day.
February 24, 2004 at 9:54 am
Use what you need.
DECLARE @Date Datetime
DECLARE @Days int
SET @Date = '01/01/2004'
SET @Days = 0
WHILE @Date <= GetDATE()
BEGIN
IF DatePart(weekday, @Date) BETWEEN 2 AND 6
SET @Days = @Days + 1
SET @Date = @Date + 1
END
PRINT CAST(@Days as varchar)
February 24, 2004 at 10:16 am
I like what Grasshopper did but, you need to carry this further.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
February 24, 2004 at 11:24 am
This is the approach I use to solve this problem:
Create a table that holds 1 row per day for maybe 2 o three years (depending on your requirements) Like:
Create Table Canlendar
(
Dte smalldatetime -- One row Per day
, WorkDay tinyint -- 1 is work day 0 is Not
)
And the Rest is really easy
Select ([Budget for the month]/
( -- Total Number of days
SELECT Sum(WorkDay)
From Calendar
where Dte
Between
DATEADD(d,1-DAY(@Date),CONVERT(char(8),@Date,112)) --First Day
and
DATEADD(m,1,DATEADD(d,1-DAY(@Date),CONVERT(char(8),@Date,112))) --Last Day
)
) *
( -- Number of days since Day1
SELECT Sum(WorkDay)
From Calendar
where Dte
Between
DATEADD(d,1-DAY(@Date),CONVERT(char(8),@Date,112)) --First Day
and
Cast(CONVERT(char(8),getdate(),112) as smalldatetime) --Last Day
)
It may not look as sexy as a funtion would but :
1. if you have to include Holidays it will be as easy as just to set WorkDay on those days to 1
2. If the schedule varies depending on anything like depertment or project or something else is just a matter of adding another column to the Calendar table
3. If the Number of Rows to proccess is HIGH then this could be faster that a function
Just my $0.02
* Noel
February 25, 2004 at 5:01 am
Thank you master.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply