Intensively Used Function In View Needs A Minimum And Maximum From A Table PartII

Because of an error in google or underlying site i can reply on my own
issue. Therefore i copied the former entered message in this message.
-------------------------------------REPY----------------------------------
Hi Maybe i wasn't clear. I want to dynamically check whether what the
lowest date and the highest date is in the calendar table. The
presented solutions has fixed dates and i don't want that.

If i could store a global variable in SQL server (dynamic properties?)
then it would be great. Fill this once and call it multiple times in
my intensively used function. Is this possible?

Greetz
Hennie

----------------------------Previously entered
issue-----------------------
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
Plaats een reactie op dit bericht

Bericht 2 van deze discussie
Van:John Bell (jbellnewsposts@hotmail.com)
Onderwerp:Re: Intensively used function in view needs a minimum and
maximum from a table


View this article only
Discussies:comp.databases.ms-sqlserver
Datum:2004-12-30 03:56:25 PST


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@hotmail.com> wrote in message
news:191115aa.0412300238.7dee0f85@posting.google.c om...[color=blue]
>I have a problem (who not?) with a function which i'm using in a[/color]
view.[color=blue]
> 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[/color]
Plaats een reactie op dit bericht

Bericht 3 van deze discussie
Van:Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo)
Onderwerp:Re: Intensively used function in view needs a minimum and
maximum from a table


View this article only
Discussies:comp.databases.ms-sqlserver
Datum:2004-12-30 15:32:06 PST


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[/color]
view.[color=blue]
>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_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)