Got to be a better way - 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)

Got to be a better way

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-21 : 15:41:26

GOAL:
I have a trigger date: '12-25-2002'
A days diff: 7
Use sat 0
Use sun 0

The days diff can be + or - the trigger date. I am trying to create a FX or PROC that gives me the trigger date +- the days diff. But, I also have some user params that say usesat, usesun.

So I need to calculate if the days diff includes any saturdays or sundays and count if necessary. Here is what I started with and stopped when I thought there could be a better way.

DECLARE @daydiff int,
@usesat int,
@usesun int,
@datetest smalldatetime, --this is selected either closing, order, user
@trigger smalldatetime,
@dateadd int,
@count int

DECLARE @dd TABLE (ddate smalldatetime,dd int)

SET @daydiff = 7
SET @usesat = 0
SET @usesun = 1
SET @datetest = '12-13-2002'
SET @trigger = DATEADD(dd, @daydiff, @datetest)

IF @daydiff < 0
SET @dateadd = -1
ELSE
SET @dateadd = 1

--*************************************************************
--RETURN ALL DAYS
--*************************************************************
WHILE DATEDIFF(dd, @datetest , @trigger) <> 0
BEGIN
SET @datetest = DATEADD(dd, @dateadd, @datetest)
INSERT INTO @dd VALUES ( @datetest, DATEPART(dw,@datetest) )

END

--*************************************************************
--TENATIVE TRIGGER DATE ** MUST CHECK FOR WEEKEND COUNTS
--*************************************************************
--DONT COUNT SATURDAYS
IF @usesat = 0
SET @trigger = DATEADD(dd,(SELECT COUNT(dd) FROM @dd WHERE dd = 7)5, @trigger )

--DONT COUNT SUNDAYS
IF @usesat = 0
SET @trigger = DATEADD(dd,(SELECT COUNT(dd) FROM @dd WHERE dd = 1), @trigger )

SELECT @trigger, * FROM @dd

slow down to move faster...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-23 : 09:00:43

This is a great little puzzle to try to solve ... I have some ideas in development, but it is a tricky one. I do think there is a quicker way than what you are doing; I think a formula can be written without using tables; I'll try to create a User-Defined function.

I'll try to post something later on in the day.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-23 : 11:58:19

OK -- I think this works. It should work fine for both negative and positive values. Very verbose so you can see what I am doing. The basic idea:

1) find out what our "new" week length is. if we are omitting sat's and sun's, our new week length is 5.

2) Find out how many weeks forward or backward we need to go, based on that week length. Start at that date. So if we are on a thursday, we jump to another thursday and start from there.

3) Figure out the "left over" days to add or subtract, make sure we don't land on or cross over an illegal day, and that is our result.

What a great brainteaser! Questions like this one are the reason why I love visiting this site. I hope this works, I tested it pretty thoroughly but you never know.

- - - - - - - - - - - - - - -

/*parameters*/
declare @useSat int;
declare @useSun int;
declare @StartDate datetime;
declare @DaysToAdd int;
declare @Answer datetime; /* the end result */

/*Local variables*/
declare @WeekLength int;
declare @NumWeeks int;
declare @LeftOver int;
declare @WD int;

/* Some test data */

Set @useSat = 0;
Set @useSun = 0;
Set @StartDate = '12/13/2002';
set @DaysToAdd = -10;

/* First, we need to determine the 'new' week length, based on whether or not we
are using saturdays and sundays*/
set @WeekLength = 5 + @useSat + @useSun;

/* with the new week length, we figure out how many weeks of this new length we need to
add (or subtract): */
set @NumWeeks = convert(int,@DaysToAdd / @WeekLength);

/* Next, we get the weekday of the starting date. We assume that
you will pass in a legal starting date; that is, you will not pass in
a Sunday if useSun is = 0.

Add error checking or some logic to handle the above if that might be a possibility.
*/

set @WD = datepart(dw,@StartDate);

/*Note that at this point, @StartDate + (@NumWeeks) * 7 has the same weekday as
the starting date; that will be our starting point. */

/*Next, we need the left over amount of how many days to add:*/
set @LeftOver = @DaysToAdd % @WeekLength

/*We are almost there. Now we make final adjustments so that we don't cross over
or land on an 'illegal' date. if we do, we add 1. The order of the following is very
important to first handle saturdays, and then sundays. */

/*the first two "ifs" handle going forward (a positive @Leftover amount), next two handle going backwards:*/

/*note that all of the below are implied false if we are going backwards, so we don't need to check that: */
IF @Leftover + @WD > 6
set @Leftover = @LeftOver + (1-@useSat);

IF @LeftOver + @WD > 7
set @Leftover = @LeftOver + (1-@useSun);

/*backwards (negative @Leftover). Note that we handle sunday first in this case, and then saturday (opposite of above).

All of the below are implied "FALSE" automatically if we are going forward, so we don't need to check that:*/

If @LeftOver + @WD <2
set @LeftOver = @LeftOver + (@useSun - 1);

IF @LeftOver + @WD <1
set @LeftOver = @LeftOver + (@useSat - 1);

/* And now, we have our final answer:*/

set @Answer = DateAdd(d,@NumWeeks * 7 + @LeftOver,@StartDate);

print @answer ;
print datepart(dw,@Answer);

- Jeff

Edited by - jsmith8858 on 12/23/2002 12:18:14

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-26 : 06:42:52

OK, I see what your doing. I am going to test this today. Thanks for the Christmas present.

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources