hi, get the business days betwen 2 dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

hi, get the business days betwen 2 dates

Author

Topic

ravindra.nadavala
Starting Member

4 Posts

Posted - 2008-07-18 : 00:11:01

hi,

I have table called Test(TaskId(identity column),StartDate date,EndDate date)

insert into Test values('12/12/2008','12/20/2008')
insert into Test values('11/11/2008','12/20/2008')

then i have to alter table like
alter table Test add (workingdays int)

update the table set workingdays = no.of business days(startdate,enddate) for each taskid

i tried a lot...but i am not getting, by where condtion i can able to do this ..i don't know how to do this for all the records

Thanks in Advance

Ravindra.N

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-18 : 00:28:31

This is complicated because I assumue you will need to cater for public holidays as well as weekends. Are the public holidays the same for all users doing tasks?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-18 : 00:30:19

In Australia, for example, there are some public holidays at the state level. And also, for example, I understnad in New Zealand, a regional area or city has a holiday different to otehr areas.

ravindra.nadavala
Starting Member

4 Posts

Posted - 2008-07-18 : 01:19:39

hi,
please conider Sat and sunday are holidays...

Thanks
Ravindra.N

quote:
Originally posted by dexter.knudson

In Australia, for example, there are some public holidays at the state level. And also, for example, I understnad in New Zealand, a regional area or city has a holiday different to otehr areas.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-07-18 : 02:36:59

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105208

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-18 : 02:38:49

Try this

declare @Test as table(TaskId int identity(1,1),StartDate datetime,EndDate datetime)

insert into @Test values('12/12/2008','12/20/2008')
insert into @Test values('11/11/2008','12/20/2008')

select dates as BusinessDays from
(
select dateadd(dd,number,StartDate)as dates,TaskId from @test inner join master.dbo.spt_values on master.dbo.spt_values.type='p'
where master.dbo.spt_values.number<=datediff(dd,startdate,enddate)

)t where datename(dw,dates)<> ('Saturday') and datename(dw,dates)<> ('sunday')

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-18 : 02:40:03

quote:
Originally posted by sunil

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105208

Missed by minute and a half.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-18 : 03:28:08

It's better if you can calculate the days instead of counting them

create function workdays_jeff(@StartDate datetime, @EndDate datetime) returns float as
begin

return CAST(
                (
                  (DATEDIFF(dd,@StartDate,@EndDate)+1)
                 -(DATEDIFF(wk,@StartDate,@EndDate)*2) 
                 -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
                 -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END)
                 )
                -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' or DATENAME(dw,@StartDate) = 'Saturday' THEN 0
                       ELSE CAST(CONVERT(CHAR(12),@StartDate,114) AS DATETIME) END)
                -(CASE WHEN DATENAME(dw,@EndDate) = 'Sunday' or DATENAME(dw,@EndDate) = 'Saturday' THEN 0 
                       ELSE (1.0-CAST(CONVERT(CHAR(12),@EndDate,114) AS DATETIME)) END)
        AS FLOAT)
end

E 12°55'05.25"
N 56°04'39.16"

ravindra.nadavala
Starting Member

4 Posts

Posted - 2008-07-18 : 04:51:56

its working great,

but i need to count the working days and update the workingdays column in the table, for each TaskId..

thanks in advance.

quote:
Originally posted by ayamas

quote:
Originally posted by sunil

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105208

Missed by minute and a half.

ravindra.nadavala
Starting Member

4 Posts

Posted - 2008-07-18 : 04:58:54

hi,
thanks for reply..

i need to dothis for each record in the table and update the same table column working days with this count ..is this possible through function or procedure

quote:
Originally posted by ravindra.nadavala

its working great,

but i need to count the working days and update the workingdays column in the table, for each TaskId..

thanks in advance.

quote:
Originally posted by ayamas

quote:
Originally posted by sunil

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105208

Missed by minute and a half.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 05:13:05

quote:
Originally posted by ravindra.nadavala

its working great,

but i need to count the working days and update the workingdays column in the table, for each TaskId..

thanks in advance.

quote:
Originally posted by ayamas

quote:
Originally posted by sunil

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105208

Missed by minute and a half.

you can convert the code into a UDF with two parameters and call it for each taskid by passing the dates

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources