SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using a Function as a column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stephenbaer
Yak Posting Veteran

USA
71 Posts

Posted - 09/13/2006 :  00:14:47  Show Profile  Reply with Quote
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)

Singapore
17655 Posts

Posted - 09/13/2006 :  00:30:47  Show Profile  Reply with Quote
select	Last, First, DOP, dbo.fnDueDate(DOP, 3, getdate()), dbo.fnDueDate(DOP, 6, getdate())
from	mytable



KH

Go to Top of Page

stephenbaer
Yak Posting Veteran

USA
71 Posts

Posted - 09/13/2006 :  01:06:49  Show Profile  Reply with Quote
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

Sweden
30276 Posts

Posted - 09/13/2006 :  01:23:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
71 Posts

Posted - 09/13/2006 :  14:51:57  Show Profile  Reply with Quote
I see that now. All of you guys rock!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000