MSSQL 2005 Question

[webit]

Folks,

We need to be able to calculate the number of net working days between any given two dates, ie. Case Opened and Case Closed. This function also needs to take into account the UK Bank Holidays.


Is there a built-in function, custom calendar or stored procedure that we can use as I couldn’t find anything in standard SQL functions to do the job?


Thanks all,

[glencooley.com]

Hi

You need to add a holiday dates to a table called holidays to exclude bankholidays. [ IF NOT EXISTS(SELECT * FROM holidays WHERE holiday= @tempdate)
]

Something like this returns working days

CREATE FUNCTION fWorkingDaysBetween (@StartDay datetime, @EndDay datetime)
RETURNS int
AS
BEGIN

DECLARE
@date1 datetime,
@date2 datetime,
@tempdate datetime,
@day int,
@count int,
@multiplier int

IF @StartDay < @EndDay
BEGIN
SET @date1 = CONVERT(datetime ,(CONVERT(char(10),@StartDay,102)),102 )
SET @date2 = CONVERT(datetime ,(CONVERT(char(10),@EndDay,102)),102 )
SET @multiplier=1
END
ELSE
BEGIN
SET @date2 = CONVERT(datetime ,(CONVERT(char(10),@StartDay,102)),102 )
SET @date1 = CONVERT(datetime ,(CONVERT(char(10),@EndDay,102)),102 )
SET @multiplier=-1
END

SET @tempdate = @date1
SET @count = 0

/* Process till tempdate becomes date2 */
WHILE ( datediff(dd,@tempdate,@date2) >= 0)
BEGIN
SET @day = Datepart(dw,@tempdate)
/* If it is not a Saturday or a Sunday */
IF (@day != 1 AND @day != 7)
/* If it is not a Public Holiday */
IF NOT EXISTS(SELECT * FROM holidays WHERE holiday= @tempdate)
/* Increment @tempdate by 1 day */
SELECT @count = @count + 1

ET @tempdate = Dateadd(dd,1,@tempdate)
END

RETURN @count * @multiplier
END


Note that this isnt inclusive of the dates searched.

Thanks

Glen

[atlascs]

Ah, it makes me feel all warm and fuzzy inside seeing some hardcore SQL code going up on UKBF so early on a Tuesday morning :)

Good work Glen....

[DuaneJackson]

Nice function.

Does anyone actually store dates in datetime format then? I always convert to an iso format, ie 20071023 and store it as a big int. I've always found datetime to be a ***** to work with.

[atlascs]

The datetime type is an absolute ***** to work with, but we still use it.

For us it used to get complicated back in the day when our framework was occasionally storing dates in strings (naughty I know). This used to bite us in the arse when our software was installed on a machine running the wrong Windows region date time format.

Now we use datetime variables throughout the software and life is good again.

[glencooley.com]

I always store for production use dates as DATETIME, its only for de nomalized data and reporting that I convert them when I need things like ISO weeks for reporting and stuff.

The only issue I run in to with dates is specifying the correct locale for US/UK dates.

Most of the new date functions or substring functions let you manipulate them as necessary whilst retaining a strong data type.

[glencooley.com]

Atlascs,

It was nice to do some good SQL for a change have spent most of my time recently doing standalone flash kiosks with SQL server doing....SELECT 1,2,3....and if it gets really exciting....loop a directory and parse in individual xml files........oh the joy ;)