Workday Calculation - SQL Server Forums

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)

Workday Calculation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-01 : 07:55:58

Bill writes "Can you help me with building a workday function?

I would like to input the number of workdays to add or subtract from a particular date, the date to add or subtract from/to, and have the function return the calculated date.

For example:
wkday= #06/23/02#
addworkday(5,wkday)

would return 06/28/02

It would also be nice to be able to input an array of holidays to exclude.

Thanks for your help.

SQL Server 2000
Windows 2000"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-01 : 09:10:31

This is NOT an easy issue....mainly because of the 'holidays' situation....but below is a SP which I use to deal with this style of problem....you'll have to play with it to see if it is useful for you....

basically i've the following data

table 1. a time-period table....
Id Name DistanceFromTodayStart DistanceFromTodayEnd
---- ---- ---------------------- --------------------
1 T 0 0
2 T+1 1 1
3 T+2+ 2 32767
5 T-1 -1 -1
6 T-2- -32767 -2
7 ALL -32767 32767

describing the start + end of certain valid time-periods....in relative terms to some base date....works backwards and forwards....data can be expanded for more time-slots...as required.

where T stands for Today...T+1 is Today + 1 working day, etc...
when it comes to the maths later, T+1 starts 1 millisecond after T finishes...which may be a non-working day, but T+1 will definitely include a working day by the time T+1 is finished.

for example....with last Friday as the base date, T = Friday from 00:00 to 23:39...and T+1 starts Saturday 00:01 and ends tonight (Monday) @ 23:59, given that today is a working day. But with today Monday as base date, T = Monday, T-1 = Friday (00:01) through to Sunday (23:59)...

table 2...calendar table....listing ALL working/non-working dates...saturdays/sundays included...some people consider these as working days!....(0 = non-working day)

FKBranchCode DayInYear WorkDay
------------ --------------------------- -------
BR01 2002-06-20 00:00:00 1
BR01 2002-06-21 00:00:00 1
BR01 2002-06-22 00:00:00 0
BR01 2002-06-23 00:00:00 0
BR01 2002-06-24 00:00:00 1
BR01 2002-06-25 00:00:00 1
BR01 2002-06-26 00:00:00 1
BR01 2002-06-27 00:00:00 1
BR01 2002-06-28 00:00:00 1
BR01 2002-06-29 00:00:00 0
BR01 2002-06-30 00:00:00 0
BR01 2002-07-01 00:00:00 1
BR01 2002-07-02 00:00:00 1
BR01 2002-07-03 00:00:00 1
BR01 2002-07-04 00:00:00 1
BR01 2002-07-05 00:00:00 1
BR02 2002-06-20 00:00:00 1
BR02 2002-06-21 00:00:00 1
BR02 2002-06-22 00:00:00 0
BR02 2002-06-23 00:00:00 0
BR02 2002-06-24 00:00:00 1
BR02 2002-06-25 00:00:00 1
BR02 2002-06-26 00:00:00 1
BR02 2002-06-27 00:00:00 1
BR02 2002-06-28 00:00:00 1
BR02 2002-06-29 00:00:00 0
BR02 2002-06-30 00:00:00 0
BR02 2002-07-01 00:00:00 1
BR02 2002-07-02 00:00:00 1
BR02 2002-07-03 00:00:00 1
BR02 2002-07-04 00:00:00 1
BR02 2002-07-05 00:00:00 1
BR04 2002-06-20 00:00:00 1
BR04 2002-06-21 00:00:00 1
BR04 2002-06-22 00:00:00 0
BR04 2002-06-23 00:00:00 0
BR04 2002-06-24 00:00:00 1
BR04 2002-06-25 00:00:00 1
BR04 2002-06-26 00:00:00 1
BR04 2002-06-27 00:00:00 1
BR04 2002-06-28 00:00:00 1
BR04 2002-06-29 00:00:00 0
BR04 2002-06-30 00:00:00 0
BR04 2002-07-01 00:00:00 1
BR04 2002-07-02 00:00:00 1
BR04 2002-07-03 00:00:00 1
BR04 2002-07-04 00:00:00 1
BR04 2002-07-05 00:00:00 1

(48 row(s) affected)

table 3....branch_time_period_table...sample table after running stored procedure at the bottom.
fkBranchCode fkTimePeriodId TodayDate StartDate EndDate
------------ -------------- --------------------------- --------------------------- ---------------------------
BR01 1 2002-07-01 13:40:00 2002-06-29 00:00:00 2002-07-01 00:00:00.000
BR01 2 2002-07-01 13:40:00 2002-07-02 00:00:00 2002-07-02 00:00:00.000
BR01 3 2002-07-01 13:40:00 2002-07-03 00:00:00 2099-12-31 23:59:59.000
BR01 5 2002-07-01 13:40:00 2002-06-28 00:00:00 2002-06-28 00:00:00.000
BR01 6 2002-07-01 13:40:00 1900-01-01 00:00:00 2002-06-27 00:00:00.000
BR01 7 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000
BR02 1 2002-07-01 13:40:00 2002-06-29 00:00:00 2002-07-01 00:00:00.000
BR02 2 2002-07-01 13:40:00 2002-07-02 00:00:00 2002-07-02 00:00:00.000
BR02 3 2002-07-01 13:40:00 2002-07-03 00:00:00 2099-12-31 23:59:59.000
BR02 5 2002-07-01 13:40:00 2002-06-28 00:00:00 2002-06-28 00:00:00.000
BR02 6 2002-07-01 13:40:00 1900-01-01 00:00:00 2002-06-27 00:00:00.000
BR02 7 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000
BR03 1 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000
BR03 2 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000
BR03 3 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000
BR03 5 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000
BR03 6 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000
BR03 7 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000
BR04 1 2002-07-01 13:40:00 2002-06-29 00:00:00 2002-07-01 00:00:00.000
BR04 2 2002-07-01 13:40:00 2002-07-02 00:00:00 2002-07-02 00:00:00.000
BR04 3 2002-07-01 13:40:00 2002-07-03 00:00:00 2099-12-31 23:59:59.000
BR04 5 2002-07-01 13:40:00 2002-06-28 00:00:00 2002-06-28 00:00:00.000
BR04 6 2002-07-01 13:40:00 1900-01-01 00:00:00 2002-06-27 00:00:00.000
BR04 7 2002-07-01 13:40:00 1900-01-01 00:00:00 2099-12-31 23:59:59.000

(24 row(s) affected)

item 4....the main SP.....
CREATE PROCEDURE USP_CHECK_UPDATE_BRANCH_TIME_PERIOD
@INFIXEDDATE CHAR(19), @INDATE CHAR(19)
AS
BEGIN
SET DATEFORMAT DMY
SET NOCOUNT ON

DECLARE @LOCALNOW DATETIME, @LOCALNOWSTART DATETIME, @LOCALNOWEND DATETIME
DECLARE @LOCALBRANCHCODE CHAR(4), @LOCALSTARTDATE DATETIME, @LOCALENDDATE DATETIME, @LOCALSTARTDATESAVE DATETIME, @LOCALENDDATESAVE DATETIME
DECLARE @LOCALDIRECTIONCHAR CHAR(1), @LOCALITERATIONCHAR CHAR(1), @LOCALCONTINUECHAR CHAR(1), @LOCALPERIODNAME CHAR(4)
DECLARE @LOCALCURRENTITERATION INTEGER, @LOCALITERATION INTEGER, @LOCALDIRECTIONMULTIPLIER INTEGER
DECLARE @OLDDATA TINYINT, @NEWDATA TINYINT, @INTIMEPERIODCODE TINYINT

SET @OLDDATA = 0
SET @NEWDATA = 1
DECLARE @TODAYDATE DATETIME
DECLARE @A2 CHAR(19), @A4 CHAR(19)
SELECT @TODAYDATE = TODAYDATE FROM BRANCH_TIME_PERIOD
SET @A2 = CONVERT(VARCHAR(10),@TODAYDATE,103)
SET @A4 = CONVERT(VARCHAR(10),@INDATE,103)
IF @A2 <> @A4
BEGIN
SET @OLDDATA = 1
DELETE FROM BRANCH_TIME_PERIOD
-- TRUNCATE TABLE BRANCH_TIME_PERIOD
END
IF @OLDDATA <> @NEWDATA RETURN

DECLARE CU_BRANCH CURSOR FOR SELECT a.CODE, b.ID, b.NAME FROM BRANCH a, TIME_PERIOD b
OPEN CU_BRANCH
FETCH NEXT FROM CU_BRANCH INTO @LOCALBRANCHCODE, @INTIMEPERIODCODE, @LOCALPERIODNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LOCALDIRECTIONCHAR = SUBSTRING(@LOCALPERIODNAME,2,1)
SET @LOCALITERATIONCHAR = SUBSTRING(@LOCALPERIODNAME,3,1)
SET @LOCALCONTINUECHAR = SUBSTRING(@LOCALPERIODNAME,4,1)
IF ISNUMERIC(@LOCALITERATIONCHAR) > 0 SET @LOCALITERATION = @LOCALITERATIONCHAR
ELSE SET @LOCALITERATION = 0
IF @LOCALDIRECTIONCHAR = '-' SET @LOCALDIRECTIONMULTIPLIER = -1
ELSE SET @LOCALDIRECTIONMULTIPLIER = 1
GOTO BRANCHSTART
BRANCHEND:
FETCH NEXT FROM CU_BRANCH INTO @LOCALBRANCHCODE, @INTIMEPERIODCODE, @LOCALPERIODNAME
END
CLOSE CU_BRANCH
DEALLOCATE CU_BRANCH
SET NOCOUNT OFF
END
RETURN

BRANCHSTART:
SET @LOCALNOW = @INDATE
SET @LOCALCURRENTITERATION = 0
SET @LOCALSTARTDATE = '01/01/1900'
SET @LOCALENDDATE = @INFIXEDDATE
SET @LOCALSTARTDATESAVE = '01/01/1900'
SET @LOCALENDDATESAVE = @INFIXEDDATE
IF @LOCALPERIODNAME = 'ALL ' GOTO BRANCHP3
BRANCHP1:
GOTO LOOPSTART
BRANCHP2:
IF @LOCALSTARTDATE <> @LOCALSTARTDATESAVE OR @LOCALENDDATE <> @LOCALENDDATESAVE
BEGIN
IF @LOCALDIRECTIONMULTIPLIER > 0
SELECT @LOCALNOW = DATEADD(DAY, @LOCALDIRECTIONMULTIPLIER, @LOCALENDDATE)
IF @LOCALDIRECTIONMULTIPLIER < 0
SELECT @LOCALNOW = DATEADD(DAY, @LOCALDIRECTIONMULTIPLIER, @LOCALSTARTDATE)
SELECT @LOCALCURRENTITERATION = @LOCALCURRENTITERATION + 1
IF @LOCALCURRENTITERATION > @LOCALITERATION GOTO BRANCHP3
SET @LOCALSTARTDATESAVE = @LOCALSTARTDATE
SET @LOCALENDDATESAVE = @LOCALENDDATE
GOTO BRANCHP1
END
BRANCHP3:
IF @LOCALCONTINUECHAR <> "" AND @LOCALDIRECTIONMULTIPLIER < 0 SET @LOCALSTARTDATE = '01/01/1900'
IF @LOCALCONTINUECHAR <> "" AND @LOCALDIRECTIONMULTIPLIER > 0 SET @LOCALENDDATE = @INFIXEDDATE
INSERT INTO BRANCH_TIME_PERIOD (FKBRANCHCODE, FKTIMEPERIODID, TODAYDATE, STARTDATE, ENDDATE)
VALUES (@LOCALBRANCHCODE, @INTIMEPERIODCODE, @INDATE, @LOCALSTARTDATE, @LOCALENDDATE)
GOTO BRANCHEND

LOOPSTART:
SELECT @LOCALSTARTDATE=DATEADD(DAY, CASE A.WORKDAY
WHEN 1 THEN
(SELECT COUNT(*) FROM CALENDAR B
WHERE B.DAYINYEAR <= A.DAYINYEAR
AND B.DAYINYEAR > (SELECT TOP 1 DAYINYEAR FROM CALENDAR C
WHERE C.FKBRANCHCODE = A.FKBRANCHCODE
AND C.DAYINYEAR < A.DAYINYEAR AND C.WORKDAY=1
ORDER BY C.DAYINYEAR DESC)
AND B.WORKDAY=0
AND B.FKBRANCHCODE=A.FKBRANCHCODE)
WHEN 0 THEN
(SELECT COUNT(*) FROM CALENDAR B
WHERE B.DAYINYEAR <= A.DAYINYEAR
AND B.DAYINYEAR > (SELECT TOP 1 DAYINYEAR FROM CALENDAR C
WHERE C.FKBRANCHCODE = A.FKBRANCHCODE
AND C.DAYINYEAR < A.DAYINYEAR AND C.WORKDAY=1
ORDER BY C.DAYINYEAR DESC)
AND B.WORKDAY=0
AND B.FKBRANCHCODE=A.FKBRANCHCODE) -1
END
* -1 ,A.DAYINYEAR),
@LOCALENDDATE=DATEADD(DAY, CASE A.WORKDAY
WHEN 1 THEN
(SELECT COUNT(*) FROM CALENDAR B
WHERE B.DAYINYEAR >= A.DAYINYEAR
AND B.DAYINYEAR < (SELECT TOP 1 DAYINYEAR FROM CALENDAR C
WHERE C.FKBRANCHCODE = A.FKBRANCHCODE
AND C.DAYINYEAR <= A.DAYINYEAR AND C.WORKDAY=1
ORDER BY C.DAYINYEAR ASC)
AND B.WORKDAY=0
AND B.FKBRANCHCODE=A.FKBRANCHCODE)
WHEN 0 THEN
(SELECT COUNT(*) FROM CALENDAR B
WHERE B.DAYINYEAR >= A.DAYINYEAR
AND B.DAYINYEAR < (SELECT TOP 1 DAYINYEAR FROM CALENDAR C
WHERE C.FKBRANCHCODE = A.FKBRANCHCODE
AND C.DAYINYEAR >= A.DAYINYEAR AND C.WORKDAY=1
ORDER BY C.DAYINYEAR ASC)
AND B.WORKDAY=0
AND B.FKBRANCHCODE=A.FKBRANCHCODE)
END, A.DAYINYEAR)
FROM CALENDAR A
WHERE A.DAYINYEAR = LEFT(@LOCALNOW,11)
AND A.FKBRANCHCODE = @LOCALBRANCHCODE
GOTO BRANCHP2

item 5....finally called by
SET DATEFORMAT DMY
EXEC USP_CHECK_UPDATE_BRANCH_TIME_PERIOD @INFIXEDDATE=@INFIXEDDATE, @INDATE=@INDATE

where @INFIXEDDATE = "2099/12/31 23:59:59"...a dummy date which I use to signify infinty....it makes sense in terms of the sample input data above....
and @INDATE = "2002/07/01" as in today...(1st July)....or whatever base date you want to supply.

The procedure caters for multiple branches having seperate independent "working date" calendars....if you don't have to worry about that, then the solution can become a little simpler.

I'm suppose the cursor part could be simplified, but for the moment it works for me!....

To speed things up, there is a bit of code at the start to figure out if data needs to be deleted or not....(this is a multi-user table, each of whom can independently pick a time-period (but all in my case, have the same base-date each day...)...it examines the data to see if there is a need to re-build the branch_time_period_table....(avoiding the cursor code is worthwhile!!!)

finally....a specific record in the branch_time_period table is joined to the raw data, based on the raw data falling between the calculated startdate and endate inclusive...

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources