473,379 Members | 1,542 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

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 (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)
Jul 23 '05 #1
2 2298
On 3 Jan 2005 08:26:56 -0800, Hennie de Nooijer wrote:

(snip)
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.


Hi Hennie,

Did you try my suggestion, to replace your current calendar-based
user0defined function with the following expression, using builtin
functions only:

CAST(CONVERT(varchar, date_column, 112) AS int)

If you did try this, why didn't it work?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2
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:
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 (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 myproblem.

(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)


Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Lobang Trader | last post by:
Hi all, I am trying to create a username and a password class. I would like to know what are the RECOMMENDED minimum and maximum length for both fields? These fields will be something like...
6
by: Jef Driesen | last post by:
I need to implement a function to implement the rounding of floating point values. At the moment i have two different implementations, depending on the type of the return value (integer or double)....
2
by: Hennie de Nooijer | last post by:
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....
1
by: Najib Abi Fadel | last post by:
Hi i have an ordered table of dates let's say: 1/1/2004 8/1/2004 15/1/2004 29/1/2004 5/2/2004 12/2/2004
1
by: itsolutionsfree | last post by:
Hi All, i am deep trouble.so,please help me how to get minimum and maximum dates for particular Month and Year.Any kind of help will help. for ex: im passing : March 2006 Minimum Date -...
5
by: pat270881 | last post by:
hello, i should implement this class: namespace test_1 { class statistician { public: // CONSTRUCTOR
14
by: Olw | last post by:
Hello, Does anyone have a nice link to some code for calculating the gradient of a function in C? Thanks, Olw
1
by: greek | last post by:
Hi! I'm suppode to write a prg to calculate maximum and minimum of 4 intergers by using functions i've writen the code but getting the error: call of a non function..cant see my error help me..here...
2
by: John Geddes | last post by:
Has anyone come up with an "IsInview" function that one could use to test whether scrollIntoView is needed? Imagine a (scrolled) list of editable item prices. I want to impose maximum and...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.