Need help with User Defined Function

I am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.

To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.

The access function is as follows:

Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long

If IFSDate < (Date + 14) Then
BuyerDeltas = IFSDate - 3 - PODate
ElseIf IFSDate < (Date + 29) Then
BuyerDeltas = IFSDate - 7 - PODate
ElseIf IFSDate > (Date + 28) Then
BuyerDeltas = IFSDate - 10 - PODate
Else
MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
End If

End Function

The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.

I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.

Any direction would be greatly appreciated!

I still cannot get this proceedure set up so it will work. I have been playing with it all day and have gotten to the following point:

CREATE FUNCTION BuyerDeltas
(@.IFSDate DATE, @.PODate DATE)
RETURNS decimal (5,0)
AS
BEGIN
DECLARE @.BaseDate DATE
DECLARE @.IFS decimal (5,0)
DECLARE @.PO decimal (5,0)
DECLARE @.ThisDay decimal (5,0)
SET @.BaseDate = CONVERT(DATETIME, '2000-12-31 00:00:00', 102)
-- Used so I can use DateDiff to get numbers for days to use in equations
-- It is just an arbitrary date that will be before any dates in the system
SET @.IFS = DateDiff(day, @.BaseDate, @.IFSDate)
SET @.PO = DateDiff(day, @.BaseDate, @.PODate)
SET @.ThisDay = DateDiff(day, @.BaseDate, GetDate())
Where @.IFS < @.ThisDay + 14
RETURN @.IFS - 3 - @.PO
Where (@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29)
RETURN @.IFS - 7 - @.PO
Where @.IFS < @.ThisDay + 28
RETURN @.IFS - 10 - @.PO
End

It gives me the following errors:

Server: Msg 443, Level 16, State 1, Procedure BuyerDeltas, Line 15
Invalid use of 'getdate' within a function.
Server: Msg 156, Level 15, State 1, Procedure BuyerDeltas, Line 18
Incorrect syntax near the keyword 'Where'.
Server: Msg 156, Level 15, State 1, Procedure BuyerDeltas, Line 20
Incorrect syntax near the keyword 'Where'.

I am really trying to understand this and get it to work right. If someone could just give me an example of a similarly structured function, I am sure I should be able to alter this one so that it works.

Calling the function from the view, actually, I guess I should just use the function in the INSERT code for the DTS package, might still drive me nuts when I try and use this, but I do really want to get this done right.

Thanks again!

|||

Hopefully this will help. We do not have a DATE datatype in SQL SERVER. We have a DATETIME datatype.

CREATE FUNCTION BuyerDeltas
(@.IFSDate DATETIME, @.PODate DATETIME)
RETURNS decimal (5,0)
AS
BEGIN
DECLARE @.BaseDate DATETIME
DECLARE @.IFS decimal (5,0)
DECLARE @.PO decimal (5,0)
DECLARE @.ThisDay decimal (5,0)
SET @.BaseDate = CONVERT(DATETIME, '2000-12-31 00:00:00', 102)

-- Used so I can use DateDiff to get numbers for days to use in equations
-- It is just an arbitrary date that will be before any dates in the system
SET @.IFS = DateDiff(day, @.BaseDate, @.IFSDate)
SET @.PO = DateDiff(day, @.BaseDate, @.PODate)
SET @.ThisDay = DateDiff(day, @.BaseDate, GetDate())

if (@.IFS < @.ThisDay + 14)
RETURN @.IFS - 3 - @.PO
else if ((@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29))
RETURN @.IFS - 7 - @.PO
else if (@.IFS < @.ThisDay + 28)
RETURN @.IFS - 10 - @.PO

RETURN -1 --trying to indicate a failure

--Where @.IFS < @.ThisDay + 14
-- RETURN @.IFS - 3 - @.PO
--Where (@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29)
--RETURN @.IFS - 7 - @.PO
--Where @.IFS < @.ThisDay + 28
-- RETURN @.IFS - 10 - @.PO
end
GO

|||

Thanks Asvin!

That is getting it very close, the only error I now get is:

Server: Msg 443, Level 16, State 1, Procedure BuyerDeltas, Line 16
Invalid use of 'getdate' within a function.

Not sure how else I can get today's date in this thing, but I keep trying. I am open to just getting the date as a variable, but when I tried that, I still go the error.

Thanks again!

|||

In 2000 you can't use the getdate function in a function (say that a few times fast) but you can in 2005.

The workaround is to add a parameter and pass in the date and time like:

create function functionName
(
@.parm1 datatype,
@.getdate datetime

Then add that to the call.

Another trick which I have never actually used is to create a view:

create view dateView
as
select getdate() as getdate

Then just add this to your queries that need the current date (using a cross join works nicely):

create view dateView
as
select getdate() as getdate
GO

select * from dateView
GO

Returns:

getdate
--
2006-04-07 23:58:30.217

select dateview.getdate, sysobjects.name
from sysobjects
cross join dateview

getdate name
--
2006-04-07 23:58:30.247 sysrowsetcolumns
2006-04-07 23:58:30.247 sysrowsets
2006-04-07 23:58:30.247 sysallocunits
.......

And so on.

|||

Thanks Louis,

I didn't actually use the ideas you put forth, but you did get me thinking. I realized that I could put GetDate() in the view that was used to make the table for the cube. I then do some alterations on the table based on what data is missing in some fields of the table. There are times that there are no dates in the fields I need to calculate the BuyerDeltas that this whole exercise is designed to calculate so I need to put dates in these fields before I can call the function anyway. After those updates, I can call the function and put the value I wanted calculated in the appropriate field of the table. There is probably a simpler and faster way to do it, but this works and the boss is breathing down my neck to get this finished.

Thanks again to all that helped, using a little bit from all of the responses has let me finally beat this thing.