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

[Hennie de Nooijer]

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 datamanipulatio n 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

[John Bell]

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

Hi

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@hot mail.com> wrote in message
news:191115aa.0 412300238.7dee0 f85@posting.goo gle.com...[color=blue]
>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 datamanipulatio n 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[/color]

[Hugo Kornelis]

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

On 30 Dec 2004 02:38:51 -0800, Hennie de Nooijer wrote:
[color=blue]
>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.[/color]
(snip)

Hi Hennie,

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

SELECT CAST(CONVERT(va rchar, CURRENT_TIMESTA MP, 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)