Next business Day - Query Posted

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)

Next business Day - Query Posted

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-28 : 15:59:07

create table holidayTL (holdate datetime)

insert into holidayTL values('06/29/2007')
insert into holidayTL values('06/30/2007')
insert into holidayTL values('07/01/2007')

************************QUERY*************************************

declare @currbussday datetime
declare @nextbussday datetime

IF (dateadd(day,(0),datediff(day,0,getdate()))) NOT IN (select holdate from holidayTL)
BEGIN
set @currbussday = (dateadd(day,(0),datediff(day,0,getdate())))
PRINT @currbussday

END

ELSE

IF
(dateadd(day,(1),datediff(day,0,getdate()))) NOT IN (select holdate from holidayTL)
BEGIN
set @nextbussday = (dateadd(day,(1),datediff(day,0,getdate())))
PRINT @nextbussday
END

ELSE

IF
(dateadd(day,(1),datediff(day,0,getdate()))) IN (select holdate from holidayTL) And
(dateadd(day,(2),datediff(day,0,getdate()))) NOT IN (select holdate from holidayTL) And
(dateadd(day,(3),datediff(day,0,getdate()))) NOT IN (select holdate from holidayTL) And
(dateadd(day,(4),datediff(day,0,getdate()))) NOT IN (select holdate from holidayTL)

BEGIN
set @nextbussday = (dateadd(day,(2),datediff(day,0,getdate())))
PRINT @nextbussday
END

ELSE

IF
(dateadd(day,(1),datediff(day,0,getdate()))) IN (select holdate from holidayTL) AND
(dateadd(day,(2),datediff(day,0,getdate()))) IN (select holdate from holidayTL) And
(dateadd(day,(3),datediff(day,0,getdate()))) NOT IN (select holdate from holidayTL) And
(dateadd(day,(4),datediff(day,0,getdate()))) NOT IN (select holdate from holidayTL)
BEGIN
set @nextbussday = (dateadd(day,(3),datediff(day,0,getdate())))
PRINT @nextbussday
END

ELSE

IF
(dateadd(day,(1),datediff(day,0,getdate()))) IN (select holdate from holidayTL) And
(dateadd(day,(2),datediff(day,0,getdate()))) IN (select holdate from holidayTL) And
(dateadd(day,(3),datediff(day,0,getdate()))) IN (select holdate from holidayTL) And
(dateadd(day,(4),datediff(day,0,getdate()))) NOT IN (select holdate from holidayTL)
BEGIN
set @nextbussday = (dateadd(day,(4),datediff(day,0,getdate())))
PRINT @nextbussday
END

ELSE

IF
(dateadd(day,(1),datediff(day,0,getdate()))) IN (select holdate from holidayTL) And
(dateadd(day,(2),datediff(day,0,getdate()))) IN (select holdate from holidayTL) And
(dateadd(day,(3),datediff(day,0,getdate()))) IN (select holdate from holidayTL) And
(dateadd(day,(4),datediff(day,0,getdate()))) IN (select holdate from holidayTL)
BEGIN
set @nextbussday = (dateadd(day,(5),datediff(day,0,getdate())))
PRINT @nextbussday
END

********************************RESULT******************************
RESULT SHOULD BE
Jun 28 2007 12:00AM
Jul 02 2007 12:00AM

But I get only

Jun 28 2007 12:00AM

Also when you do this insert:

insert into holidayTL values('07/02/2007')

result should be

Jun 28 2007 12:00AM
Jul 05 2007 12:00AM

(There are not more than 4 holidays in a row in US. So I am not going to have any more cases)*******************************************************************

Ashley Rhodes

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-06-28 : 20:32:04

i think i have seen solution for this in book "The Guru's Guide to Transact-SQL" or in The Guru's Guide to SQL Server Stored Procedures, XML, and HTML . see if u can get it.
cheers

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources