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 (jb************@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" <hd********@hotmail.com> wrote in message
news:19**************************@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 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:
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)
