July 17, 2008 at 6:11 am
hi,
i am new to SqlSer and this is my first post in this forum.
i need to update the table by no.of business days beteween the two days.
Example : Create table Test(SNo int (identity column), StartDate datetime,EndDate datetime,WorkingDays)
insert into Test(StartDate,EndDate) values('02/02/2008','02/04/2008')
insert into Test(StartDate,EndDate) values('03/02/2008','03/04/2008')
insert into Test(StartDate,EndDate) values('03/21/2008','03/22/2008')
insert into Test(StartDate,EndDate) values('02/22/2008','02/29/2008')
Then i have to update the table WorkingDays by reading two dates and finding the no.of business days between
Thanks in Advance
Ravindra
July 17, 2008 at 6:35 am
simple... just use the datediff function...
update Test set workingdays = datediff(dd,startdate,enddate)
select * from Test
check help on date functions... JO
July 17, 2008 at 6:58 am
Try the code here
July 17, 2008 at 9:42 am
Best solution for this kind of this is to build a calendar table.
Workdays between two dates isn't the same as datediff(day), because it doesn't count weekends or holidays. The function on the EggHeadCafe page requires the use of an inline function that compares dates one at a time to a table of holidays (which is insane).
Build a calendar table. Takes a couple of minutes to do the initial population, then a little homework and a few simple updates to mark holidays, and a few seconds to mark weekends. Then you can select count(*) from calendar where date between x and y and IsWeekend = 0 and IsHoliday = 0, or whatever your column names end up being.
It's more accurate, faster, easier to manage, and can include localization, if you end up having to work with other calendars (Arabic, Hebrew, Chinese, etc.). It works for non-US holidays, if you want it to. Etc., etc., etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2008 at 10:09 am
This the modified version of the function that Steven Cameron has posted and works for any date range you enter...
--dbo.GetBusinessDays
CREATE function dbo.GetBusinessDays
(
@StartDate datetime,
@EndDate datetime
)
returns int
as
begin
declare @DaysBetween int,
@BusinessDays int,
@Cnt int,
@ret int,
@EvalDate datetime
set @DaysBetween = 0
set @BusinessDays = 0
set @Cnt=0
set @DaysBetween = datediff(Day,@StartDate,@endDate) + 1
while @Cnt < @DaysBetween
begin
select @EvalDate = @StartDate + @Cnt
set @ret = (select case
when convert(varchar(2),datepart(mm,@EvalDate)) + convert(varchar(2),datepart(dd,@EvalDate))
in (11,74,91,1122,1224,1225) then 1 else 0
end
)
if @ret = 0
BEGIN
if ((datepart(dw,@EvalDate) <> 1) and (datepart(dw,@EvalDate) <> 7))
BEGIN
select @BusinessDays = @BusinessDays + 1
END
END
select @Cnt = @Cnt + 1
end
return @BusinessDays
end
--- That is it... gives you the number of business days between dates... see you... JO
July 17, 2008 at 10:30 am
I agree with GSquared. The only way to accurately do this is by using a calendar table. You could so a lot of logic using DateDiff and get an answer, which may be correct, but it is a lot more work in the long run. Also the calendar table will be customizable because holiday's used may be different by business.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2008 at 7:30 pm
A calendar table is good to have. Or, you can do it with a simple formula and then subtract the holidays listed in a Holiday table. The following link tells you what the formula is...
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
Of course, it's not setup for international sites because of the reference to 'Saturday' and 'Sunday'... but it's very fast and can be used in a non-RBAR fashion.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply