SILLY SQL - Working days (network days equivilant)

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 2000 Forums

SQL Server Development (2000)

SILLY SQL - Working days (network days equivilant)

Author

Topic

greeny122229
Starting Member

25 Posts

Posted - 2009-05-07 : 10:01:14

Hi Guys,

Hope your all well, can anybody assist with a function or a code that will do the following sum

date + (number of working days(including bank hols)) = date

examples:
01/05/09 + 5 = 08/05/09 (as 4th was bhol)
05/05/09 + 10 = 18/05/09

I have the following function already which looks up bank hols however this will only clculate working days between 2 dates - however as above i need to calculate the 2nd date:

CREATE FUNCTION dbo.udfWorkDaysLessBH
(@StartDate DATETIME,@EndDate DATETIME = NULL
)RETURNS INT
AS
BEGIN
DECLARE @Swap DATETIME
DECLARE @HolDays int
--Hold Bank Holiday count between @StartDate and @EndDate
IF @StartDate IS NULL
RETURN NULL

IF @EndDate IS NULL
SELECT @EndDate = @StartDate

SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) ,0)

IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap

SELECT @HolDays = (SELECT COUNT(*) FROM tblBankHolidays WHERE hol_date > DATEADD(dd, -1, @StartDate) AND hol_date < DATEADD(dd, 1, @EndDate))

RETURN (SELECT
(DATEDIFF(dd,@StartDate,@EndDate)+1)
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
-@HolDays
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END) )
END

any help appreciated

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-07 : 11:16:30

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118127
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107799 (in this one read the post by Micheal V. Jones)

Be One with the Optimizer
TG

greeny122229
Starting Member

25 Posts

Posted - 2009-05-07 : 12:08:20

created my own way of doing this using a hash table, to minus bank hols you will need to create a bankhol lookup table however this will do the rest for you, enjoy:

declare @end datetime
declare @start datetime
declare @isworkday int
declare @isbankhol int
declare @daystoadd int
set @end = '01 aug 2009'
set @start = dateadd(d,-365,@end)
set @isworkday = 0
set @daystoadd = 15

CREATE TABLE #workingdays
(
acDate datetime,
dayname VARCHAR(9),
isworkday int,
isbankhol int
)
while (select @start) < @end
begin
set @start = dateadd(d,1,@start)
set @isworkday = case when (DATENAME(dw,@start))not in('Saturday','Sunday') then 1 else 0 end
set @isbankhol = case when @start in(SELECT hol_date FROM ssqtrnt03cgcfge.master.dbo.tblBankHolidays) then 1 else 0 end
set @isworkday = case when @isbankhol = 1 then 0 else @isworkday end
insert into #workingdays
(acdate,dayname,isworkday,isbankhol)
select @start,DATENAME(dw,@end),@isworkday,@isbankhol
end

select
@start as End_date,
(
select acdate from
(
select top 100 percent (acdate),
(select count(*) from #workingdays
where acdate >= T.acdate and isworkday <> 0) as rnk,isworkday
from #workingdays as T
order by t.acdate desc
) as a
where rnk = 15 and isworkday=1
) as Start_date

DROP TABLE #workingdays

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources