loops - 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

Transact-SQL (2000)

loops

Author

Topic

nbs
Starting Member

22 Posts

Posted - 2007-04-12 : 12:43:26

Hi all,
I am trying to execute this and it takes for ever to run.. I cant figure out why its taking such a long time.

what i am trying to achieve is if the input date is a weekday give me a estimated finish date adding 1 day to it with the time intact.
if the input date falls on a weekend or the Hol_IND1 or hol_IND2, give me a new input date to start with (the next business day) and then return an estimated finish date based on the new input date (adding 1 and resetting the time to 8AM)

example:

2005-05-27 is a friday; 2005-05-30 is a holiday; Taking these into consideration

Input_date Effective_input_date Estimated_Finish_date
---------- -------------------- ---------------------

2005-05-27 3pm; 2005-05-27; 2005-05-31 3pm;

2005-05-28 3pm; 2005-05-31; 2005-06-01 8am;

2005-05-29 3pm; 2005-05-31; 2005-06-01 8am;

2005-05-30 3pm; 2005-05-31; 2005-06-01 8am;

2005-05-31 3pm; 2005-05-31; 2005-06-01 3pm;

*************************************************************
Declare @inputDate smalldatetime
Declare @intHoliday1 Varchar(2)
Declare @intHoliday2 Varchar(2)
Declare @inputHoliday1 Varchar(2)
Declare @inputHoliday2 Varchar(2)
Declare @TempDate SmallDateTime
Declare @TempDate2 Smalldatetime
Declare @Nextworkingdate SmallDateTime
Declare @NewTempDate smalldatetime
Declare @intNewHoliday1 Varchar(2)
Declare @intNewHoliday2 Varchar(2)
Declare @finishdt smalldatetime

Select @inputDate = '2005-05-27 15:23:10'

Select @inputHoliday1 = Hol_IND1, @inputHoliday2 = Hol_IND2
From Bus_Eff_DATE
Where Translate_DTE = Convert(Varchar(10), @inputDate, 101)

If (@inputHoliday1 = 'Y') Or (@inputHoliday2 = 'Y') Or (DatePart(dw, @inputDate) In (1, 7))
Begin
L1: Select @TempDate = Convert(Varchar(10), @inputDate + 1, 101)

Select @intHoliday1 = Hol_IND1, @intHoliday2 = Hol_IND2
From Bus_Eff_DATE
Where Translate_DTE = Convert(Varchar(10), @TempDate, 101)

If (@intHoliday1 = 'Y') Or (@intHoliday2 = 'Y') Or (DatePart(dw, @TempDate) In (1, 7))
GOTO L1
Else
L2: Select @NewTempDate = Convert(Varchar(10), @TempDate + 1, 101)
Select @intNewHoliday1 = Hol_IND1, @intNewHoliday2 = Hol_IND2
From Bus_Eff_DATE
Where Translate_DTE = Convert(Varchar(10), @NewTempDate, 101)

If (@intNewHoliday1 = 'Y') Or (@intNewHoliday2 = 'Y') Or (DatePart(dw, @NewTempDate) In (1, 7))
GOTO L2
Else
Select @Nextworkingdate = Convert(Char(10), @NewTempDate, 101) + ' 08:00:00'
End

else
Begin
L3: Select @TempDate2 = Convert(Varchar(10), @inputDate + 1, 101)

Select @intHoliday1 = Hol_IND1, @intHoliday2 = Hol_IND2
From Bus_Eff_DATE
Where Translate_DTE = Convert(Varchar(10), @TempDate2, 101)

If (@intHoliday1 = 'Y') Or (@intHoliday2 = 'Y') Or (DatePart(dw, @TempDate2) In (1, 7))
GOTO L3
else
Select @Nextworkingdate = Convert(Char(10), @TempDate2, 101) + ' ' + Convert(Char(8), @inputDate, 108)
End

select @Nextworkingdate

********************************************************************

can anyone guide me where i am going wrong. and if this is not really the best way to do this?

Thanks much!

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-12 : 14:49:59

Well...first you need to start thinking it terms of set based models...not procedural ones like loops and cursors

Next read this

http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx

You will benefit from using another table the holds weekends and folidays...it should then be a snap

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources