function

hi..
i have one holiday master where all holiday are defined..now in below function i want to add payoutday to my trade_day.and this date will insert into my funding table.but before that i have to check after adding payoutday to the trade_day, this date not fall in holiday list.if that day will holidaty than i have to take next working day.and if again next day also holiday than i want to skip that day also and take next working day...and again check for holiday so on.
how to do this?

[code]

ALTER FUNCTION dbo.NewPayOutDate(@.id numeric(9))
Returns datetime
as
Begin

DECLARE @.CALDATE int
DECLARE @.ACTDATE DATETIME
Declare @.adddate DATETIME
declare @.moreCALDATE int
Begin
SELECT @.CALDATE=payoutday ,@.ACTDATE=trade_date
FROM pruamc.Tbl_GroupMst GM
INNER JOIN pruamc.Tbl_Redemption_UploadDetails (NOLOCK)
ON
GM.Group_Name = pruamc.Tbl_Redemption_UploadDetails.scheme_group
WHERE pruamc.Tbl_Redemption_UploadDetails.RedemptionUploadMaster_Id=@.id
SET @.adddate = dateadd(dd,@.CALDATE,@.ACTDATE)
SELECT @.moreCALDATE=count(*) from pruamc.Tbl_holidaymst where holiday_date between @.ACTDATE and @.adddate
END
set @.adddate = dateadd(dd,@.moreCALDATE,@.adddate)
RETURN @.adddate
END

[/code]

Moving to the Transact-SQL forum.|||

i m sorry ...thanx for

Moving to the Transact-SQL forum.

but any solution about my problem?

thanx a lot

|||

The following function will be help you to get the next working day...

Code Snippet

Create Function GetWorkingDay(@.Date as DateTime)

Returns DateTime

as

Begin
Declare @.Holiday as DateTime;
Select @.Holiday = holiday_date From Tbl_holidaymst Where holiday_date = @.Date;

If @.Holiday is Null
return @.Date;

return dbo.GetWorkingDay(DateAdd(DD, 1, @.Date));

End

Output:

if 1/1/2007 & 1/2/2007 is holiday

Select dbo.GetWorkingDay('1/1/2007') => 1/3/2007

Select dbo.GetWorkingDay('12/31/2006') => 12/31/2007

|||great thanx a lot..manid