February 11, 2003 at 9:03 pm
I tried Paul's solution. Seems to be working for me. I did not get the solution posted by wells. I am using SQL Server 7.0. So I think I will have to stick to the solution by Paul. Greg's solution seems to be interesting too. Will try that one out.
Paul, you are right about the data storage. I don't think that there is any point to crib about it. Thanks for supporting that.
Cheers!
Abhijit
February 13, 2003 at 12:22 pm
Here's how I'd do it (very similar to Greg's, and the same limitations)...
declare @start_date datetime
declare @end_date datetime
set @start_date = '1 jan 2001'
set @end_date = '1 jan 2003'
declare @reference_date datetime --1st Jan of year of start_date
declare @days_from_reference_date_to_start_date int
declare @days_in_period int
declare @s char(8000)
set @reference_date = '1 jan ' + cast(year(@start_date) as varchar(4))
set @days_from_reference_date_to_start_date = datediff(day, @reference_date, @start_date)
set @days_in_period = datediff(day, @start_date, @end_date) + 1
set @s = ''
select @s = rtrim(@s) + jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec
from holiday
where year between datepart(year, @start_date) and datepart(year, @end_date)
set @s = replace(@s, '2', '') --remove days that don't exist
set @s = substring(@s, @days_from_reference_date_to_start_date + 1, @days_in_period) --get days you're interested in
set @s = replace(@s, '1', '') --remove non-work days
print 'The number of work days is: ' + cast(len(@s) as char)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
February 13, 2003 at 9:18 pm
Hi Ryan and Greg,
Your solutions are appreciated.
Now I wonder what is wrong with my logic. The one I posted originally seemed to be going nowhere.
Cheers!
Abhijit
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply