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
 Using a Function as a column

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![:I
First, here is the function itself:

CREATE FUNCTION dbo.fnDueDate
(
@DOP AS DATETIME,
@INTERVAL AS TINYINT,
@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
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 @Temp
END


It is called using something like this
SELECT 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 myTable
ResidentsID Last First DOP
1 Jones Bridget 09/08/2004
2 Longstocking Pippi 08/01/2006
etc.

What I would like to do is generate this:
Last First DOP Next3month Next6Month
Jones Bridget 09/08/2004 12/08/2006 3/08/2006
Longstocking Pippi 08/01/2006 11/01/2006 5/01/2006

Using the data from each row for DOP

Help. 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

Go to Top of Page

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.


Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-13 : 14:51:57
I see that now. All of you guys rock!
Go to Top of Page
   

- Advertisement -