| re: Intensively used function in view needs a minimum and maximum from a table PartII
Hi
If you are using a stored procedure then you can do the
SELECT @maxdate =MAX([Date]) @mindate = MIN([Date]) FROM MyCalendar
and then pass these as parameters to your function. Alternatively you
can have the select appropriate select statements instead of the hard
coded dates in the function.
As Hugo says: why is there a problem with the dates not being in your
calendar table? The cast/convert option is probably the quickest!
John
Hennie de Nooijer wrote:[color=blue]
> Because of an error in google or underlying site i can reply on my[/color]
own[color=blue]
> issue. Therefore i copied the former entered message in this message.
>[/color]
-------------------------------------REPY----------------------------------[color=blue]
> 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[/color]
properties?)[color=blue]
> 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[/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[/color]
this[color=blue]
> 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[/color]
'19800101'[color=blue]
> 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=green]
> >I have a problem (who not?) with a function which i'm using in a[/color]
> view.[color=green]
> > 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[/color][/color]
my[color=blue][color=green]
> > 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[/color][/color]
this[color=blue][color=green]
> > function insert a minimum date when date < minimum date and a[/color][/color]
maximum[color=blue][color=green]
> > date when date > maximum date.
> >
> > Yes i know you're thinking : This is datamanipulation and yes this[/color][/color]
is[color=blue][color=green]
> > true. But now we loose information in our cubes and reports by[/color][/color]
inner[color=blue][color=green]
> > 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=green]
> >I have a problem (who not?) with a function which i'm using in a[/color]
> view.[color=green]
> >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[/color][/color]
my[color=blue][color=green]
> >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)[/color]
| 
