May 14, 2004 at 8:44 pm
Hello All!
Just a newbie in SQL
Can you pls help. I have 2 tables (this tables have a dummy data so that you can all get an idea about what i need)
Table1 (It is all my SA holidays I just named few)
Holiday
16 Dec 2003
25 Dec 2003
26 Dec 2003
1 Jan 2004
Table2 (Transaction Date)
ID TrDate FYI
1 9 Dec 2003 Tue
2 10 Dec 2003 Wed
3 16 Dec 2003 Tue
4 17 Dec 2003 Wed
5 18 Dec 2003 Thu
6 19 Dec 2003 Fri
I actually want to create a user-defined- function (the reason for this is i will like to call it now and again in my statements).
This function must do the following as one user-defined-function
1. select trdate from table2 and this table2.trdate must not be in table1 (to avoid holidays)
2. It must add 7 days to trdate, if day is (Monday to Friday) then add 6 days instead of 7 if trdate falls on Saturday and 5 if trdate falls on Sunday. So basically if trdate = 15 May 2004 which is a Saturday if I add 6 days my newtrdate will be 15 + 7 = 21 May 2004
3. I need to check the above against table1 (that after adding 7days for Monday to Friday or 5 to Sunday or 6 Saturday, my newtrdate must not) be a holiday. If it is a holiday, then I need to add 1 to my newtrdate
So this user-defined function must return to me
newtrdate that is not a holiday and that does not fall on a Sunday or Saturday
Sho, i have been struggling with this for 3days
May 14, 2004 at 9:59 pm
I have hard time understanding how your table1 fits into the UDF, seems to me that UDF should be run against table1, not reference it internally? Probably more efficient way to skip holidays, this is crude.
create table Holidays(Holiday datetime)
insert Holidays (Holiday) values('16 Dec 2003')
insert Holidays (Holiday) values('25 Dec 2003')
insert Holidays (Holiday) values('26 Dec 2003')
insert Holidays (Holiday) values('01 Jan 2004')
go
create table Transactions(ID int identity(1,1), TrDate datetime)
insert Transactions (TrDate) values('09 Dec 2003')
insert Transactions (TrDate) values('10 Dec 2003')
insert Transactions (TrDate) values('16 Dec 2003')
insert Transactions (TrDate) values('17 Dec 2003')
insert Transactions (TrDate) values('18 Dec 2003')
insert Transactions (TrDate) values('19 Dec 2003')
go
create function fn_NextTrDate(@TrDate datetime)
returns datetime
as
begin
--advance to next date (not using datepart because of @@datefirst concerns)
set @TrDate = dateadd(dd, case datename(dw, @TrDate)
when 'Sunday' then 5
when 'Saturday' then 6
else 7
end, @TrDate)
--skip past known holidays
while exists(select 1 from Holidays where Holiday = @TrDate)
set @TrDate = dateadd(dd, 1, @TrDate)
return @TrDate
end
go
select TrDate, dbo.fn_NextTrDate(TrDate)
from Transactions
May 16, 2004 at 12:46 pm
Hello Mike
Thanks a lot for your help but I have a small is problem
My data type is a problem, TrDate is saved in the format 2003-04-24 11:27:56.000 where as my Holiday is saved as 2003-04-24 00:00:00.000
I tried to create your function using covert(varchar,trdate),105) but i get his error when i run it The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
May 16, 2004 at 12:55 pm
I think it is probably a question of the dates that you consider a holiday that needs to be accounted for.
May 16, 2004 at 1:09 pm
Hi Antares686
I thought you everything which Mike did is working fine, the only problem is the dates. I need help on how to create a function that will return date in the following format 2003-04-24 00:00:00.000 since my Trdate is having 2003-04-24 11:26:57.000
May 16, 2004 at 7:08 pm
I think you just need to add convert in two places (I use style 120 mostly, other choices ok):
--advance to next date (not using datepart because of @@datefirst concerns)
set @TrDate = convert(char(10), dateadd(...), 120)
and
--skip past known holidays
while exists(select 1 from Holidays where convert(char(10), Holiday, 120) = @TrDate)
May 17, 2004 at 4:41 am
Sorry about this Mike but this doesn't work in the following cases
If I use TrDate = '2003-04-25' and I add 7 which is the 2 May 2003. 2 May 2003 was my Holiday, so this add 1 get 3 May 2003 as my results which is correct, but 3 May was a Saturday, so it must be skipped so that I can get my settlement date the following Monday which is 5 May 2003. I hope you understand. I am just fresh in this
May 17, 2004 at 5:12 am
Ok so you need to drop the time to 0. Try this on your date.
dateadd(d,datediff(d,0,your_date),0)
and you should end up with the resulting value you need for the situation.
Then to make simpler for you right after the being in the UDF add the following line.
-- This line strips the time value out.
SET @TrDate = dateadd(d,datediff(d,0,@Tr_Date),0)
May 17, 2004 at 5:45 am
I am not sure if i understand you, you meena my query must look like this correct me if i am wrong because this is not working
create function fn_NextTrDate(@TrDate datetime)
returns datetime
as
begin
--advance to next date (not using datepart because of @@datefirst concerns)
set @TrDate = dateadd(dd, case datename(dw, @TrDate)
when 'Sunday' then 5
when 'Saturday' then 6
else 7
end, @TrDate)
--skip past known holidays
while exists(select 1 from Holidays where Holiday = @TrDate)
-- set @TrDate = dateadd(dd, 1, @TrDate)
SET @TrDate = dateadd(d,datediff(d,0,@Tr_Date),0)
return @TrDate
end
go
May 17, 2004 at 6:02 am
I am thinking like this
create function fn_NextTrDate(@TrDate datetime)
returns datetime
as
begin
-- This line strips the time value out.
SET @TrDate = dateadd(d,datediff(d,0,@Tr_Date),0)
--advance to next date (not using datepart because of @@datefirst concerns)
set @TrDate = dateadd(dd, case datename(dw, @TrDate)
when 'Sunday' then 5
when 'Saturday' then 6
else 7
end, @TrDate)
--skip past known holidays
while exists(select 1 from Holidays where Holiday = @TrDate)
set @TrDate = dateadd(dd, 1, @TrDate)
return @TrDate
end
go
However are you still needing to keep the time portion on the output?
May 17, 2004 at 6:19 am
This is not working because your code gives me the date as 2003-05-03 and this is a Saturday, I am expecting the results to be Monday since I am only dealing from Monday to Friday only
May 17, 2004 at 11:10 am
This has added logic to validate after a friday holiday to move to monday.
create function fn_NextTrDate(@TrDate datetime)
returns datetime
as
begin
-- knock off the time part.
SET @TrDate = dateadd(d,datediff(d,0,@TrDate),0)
-- advance to next date (not using datepart because of @@datefirst concerns)
set @TrDate = dateadd(dd, case datename(dw, @TrDate)
when 'Sunday' then 5
when 'Saturday' then 6
else 7
end, @TrDate)
-- skip past known holidays and slid forward to monday if sat or sun.
while exists(select 1 from Holidays where Holiday = @TrDate Or datename(dw,@TrDate) In ('Sunday','Saturday'))
set @TrDate = dateadd(dd, 1, @TrDate)
return @TrDate
end
go
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply