Determine working dates, relative to given date

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)

Determine working dates, relative to given date

Author

Topic

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2001-07-04 : 06:41:27

I'm working with SQL 7 (in case it matters for the answer)
My problem is that I need to determine working dates relative to today (or more particularly to a given date/time).
I have the following data structures and need to work out what date ranges satisfy each selection. I've come
close with the code at the bottom, but I'm running into problems at the boundary conditions....ie the weekends!!!

All dates shown are in DD/MM/YYYY format...ie it's June and July below.

Calendar Table.
structure BranchCode, DayinYear, WorkingDay
type char(4), smalldate, bit
sampledata ri00, 26/06/2001, 1 - tuesday
ri00, 27/06/2001, 1 - wednesday
ri00, 28/06/2001, 1 - thursday
ri00, 29/06/2001, 1 - friday
ri00, 30/06/2001, 0 - saturday
ri00, 01/07/2001, 0 - sunday
ri00, 02/07/2001, 1 - monday
ri00, 03/07/2001, 1 - tuesday
ri00, 04/07/2001, 1 - wednesday

ri01, 26/06/2001, 1 - tuesday
ri01, 27/06/2001, 1 - wednesday
ri01, 28/06/2001, 1 - thursday
ri01, 29/06/2001, 1 - friday
ri01, 30/06/2001, 0 - saturday
ri01, 01/07/2001, 0 - sunday
ri01, 02/07/2001, 0 - monday ****
ri01, 03/07/2001, 1 - tuesday
ri01, 04/07/2001, 1 - wednesday

**** NOTE different value from previous branch...branch codes relate to different countrys
with different national public holidays....1=working day, 0=non-working day.

Showing that I have to copy with multiple branches is to highlight that whatever solution I arrive
at has to cope with different working calendars in different branches.

TimePeriod Table
structure id, name, distancefromtodaystart, distancefromtodayend
type tinyint, char(4), smallint, smallint
sampledata 1, T, 0, 0
2, T+1, 1, 1
3, T+2+, 2, 32767
6, T-1, -1, -1
7, T-2-, -32767, -2
9, ALL, -32767, 32767

T = "Today (or more accurately the next working date)"....therefore T+1 = "Today + 1 (working date)"
...and T+2+...is "Today + 2 and beyond until forever (or as far in to the future as doesn't matter)"
...and T-1 = "yesterday or more accurately...the last working day"...and so on.
ALL is there to cover all possible dates.

The numbers above (0,1,2,32767,etc) are (under my initial design) offsets from today in terms of days.
I'm open to correction as to whether or not this is the best(or even a good) approach.

What I need to achieve is something like the following on each of the scenarios below.

For branch ri00, if today is 27/06/2001 (a wednesday)
starts on and ends on
T-2- = 02/01/2000 25/06/2001 ....01/01/2000 1st date for which I know a working calendar
T-1 = 26/06/2001 26/06/2001
T = 27/06/2001 27/06/2001
T+1 = 28/06/2001 27/06/2001
T+2+ = 29/06/2001 31/12/2003 (the last date for which I know CURRENTLY know a working calendar)

For branch ri00, but if today is 29/06/2001 (a friday)
starts on and ends on
T-2- = 02/01/2000 27/06/2001
T-1 = 28/06/2001 28/06/2001
T = 29/06/2001 29/06/2001
T+1 = 30/06/2001 02/07/2001...ie it covers the w/e
T+2+ = 03/07/2001 31/12/2003

For branch ri00, but if today is 02/07/2001 (a monday)
starts on and ends on
T-2- = 02/01/2000 28/06/2001
T-1 = 29/06/2001 29/06/2001
T = 30/06/2001 02/07/2001...ie it covers the w/e
T+1 = 03/07/2001 03/07/2001
T+2+ = 04/07/2001 31/12/2003

For branch ri01, but if today is 02/07/2001 (a monday)
starts on and ends on
T-2- = 02/01/2000 28/06/2001
T-1 = 29/06/2001 29/06/2001
T = 30/06/2001 03/07/2001...ie it covers the w/e...and the holiday
T+1 = 04/07/2001 04/07/2001
T+2+ = 05/07/2001 31/12/2003

In general i won't be getting records for saturdays and sundays....but I need to cover off the possibility
that there may be such records. In addition, due to (user)mistakes in identifying public holidays, I may have
some data for the public holidays...mostly Mondays...but as you know, not exclusively. As you can see the set
of date ranges "T-2- through to T+2+" should cover each day in the calendar (I'm guaranteed that there will be
a record present for each day in any one year), and not include any day in more than 1 timeperiod, and also not
leave any date out of a timeperiod....this is my most important requirement and is the nub of my current predicament.

I enclose below what code I have at the moment. Maybe it needs a tweak...maybe it need gutting.
All advice will be appreciated. Once I can get the right values into the temp table, then I'll be using same
to drive a furthur query.
Andrew

SET DATEFORMAT DMY
DECLARE @LOCALTIMEPERIODCODE VARCHAR(5), @LOCALNOW DATETIME

SET NOCOUNT ON
SET @LOCALNOW = '29/06/2001 15:54:40.320' -- these 2 values change the whole time and
SET @LOCALTIMEPERIODCODE = 2 -- are used to drive the query.

DROP TABLE #WORKINGDATE -- in code only when running in QA...for testing.

CREATE TABLE #WORKINGDATE (BRANCHCODE CHAR(4), STARTWORKDATE DATETIME, ENDWORKDATE DATETIME)
INSERT INTO #WORKINGDATE (BRANCHCODE, STARTWORKDATE, ENDWORKDATE)
SELECT a.FKBRANCHCODE, MIN(a.DAYINYEAR) AS a_DAYINYEAR, '01/01/2000'
FROM CALENDAR a, TIME_PERIOD b
WHERE CONVERT(VARCHAR(12),a.DAYINYEAR,112) >= CONVERT(VARCHAR(12),DATEADD(D,b.DISTANCEFROMTODAYSTART, @LOCALNOW),112)
AND a.WORKDAY = 1
AND b.ID=@LOCALTIMEPERIODCODE
GROUP BY a.FKBRANCHCODE

UPDATE #WORKINGDATE
SET ENDWORKDATE=(SELECT MAX(a.DAYINYEAR) AS a_DAYINYEAR
FROM CALENDAR a, TIME_PERIOD b
WHERE CONVERT(VARCHAR(12),a.DAYINYEAR,112) <= CONVERT(VARCHAR(12),DATEADD(D,b.DISTANCEFROMTODAYEND, @LOCALNOW),112)
AND a.WORKDAY = 1
AND b.ID=@LOCALTIMEPERIODCODE AND BRANCHCODE=a.FKBRANCHCODE)

End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources