Intensively used function in view needs a minimum and maximum from a table

I have a problem (who not?) with a function which i'm using in a view.
This function is a function which calculates a integer value of a
date. For example: '12/31/2004 00:00:00" becomes 20041231. This is
very handy in a datawarehouse and performes superfast. But here is my
problem.

My calendar table is limited by a couple of years. What happens is
that sometimes a value is loaded which is not in the range of the
Calendardate. What we want to do is when a date is loaded is that this
function insert a minimum date when date < minimum date and a maximum
date when date > maximum date.

Yes i know you're thinking : This is datamanipulation and yes this is
true. But now we loose information in our cubes and reports by inner
joining. So if we can use a minimum and a maximum than a user would
say: "This is strange, a lot of values on 1980/1/1!" instead of "I
think that i have not all the data!"

Greetz

HennieHi

If you LEFT or RIGHT JOIN to the calendar table you will get a NULL value
for the column, you can then is CASE to determine the value

CREATE FUNCTION ConvertDate (@.datevalue datetime)
RETURNS INT
AS
BEGIN
DECLARE @.dateint INT
SELECT @.dateint = CAST( CASE WHEN A.Date < '20030101' THEN '19800101'
WHEN A.Date > '20051231' THEN '99991231'
ELSE CONVERT(CHAR(4),C.[Year]) + RIGHT('0'+
CONVERT(VARCHAR(2),C.[Month]),2) + RIGHT('0'+ CONVERT(VARCHAR(2),C.[Day]),2)
END AS INT )
FROM ( SELECT @.datevalue AS [Date] ) A
LEFT JOIN CALENDAR C ON C.[Date] = A.[Date]
RETURN @.dateint
END
GO

John

"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0412300238.7dee0f85@.posting.google.c om...
>I have a problem (who not?) with a function which i'm using in a view.
> This function is a function which calculates a integer value of a
> date. For example: '12/31/2004 00:00:00" becomes 20041231. This is
> very handy in a datawarehouse and performes superfast. But here is my
> problem.
> My calendar table is limited by a couple of years. What happens is
> that sometimes a value is loaded which is not in the range of the
> Calendardate. What we want to do is when a date is loaded is that this
> function insert a minimum date when date < minimum date and a maximum
> date when date > maximum date.
> Yes i know you're thinking : This is datamanipulation and yes this is
> true. But now we loose information in our cubes and reports by inner
> joining. So if we can use a minimum and a maximum than a user would
> say: "This is strange, a lot of values on 1980/1/1!" instead of "I
> think that i have not all the data!"
> Greetz
> Hennie|||On 30 Dec 2004 02:38:51 -0800, Hennie de Nooijer wrote:

>I have a problem (who not?) with a function which i'm using in a view.
>This function is a function which calculates a integer value of a
>date. For example: '12/31/2004 00:00:00" becomes 20041231. This is
>very handy in a datawarehouse and performes superfast. But here is my
>problem.
(snip)

Hi Hennie,

Is this conversion all that your function does? If so, you might want to
try the following alternative (using CURRENT_TIMESTAMP as example; replace
it with your date column / parameter):

SELECT CAST(CONVERT(varchar, CURRENT_TIMESTAMP, 112) AS int)

You could put this in the UDF (probably at least as fast as your current
Calenmdar-table based function), or use it inline as a replacement to the
function call (probably even faster).

It should work for all dates from Jan 1st 1753 through Dec 31st 9999.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)