While statement - 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

General SQL Server Forums

New to SQL Server Programming

While statement

Author

Topic

debora
Starting Member

7 Posts

Posted - 2008-11-25 : 04:45:03

Hi,
i am using the while statment to loop the holidays, but its giving syntax problem, can any one help me how to work with while statement below is the my code......

ALTER function [dbo].[GetWorkingDays_NoWeekend_NoHoliday](
@startDate datetime

)
RETURNS datetime
AS
BEGIN

DECLARE @dStartDay datetime

If DATENAME(WEEKDAY, @startDate) = 'Monday'
set @dStartday = DateAdd(day,-3,@startDate)
else If DATENAME(WEEKDAY, @startDate) = 'Sunday'
set @dStartday = DateAdd(day,-2,@startDate)
else
begin
set @dStartday = DateAdd(day,-1,@startDate)
if DATENAME(WEEKDAY, @dStartday) = 'Monday'
set @dStartday = DateAdd(day,-3,@dStartday)
else If DATENAME(WEEKDAY, @dStartday) = 'Sunday'
set @dStartday = DateAdd(day,-2,@dStartday)
end
Declare @COunt integer
select @count = COUNT(*) FROM holiday where caldate1 in ( @dstartday )
while @count = 1
do
set @dStartday = DateAdd(day,-1,@dStartday)
select @COunt = COUNT(*) FROM holiday where caldate1 in( @dStartDay )
end while
RETURN @dStartday

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-25 : 04:54:20

quote:
Originally posted by debora

Hi,
i am using the while statment to loop the holidays, but its giving syntax problem, can any one help me how to work with while statement below is the my code......

ALTER function [dbo].[GetWorkingDays_NoWeekend_NoHoliday](
@startDate datetime

)
RETURNS datetime
AS
BEGIN

DECLARE @dStartDay datetime

If DATENAME(WEEKDAY, @startDate) = 'Monday'
set @dStartday = DateAdd(day,-3,@startDate)
else If DATENAME(WEEKDAY, @startDate) = 'Sunday'
set @dStartday = DateAdd(day,-2,@startDate)
else
begin
set @dStartday = DateAdd(day,-1,@startDate)
if DATENAME(WEEKDAY, @dStartday) = 'Monday'
set @dStartday = DateAdd(day,-3,@dStartday)
else If DATENAME(WEEKDAY, @dStartday) = 'Sunday'
set @dStartday = DateAdd(day,-2,@dStartday)
end
Declare @COunt integer
select @count = COUNT(*) FROM holiday where caldate1 in ( @dstartday )
while @count = 1
do
set @dStartday = DateAdd(day,-1,@dStartday)
select @COunt = COUNT(*) FROM holiday where caldate1 in( @dStartDay )
end while
RETURN @dStartday

try this

ALTER function [dbo].[GetWorkingDays_NoWeekend_NoHoliday](
@startDate datetime

)
RETURNS datetime
AS
BEGIN

DECLARE @dStartDay datetime

If DATENAME(WEEKDAY, @startDate) = 'Monday'
set @dStartday = DateAdd(day,-3,@startDate)
else If DATENAME(WEEKDAY, @startDate) = 'Sunday'
set @dStartday = DateAdd(day,-2,@startDate)
else
begin
set @dStartday = DateAdd(day,-1,@startDate)
if DATENAME(WEEKDAY, @dStartday) = 'Monday'
set @dStartday = DateAdd(day,-3,@dStartday)
else If DATENAME(WEEKDAY, @dStartday) = 'Sunday'
set @dStartday = DateAdd(day,-2,@dStartday)
end
Declare @COunt integer
select @count = COUNT(*) FROM holiday where caldate1 in ( @dstartday )
while @count = 1
begin
set @dStartday = DateAdd(day,-1,@dStartday)
select @COunt = COUNT(*) FROM holiday where caldate1 in( @dStartDay )
end
end
RETURN @dStartday

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 04:55:33

quote:
Originally posted by debora

Hi,
i am using the while statment to loop the holidays, but its giving syntax problem, can any one help me how to work with while statement below is the my code......

ALTER function [dbo].[GetWorkingDays_NoWeekend_NoHoliday](
@startDate datetime

)
RETURNS datetime
AS
BEGIN

DECLARE @dStartDay datetime

If DATENAME(WEEKDAY, @startDate) = 'Monday'
set @dStartday = DateAdd(day,-3,@startDate)
else If DATENAME(WEEKDAY, @startDate) = 'Sunday'
set @dStartday = DateAdd(day,-2,@startDate)
else
begin
set @dStartday = DateAdd(day,-1,@startDate)
if DATENAME(WEEKDAY, @dStartday) = 'Monday'
set @dStartday = DateAdd(day,-3,@dStartday)
else If DATENAME(WEEKDAY, @dStartday) = 'Sunday'
set @dStartday = DateAdd(day,-2,@dStartday)
end
Declare @COunt integer
select @count = COUNT(*) FROM holiday where caldate1 in ( @dstartday )
while @count = 1
begin
do
set @dStartday = DateAdd(day,-1,@dStartday)
select @COunt = COUNT(*) FROM holiday where caldate1 in( @dStartDay )
end
~~while~~ RETURN @dStartday

debora
Starting Member

7 Posts

Posted - 2008-11-25 : 05:00:56

Hi,
i have used below code still it is giving eror,
Declare @COunt integer
select @count = COUNT(*) FROM holiday where caldate1 in ( @dstartday )
while @count = 1
begin
set @dStartday = DateAdd(day,-1,@dStartday)
select @COunt = COUNT(*) FROM holiday where caldate1 in( @dStartDay )
end
end
RETURN @dStartday

can any body please kindly help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:02:51

quote:
Originally posted by debora

Hi,
i have used below code still it is giving eror,
Declare @COunt integer
select @count = COUNT(*) FROM holiday where caldate1 in ( @dstartday )
while @count = 1
begin
set @dStartday = DateAdd(day,-1,@dStartday)
select @COunt = COUNT(*) FROM holiday where caldate1 in( @dStartDay )
end
~~end~~
RETURN @dStartday

can any body please kindly help me

you need only a single end

debora
Starting Member

7 Posts

Posted - 2008-11-25 : 05:06:46

Hi,

I have used single end, its still giving error so ihave used

Declare @COunt integer
select @count = COUNT(*) FROM holiday where caldate1 in ( @dstartday )
while @count = 1
begin
set @dStartday = DateAdd(day,-1,@dStartday)
select @COunt = COUNT(*) FROM holiday where caldate1 in( @dStartDay )
end
RETURN @dStartday
end

is it correct or not please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:11:44

[code]Declare @COunt integer
select @count = COUNT(*) FROM holiday where caldate1 = @dstartday
while @count = 1
begin
set @dStartday = DateAdd(day,-1,@dStartday)
select @COunt = COUNT(*) FROM holiday where caldate1 = @dstartday end
RETURN @dStartday
end[/code]
try like above.

b/w i didnt understand what loop is for here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources