Author |
Topic |
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2006-09-13 : 00:14:47
|
I finally got my UDF working, but now I'm faced with another problem. I can't seem to use it for anything![:IFirst, here is the function itself:CREATE FUNCTION dbo.fnDueDate( @DOP AS DATETIME, @INTERVAL AS TINYINT, @ThisDate DATETIME)RETURNS DATETIMEBEGIN DECLARE @Temp DATETIME IF @dop <= @ThisDate +(DATEADD(month,1,@dop)) SELECT @Temp = DATEADD(month, @Interval, @dop) ELSE SELECT @Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop) RETURN @TempENDIt is called using something like thisSELECT dbo.fnDueDate('20060315', 3, GETDATE())This function executes(correct term?) with three parameters, DOP, Interval, and ThisDate, which correspond to a column from a table (mytable.dop), an interval in months, and ThisDate (GETDATE())Now, I have determinied that the function works great, after several posts and a lot of help from our SQLteam gurus/yaks.However, I can't seem to use it in a stored procedure.Here is some sample data from myTableResidentsID Last First DOP1 Jones Bridget 09/08/20042 Longstocking Pippi 08/01/2006etc.What I would like to do is generate this:Last First DOP Next3month Next6MonthJones Bridget 09/08/2004 12/08/2006 3/08/2006Longstocking Pippi 08/01/2006 11/01/2006 5/01/2006Using the data from each row for DOPHelp. Please!Stephen |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-13 : 00:30:47
|
[code]select Last, First, DOP, dbo.fnDueDate(DOP, 3, getdate()), dbo.fnDueDate(DOP, 6, getdate())from mytable[/code] KH |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2006-09-13 : 01:06:49
|
quote: Originally posted by khtan
select Last, First, DOP, dbo.fnDueDate(DOP, 3, getdate()), dbo.fnDueDate(DOP, 6, getdate())from mytable KH
Gawd, could it be that easy? There I was, trying to do it with some crazy join...Thank you! I'll try it. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-13 : 01:23:19
|
That's the power of the scalar function I suggested to you.Peter LarssonHelsingborg, Sweden |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2006-09-13 : 14:51:57
|
I see that now. All of you guys rock! |
|
|
|
|
|