still not clear

**Simon Hayes** · Jul 23 '05, 07:34 AM

Re: still not clear

"Hennie de Nooijer" <hdenooijer@hot mail.com> wrote in message
news:41da5eaa$1 _1@127.0.0.1...[color=blue]
> hi thank you for this solution Hugo (dutch?) and it's very handy, thank
> you but my issue is that we created dozens of views with my function.
> This functions returns an integer of a date. This function has to give a
> lowerlimit and higherlimit back when dates are out of Calendar range.
>
> But i want to read this from the Calendartable ONCE and store it in a
> global variable somewhere in SQL Server. I DON'T want to reference
> tables in my function. SO read once the lower- and upperlimit and use
> this variable/property for referencing in my function!!
>
>
> Greetz
> Hennie
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

I haven't read your previous posts, but there are no global variables in
TSQL, and the only place you can store data is in a table - it's quite
common to have a dbo.Constants (or whatever) table for this purpose.

If you can use stored procs or table-valued functions instead of views you
might be able to do something such as retrieve the correct date values once,
then pass them to each proc/function as parameters, but I have no idea if
that would be a good idea in your situation. If you definitely need to use
views then you might look into replacing your function with a CASE statement
in the view definition.

If that doesn't help, you might want to give a (simplified) set of DDL and
INSERT statements for your table, view, function and sample data which can
be run in QA - with a clear example of your problem, someone may be able to
make a better suggestion. (Apologies if you've already done this in another
post.)

Simon

**Hugo Kornelis** · Jul 23 '05, 07:34 AM

Re: still not clear

On 4 Jan 2005 03:15:22 -0600, Hennie de Nooijer wrote:
[color=blue]
>hi thank you for this solution Hugo (dutch?) and it's very handy, thank
>you but my issue is that we created dozens of views with my function.
>This functions returns an integer of a date. This function has to give a
>lowerlimit and higherlimit back when dates are out of Calendar range.[/color]

Hoi Hennie,

Ja, ik ben inderdaad Nederlander. Jij ook, neem ik aan? :-)
But let's continue in English - this is an English group, after all.

Could you post the current code of your function? I can understand not
wanting to change something used in so many places, but I guess it might
be possible to change the function's code to do the same as it is doing
now, but without the limitations you are experiencing. However, I can only
be sure if I see your code.

[color=blue]
>But i want to read this from the Calendartable ONCE and store it in a
>global variable somewhere in SQL Server. I DON'T want to reference
>tables in my function. SO read once the lower- and upperlimit and use
>this variable/property for referencing in my function!![/color]

Simon is correct - there are no global variables. Other than that, I also
am not sure if I really understand what you are trying to achieve, so I
will second Simon's request to post table structure (as CREATE TABLE
statements), sample data (as INSERT statements) and expected output along
with the description of the problem you're trying to solve. That might
help me (and others) understand what you're trying to achieve. Check this
site: http://www.aspfaq.com/5006 - it has some valuable tips to help you
assemble the requested data.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**Hennie de Nooijer** · Jul 23 '05, 07:34 AM

Re: still not clear

Ok here 's an impression of my view(see below). As you can see i called
the function xxxxx_FN_GetDat eString(ISNULL( Intake_Date,'19 90-01-01
00:00:00')) multiple times This function returns an integer back :
19900101. Not more than that!!!

The calendar table (see create script below) has information about the
dates, which month, which day, etc. This is standard table in a
datawarehouse. Nothing special. The Calendar_Dim_ke y has the same key as
my generated date : 19900101 for example.

If i want to return a minimumdate and a maximumdate(bec ause my
calendartable has limited dateinformation )
and if want to use the lowest date and the highest date in the calendar
table i would have to create a SELECT MIN(Calendar_Di m_Key) FROM
SDM_Calendar and a SELECT MAX(Calendar_Di m_Key) FROM SDM_Calendar in my
FUNCTION.

Every time i call this function it would execute this query twice but
this information is retrieved already is by a former call of this
function. And this irritates me. I want a one call, retrieve the
information and use it multiple times....

An example:

My calendar table ranges from 1980 - 2049 and when the sourcetable has a
date like 01-05-249 the function has to return a 19800101 date (249 <
1980) not 2490501 because this a faulty date.

So i hope it's now easier to understand. Thanx.

en ja hugo ik ben ook nederlander;-) ik hoop dat dit wat duidelijker is.

Greetz

Hennie

-------------------------------------------------
This is the create script of the calendar table:
-------------------------------------------------
CREATE TABLE [dbo].[SDM_Calendar] (
[Calendar_Dim_Ke y] [int] NOT NULL ,
[Full_Date_App] [datetime] NOT NULL ,
[Day_of_Month] [tinyint] NOT NULL ,
[Day_of_Year] [smallint] NOT NULL ,
[Day_Full_Name] [varchar] (9) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[Week_Number] [tinyint] NOT NULL ,
[Month_Full_Name] [varchar] (9) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[Month_Number] [tinyint] NOT NULL ,
[Quarter] [tinyint] NOT NULL ,
[Quarter_Full_Na me] [varchar] (9) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Calendar_Year] [smallint] NOT NULL
) ON [SAMIS_SDM_Index 1]
GO
----------------------------------------------
This is the view
-----------------------------------------------
CREATE VIEW TR_Fact_Event_A ction1_V
AS
SELECT
Event_Process_T ype as Event_Process_T ype_Code,
...........
ISNULL(contract _id,'N/A') as Serv_Contract_C ode,
ISNULL(Site_cat egory,'N/A') as Event_Site_Cate gory_Code,
ISNULL(Cause_Co de,'N/A') as Event_Cause_Cod e,
ISNULL(Resoluti on_Code,'N/A') as Event_Resolutio n_Code,
..........
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Close_Time,O pen_Time))
as FK_Event_Close_ Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Close_Time,O pen_Time))
as FK_Event_Close_ Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Reopen_Time, Open_Time))
as FK_Event_Reopen _Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Reopen_Time, Open_Time))
as FK_Event_Reopen _Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Site_Visit_T ime,Open_Tim
e)) as FK_Event_Site_V isit_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Site_Visit_T ime,Open_Tim
e)) as FK_Event_Site_V isit_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(SLA_Expire_T ime,Open_Tim
e)) as FK_Event_SLA_Ex pire_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(SLA_Expire_T ime,Open_Tim
e)) as FK_Event_SLA_Ex pire_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Request_Date ,'1990-01-01
00:00:00')) as FK_Event_Reques t_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Request_Date ,'1990-01-01
00:00:00')) as FK_Event_Reques t_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Negotiate_Da te,'1990-01-
01 00:00:00')) as FK_Event_Negoti ate_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Negotiate_Da te,
'1990-01-01 00:00:00')) as FK_Event_Negoti ate_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Intake_Date, '1990-01-01
00:00:00')) as FK_Event_Intake _Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Intake_Date, '1990-01-01
00:00:00')) as FK_Event_Intake _Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Planned_Star t_Time,'1990
-01-01 00:00:00')) as FK_Event_Planne d_Start_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Planned_Star t_Time,'1990
-01-01 00:00:00')) as FK_Event_Planne d_Start_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Planned_End_ Time,'1990-0
1-01 00:00:00')) as FK_Event_Planne d_End_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Planned_End_ Time,'1990-0
1-01 00:00:00')) as FK_Event_Planne d_End_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Implement_St art_Time,'19
90-01-01 00:00:00')) as FK_Event_Implem ent_Start_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Implement_St art_Time,'19
90-01-01 00:00:00')) as FK_Event_Implem ent_Start_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Implement_En d_Time,'1990
-01-01 00:00:00')) as FK_Event_Implem ent_End_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Implement_En d_Time,'1990
-01-01 00:00:00')) as FK_Event_Implem ent_End_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Action_Open_ Time,Open_Ti
me)) as FK_Action_Open_ Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Action_Open_ Time,Open_Ti
me)) as FK_Action_Open_ Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(ISNULL(Actio n_Close_Time
,Close_Time),Op en_Time)) as FK_Action_Close _Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(ISNULL(Actio n_Close_Time
,Close_Time),Op en_Time)) as FK_Action_Close _Time,
......
CASE
WHEN Close_Time IS NOT NULL THEN
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA
(ISNULL(SLA_Exp ire_Time,Close_ Time),
Close_Time,ISNU LL(service_sche dule,'N/A'),'Y','N')
ELSE 0 END,0) as SLA_Exceed_Clos ed_Time_Min,
'0' AS SLA_Exceed_Clos ed_Time_Min_2,
ISNULL(
CASE
WHEN Resolved_Time IS NOT NULL THEN
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA (Open_Time,
Resolved_Time,I SNULL(service_s chedule,'N/A'),'Y','N')
WHEN Resolved_Time IS NULL AND Close_Time IS NOT NULL THEN
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA (Open_Time,
Close_Time,ISNU LL(service_sche dule,'N/A'),'Y','N')
ELSE 0 END,0) as Event_Duration_ Resolved_Time_M in,
ISNULL(
CASE
WHEN Close_Time IS NOT NULL THEN
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA (Open_Time,
Close_Time,ISNU LL(service_sche dule,'N/A'),'Y','N')
ELSE 0 END,0) as Event_Duration_ Closed_Time_Min ,
ISNULL(
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA (Action_Open_Ti me,
Action_Close_Ti me,ISNULL(servi ce_schedule,'N/A'),'Y','N'),0) as
Action_Duration _Min,
CASE
WHEN Event_Process_T ype = 'Call'
THEN DATEDIFF (ss , '4000-01-01 00:00:00', call_handle_tim e) ELSE 0
END as Call_Handling_S ec,
ISNULL(Planned_ Hours,0) as Event_Planned_H ours,
ISNULL(Spent_Ho urs,0) as Event_Spent_Hou rs,
ISNULL(Remainin g_Hours,0) as Event_Remaining _Hours,
ISNULL(sysmodti me,GETDATE()) as Sysmodtime
FROM
SAD_Fact_Event_ Action

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Hennie de Nooijer** · Jul 23 '05, 07:34 AM

Re: still not clear

Ok here 's an impression of my view(see below). As you can see i called
the function xxxxx_FN_GetDat eString(ISNULL( Intake_Date,'19 90-01-01
00:00:00')) multiple times This function returns an integer back :
19900101. Not more than that!!!

The calendar table (see create script below) has information about the
dates, which month, which day, etc. This is standard table in a
datawarehouse. Nothing special. The Calendar_Dim_ke y has the same key as
my generated date : 19900101 for example.

If i want to return a minimumdate and a maximumdate(bec ause my
calendartable has limited dateinformation )
and if want to use the lowest date and the highest date in the calendar
table i would have to create a SELECT MIN(Calendar_Di m_Key) FROM
SDM_Calendar and a SELECT MAX(Calendar_Di m_Key) FROM SDM_Calendar in my
FUNCTION.

Every time i call this function it would execute this query twice but
this information is retrieved already is by a former call of this
function. And this irritates me. I want a one call, retrieve the
information and use it multiple times....

An example:

My calendar table ranges from 1980 - 2049 and when the sourcetable has a
date like 01-05-249 the function has to return a 19800101 date (249 <
1980) not 2490501 because this a faulty date.

So i hope it's now easier to understand. Thanx.

en ja hugo ik ben ook nederlander;-) ik hoop dat dit wat duidelijker is.

Greetz

Hennie

-------------------------------------------------
This is the create script of the calendar table:
-------------------------------------------------
CREATE TABLE [dbo].[SDM_Calendar] (
[Calendar_Dim_Ke y] [int] NOT NULL ,
[Full_Date_App] [datetime] NOT NULL ,
[Day_of_Month] [tinyint] NOT NULL ,
[Day_of_Year] [smallint] NOT NULL ,
[Day_Full_Name] [varchar] (9) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[Week_Number] [tinyint] NOT NULL ,
[Month_Full_Name] [varchar] (9) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[Month_Number] [tinyint] NOT NULL ,
[Quarter] [tinyint] NOT NULL ,
[Quarter_Full_Na me] [varchar] (9) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Calendar_Year] [smallint] NOT NULL
) ON [SAMIS_SDM_Index 1]
GO
----------------------------------------------
This is the view
-----------------------------------------------
CREATE VIEW TR_Fact_Event_A ction1_V
AS
SELECT
Event_Process_T ype as Event_Process_T ype_Code,
...........
ISNULL(contract _id,'N/A') as Serv_Contract_C ode,
ISNULL(Site_cat egory,'N/A') as Event_Site_Cate gory_Code,
ISNULL(Cause_Co de,'N/A') as Event_Cause_Cod e,
ISNULL(Resoluti on_Code,'N/A') as Event_Resolutio n_Code,
..........
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Close_Time,O pen_Time))
as FK_Event_Close_ Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Close_Time,O pen_Time))
as FK_Event_Close_ Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Reopen_Time, Open_Time))
as FK_Event_Reopen _Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Reopen_Time, Open_Time))
as FK_Event_Reopen _Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Site_Visit_T ime,Open_Tim
e)) as FK_Event_Site_V isit_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Site_Visit_T ime,Open_Tim
e)) as FK_Event_Site_V isit_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(SLA_Expire_T ime,Open_Tim
e)) as FK_Event_SLA_Ex pire_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(SLA_Expire_T ime,Open_Tim
e)) as FK_Event_SLA_Ex pire_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Request_Date ,'1990-01-01
00:00:00')) as FK_Event_Reques t_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Request_Date ,'1990-01-01
00:00:00')) as FK_Event_Reques t_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Negotiate_Da te,'1990-01-
01 00:00:00')) as FK_Event_Negoti ate_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Negotiate_Da te,
'1990-01-01 00:00:00')) as FK_Event_Negoti ate_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Intake_Date, '1990-01-01
00:00:00')) as FK_Event_Intake _Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Intake_Date, '1990-01-01
00:00:00')) as FK_Event_Intake _Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Planned_Star t_Time,'1990
-01-01 00:00:00')) as FK_Event_Planne d_Start_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Planned_Star t_Time,'1990
-01-01 00:00:00')) as FK_Event_Planne d_Start_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Planned_End_ Time,'1990-0
1-01 00:00:00')) as FK_Event_Planne d_End_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Planned_End_ Time,'1990-0
1-01 00:00:00')) as FK_Event_Planne d_End_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Implement_St art_Time,'19
90-01-01 00:00:00')) as FK_Event_Implem ent_Start_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Implement_St art_Time,'19
90-01-01 00:00:00')) as FK_Event_Implem ent_Start_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Implement_En d_Time,'1990
-01-01 00:00:00')) as FK_Event_Implem ent_End_Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Implement_En d_Time,'1990
-01-01 00:00:00')) as FK_Event_Implem ent_End_Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Action_Open_ Time,Open_Ti
me)) as FK_Action_Open_ Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Action_Open_ Time,Open_Ti
me)) as FK_Action_Open_ Time,
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(ISNULL(Actio n_Close_Time
,Close_Time),Op en_Time)) as FK_Action_Close _Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(ISNULL(Actio n_Close_Time
,Close_Time),Op en_Time)) as FK_Action_Close _Time,
......
CASE
WHEN Close_Time IS NOT NULL THEN
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA
(ISNULL(SLA_Exp ire_Time,Close_ Time),
Close_Time,ISNU LL(service_sche dule,'N/A'),'Y','N')
ELSE 0 END,0) as SLA_Exceed_Clos ed_Time_Min,
'0' AS SLA_Exceed_Clos ed_Time_Min_2,
ISNULL(
CASE
WHEN Resolved_Time IS NOT NULL THEN
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA (Open_Time,
Resolved_Time,I SNULL(service_s chedule,'N/A'),'Y','N')
WHEN Resolved_Time IS NULL AND Close_Time IS NOT NULL THEN
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA (Open_Time,
Close_Time,ISNU LL(service_sche dule,'N/A'),'Y','N')
ELSE 0 END,0) as Event_Duration_ Resolved_Time_M in,
ISNULL(
CASE
WHEN Close_Time IS NOT NULL THEN
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA (Open_Time,
Close_Time,ISNU LL(service_sche dule,'N/A'),'Y','N')
ELSE 0 END,0) as Event_Duration_ Closed_Time_Min ,
ISNULL(
xxxxx_Control.d bo.xxxxx_FN_Get DiffSLA (Action_Open_Ti me,
Action_Close_Ti me,ISNULL(servi ce_schedule,'N/A'),'Y','N'),0) as
Action_Duration _Min,
CASE
WHEN Event_Process_T ype = 'Call'
THEN DATEDIFF (ss , '4000-01-01 00:00:00', call_handle_tim e) ELSE 0
END as Call_Handling_S ec,
ISNULL(Planned_ Hours,0) as Event_Planned_H ours,
ISNULL(Spent_Ho urs,0) as Event_Spent_Hou rs,
ISNULL(Remainin g_Hours,0) as Event_Remaining _Hours,
ISNULL(sysmodti me,GETDATE()) as Sysmodtime
FROM
SAD_Fact_Event_ Action

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Hugo Kornelis** · Jul 23 '05, 07:35 AM

Re: still not clear

On 5 Jan 2005 05:20:22 -0600, Hennie de Nooijer wrote:
[color=blue]
>Ok here 's an impression of my view(see below). As you can see i called
>the function xxxxx_FN_GetDat eString(ISNULL( Intake_Date,'19 90-01-01
>00:00:00')) multiple times This function returns an integer back :
>19900101. Not more than that!!![/color]
(snip)

Hi Hennie,

Thanks for posting the code. Unfortunately, you forgot to include the code
of the function itself. But based on what you wrote, I'd say you have
several options:

IF the function currently has a datetime as input parameter and an integer
as output, you could replace the function:

ALTER FUNCTION dbo.xxxxx_FN_Ge tDateString (@InDate datetime)
RETURNS int
AS
BEGIN
DECLARE @OutDate int
SET @OutDate = CAST(CONVERT(va rchar, @InDate, 112) AS int)
RETURN (@OutDate)
END

The above assumes that you only want the special handling to prevent
errors based on your calendar table not having all dates and would be
happy to convert dates outside of that range if possible.

If there are other reasons to impose a minimum and maximum date as well,
then you'd have to tweak it a little:

ALTER FUNCTION dbo.xxxxx_FN_Ge tDateString (@InDate datetime)
RETURNS int
AS
BEGIN
DECLARE @OutDate int,
@MinDate datetime,
@MaxDate datetime
SET @MinDate = '19800101'
SET @MaxDate = '20491231'
SET @OutDate = CASE
WHEN @InDate < @MinDate
THEN CAST(CONVERT(va rchar, @MinDate, 112) AS int)
WHEN @InDate > @MaxnDate
THEN CAST(CONVERT(va rchar, @MaxDate, 112) AS int)
ELSE CAST(CONVERT(va rchar, @InDate, 112) AS int)
END
RETURN (@OutDate)
END

If this doesn't do what you need, then it's time I ask you to post a repro
script (a script containing all CREATE TABLE, INSERT, CREATE PROC, CREATE
VIEW, CREATE FUNCTION and other statements needed to create a simplified
version of your situation - simplified as much as possible, to make it as
small as possible while still showing what you want to get done).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**Hennie de Nooijer** · Jul 23 '05, 07:35 AM

Re: still not clear

Hi Hugo,

As you post in your message you put a hardcoded minimum and maximumdate
in your function.

SET @MinDate = '19800101'
SET @MaxDate = '20491231'

I want it to make it flexible and dynamic

I want to make it something like this:

SET @MinDate = (SELECT MIN(Calendar_di m_key) From Calendar)
SET @MaxDate = (SELECT MAX(Calendar_di m_key) From Calendar)

But if i do that it will query the calendar table twice and that for
every time i use this function in the views and i don't want that
because of performance issues. So i want a sort of global variable which
i could fill in once and use it many times. Or maybe with a trick in the
query in which i call this function?

Greetz

Hennie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Hugo Kornelis** · Jul 23 '05, 07:35 AM

Re: still not clear

On 5 Jan 2005 09:20:24 -0600, Hennie de Nooijer wrote:
[color=blue]
>Hi Hugo,
>
>As you post in your message you put a hardcoded minimum and maximumdate
>in your function.
>
>SET @MinDate = '19800101'
>SET @MaxDate = '20491231'
>
>I want it to make it flexible and dynamic
>
>I want to make it something like this:
>
>SET @MinDate = (SELECT MIN(Calendar_di m_key) From Calendar)
>SET @MaxDate = (SELECT MAX(Calendar_di m_key) From Calendar)
>
>But if i do that it will query the calendar table twice and that for
>every time i use this function in the views and i don't want that
>because of performance issues. So i want a sort of global variable which
>i could fill in once and use it many times. Or maybe with a trick in the
>query in which i call this function?[/color]

Hi Hennie,

You can reduce the number of queries to the table to one:

SELECT @MinDate = MIN(Calendar_di m_key),
@MaxDate = MAX(Calendar_di m_key)
FROM Calendar

This will still have to be done for each execution of the user-defined
function, as there are no global variable in SQL Server. All executions
after the will take the results from cache, so they won't be terribly
slow, but they will have some slowness. Do make sure that Calendar_dim_ke y
is indexed, otherwise you'll have a table scan which DEFINITELY is slow.

Another possible improvement is to store min_date and max_date in a
special one-row table of constants:
CREATE TABLE Constants
(LimitToOne char(1) NOT NULL PRIMARY KEY DEFAULT 'X'
CHECK (LimitToOne = 'X'),
min_date datetime NOT NULL,
max_date datetime NOT NULL,
.....)
You could use triggers on Calendar to keep min_date and max_date in sync
with the Calendar table. This might give a small performance improvement
over using MIN() and MAX() as above, but not much.

If you really want to reduce the number of accesses to the Calendar table,
your only option is to change the function and supply min_date and
max_date as parameters. This will force you to change all code where the
function os called. If you decide to take this route, your function will
look something like this (omitting the details for brevity):

CREATE FUNCTION xxxxx_FN_GetDat eString
(@DateIn datetime, @DateMin datetime, @DateMax datetime)
RETURNS int
AS
.....

And your query will become something like the following:

SELECT
Event_Process_T ype as Event_Process_T ype_Code,
...........
xxxxx_Control.d bo.xxxxx_FN_Get DateString(ISNU LL(Close_Time,O pen_Time),
md.MinDate,md.M axDate)
as FK_Event_Close_ Date,
xxxxx_Control.d bo.xxxxx_FN_Get TimeString(ISNU LL(Close_Time,O pen_Time),
md.MinDate,md.M axDate)
as FK_Event_Close_ Time,
...........
FROM SAD_Fact_Event_ Action
CROSS JOIN (SELECT MIN(Calendar_di m_key) AS MinDate,
MAX(Calendar_di m_key) AS MaxDate
FROM Calendar) AS md

Now, the minimum and maximum date will only be fetched once from the
Calendar table, then passed as argument to the function.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**Erland Sommarskog** · Jul 23 '05, 07:35 AM

Re: still not clear

Hennie de Nooijer (hdenooijer@hot mail.com) writes:[color=blue]
> I want to make it something like this:
>
> SET @MinDate = (SELECT MIN(Calendar_di m_key) From Calendar)
> SET @MaxDate = (SELECT MAX(Calendar_di m_key) From Calendar)
>
> But if i do that it will query the calendar table twice and that for
> every time i use this function in the views and i don't want that
> because of performance issues. So i want a sort of global variable which
> i could fill in once and use it many times. Or maybe with a trick in the
> query in which i call this function?[/color]

There are no global variables, and, assuming that the Calender_dim_ke y
column is indexed, neither is there any performance issue. Would this
table frequently be updated, you could have contention issues, but, then
again, if the table is frequently updated, you probably need to query
the table each time to get the max/min values.

What you are looking for a place to keep a cache of these values. But
this cache is already there! Ever wondered why SQL Server takes so much
memory? Because it likes to have huge cache. Since this table would
be queried frequently, it would be cache constantly, so access to it as
quick your global variables.

There are many ways to make things go really slow in a database, but
this is one one of them.

Again, I like to stress, assuming that the key is indexed.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

SQL Server 2025 | Microsoft Evaluation Center

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Accelerate your AI application's time to market by harnessing the power of your own data and the built-in AI capabilities of SQL Server 2025, the enterprise database with best-in-class security, performance and availability.

**Hennie de Nooijer** · Jul 23 '05, 07:37 AM

Re: still not clear

Haha thanx for your reply Erland. Trust me i don't want things to go
slow. hmmm Should i use the proposed solution of Hugo with the
crossjoin? I think this a very good solution.

And secondly, is there information available about what is cached and
what not? And can i see this somewhere in SQL Server?

Hennie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Hugo Kornelis** · Jul 23 '05, 07:37 AM

Re: still not clear

On 7 Jan 2005 04:30:21 -0600, Hennie de Nooijer wrote:
[color=blue]
>Haha thanx for your reply Erland. Trust me i don't want things to go
>slow. hmmm Should i use the proposed solution of Hugo with the
>crossjoin? I think this a very good solution.[/color]

Hi Hennie,

If I were you, I'd postpone that and first try how things go if you just
fetch min and max date at the start of the function. At most one read will
not be from cache, all others will. (And - it just can't be stressed
enough - you REALLY need to have an index on Calendar_dim_ke y!). The cross
join I proposed would require you to change all your code. Fine if you
really need to squeeze the last bit of performance out of your queries,
but a waste of your time otherwise.

[color=blue]
>And secondly, is there information available about what is cached and
>what not?[/color]

Every bit of data read by SQL Server goes in the cache. The maximum cache
size can be configured; by default, SQL Server will simply use all memory
it can get, unless other applications need it. Once no more memory is
available, SQL Server will remove the data from cache that was accessed
last. Therefor, any data that is frequently accessed will stay in ccache
forever.

[color=blue]
> And can i see this somewhere in SQL Server?[/color]

If you run your queries with SET STATISTICS IO ON, you'll the number of
logical reads (from cache or disk) and the number of physical reads (from
disk only). The difference between these numbers is an indication of how
much was taken from cache. You can easily check this by running a query
against a table not used often, then directly running the query again.

use Northwind
set statistics io on
select * from Categories
select * from Categories

I'm not sure though if reads from within a function are properly
reflected. If not, you'll have to use Profiler instead.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**Erland Sommarskog** · Jul 23 '05, 07:37 AM

Re: still not clear

Hennie de Nooijer (hdenooijer@hot mail.com) writes:[color=blue]
> Haha thanx for your reply Erland. Trust me i don't want things to go
> slow. hmmm Should i use the proposed solution of Hugo with the
> crossjoin? I think this a very good solution.[/color]

I don't remember Hugo's CROSS JOIN, but he seems to know SQL a bit, so
it's probably a good bet.
[color=blue]
> And secondly, is there information available about what is cached and
> what not? And can i see this somewhere in SQL Server?[/color]

I say: just don't worry about it. If the table is queried often enough
the data will be in the cache. If the table is only queried every
second Wednesday morning, the data will drop out of the cache by
Wednesday noon. But in that case, it's probably not critical that data
is in cache.

There is actually a way to force a table to be resident in memory, but
usage of this possibility is not recommended, and if I recall correctly,
that feature has been dropped in SQL2005, because it caused more harm than
good.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

SQL Server 2025 | Microsoft Evaluation Center

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Accelerate your AI application's time to market by harnessing the power of your own data and the built-in AI capabilities of SQL Server 2025, the enterprise database with best-in-class security, performance and availability.

still not clear

still not clear

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment