Ok here 's an impression of my view(see below). As you can see i called
the function xxxxx_FN_GetDateString(ISNULL(Intake_Date,'1990-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_key has the same key as
my generated date : 19900101 for example.
If i want to return a minimumdate and a maximumdate(because 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_Dim_Key) FROM
SDM_Calendar and a SELECT MAX(Calendar_Dim_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_Key] [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_General_CP1_CI_AS NOT
NULL ,
[Week_Number] [tinyint] NOT NULL ,
[Month_Full_Name] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Month_Number] [tinyint] NOT NULL ,
[Quarter] [tinyint] NOT NULL ,
[Quarter_Full_Name] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Calendar_Year] [smallint] NOT NULL
) ON [SAMIS_SDM_Index1]
GO
----------------------------------------------
This is the view
-----------------------------------------------
CREATE VIEW TR_Fact_Event_Action1_V
AS
SELECT
Event_Process_Type as Event_Process_Type_Code,
...........
ISNULL(contract_id,'N/A') as Serv_Contract_Code,
ISNULL(Site_category,'N/A') as Event_Site_Category_Code,
ISNULL(Cause_Code,'N/A') as Event_Cause_Code,
ISNULL(Resolution_Code,'N/A') as Event_Resolution_Code,
..........
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Cl ose_Time,Open_Time))
as FK_Event_Close_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Cl ose_Time,Open_Time))
as FK_Event_Close_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Re open_Time,Open_Time))
as FK_Event_Reopen_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Re open_Time,Open_Time))
as FK_Event_Reopen_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Si te_Visit_Time,Open_Tim
e)) as FK_Event_Site_Visit_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Si te_Visit_Time,Open_Tim
e)) as FK_Event_Site_Visit_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(SL A_Expire_Time,Open_Tim
e)) as FK_Event_SLA_Expire_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(SL A_Expire_Time,Open_Tim
e)) as FK_Event_SLA_Expire_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Re quest_Date,'1990-01-01
00:00:00')) as FK_Event_Request_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Re quest_Date,'1990-01-01
00:00:00')) as FK_Event_Request_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Ne gotiate_Date,'1990-01-
01 00:00:00')) as FK_Event_Negotiate_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Ne gotiate_Date,
'1990-01-01 00:00:00')) as FK_Event_Negotiate_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(In take_Date,'1990-01-01
00:00:00')) as FK_Event_Intake_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(In take_Date,'1990-01-01
00:00:00')) as FK_Event_Intake_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Pl anned_Start_Time,'1990
-01-01 00:00:00')) as FK_Event_Planned_Start_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Pl anned_Start_Time,'1990
-01-01 00:00:00')) as FK_Event_Planned_Start_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Pl anned_End_Time,'1990-0
1-01 00:00:00')) as FK_Event_Planned_End_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Pl anned_End_Time,'1990-0
1-01 00:00:00')) as FK_Event_Planned_End_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Im plement_Start_Time,'19
90-01-01 00:00:00')) as FK_Event_Implement_Start_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Im plement_Start_Time,'19
90-01-01 00:00:00')) as FK_Event_Implement_Start_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Im plement_End_Time,'1990
-01-01 00:00:00')) as FK_Event_Implement_End_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Im plement_End_Time,'1990
-01-01 00:00:00')) as FK_Event_Implement_End_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Ac tion_Open_Time,Open_Ti
me)) as FK_Action_Open_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Ac tion_Open_Time,Open_Ti
me)) as FK_Action_Open_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(IS NULL(Action_Close_Time
,Close_Time),Open_Time)) as FK_Action_Close_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(IS NULL(Action_Close_Time
,Close_Time),Open_Time)) as FK_Action_Close_Time,
......
CASE
WHEN Close_Time IS NOT NULL THEN
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA
(ISNULL(SLA_Expire_Time,Close_Time),
Close_Time,ISNULL(service_schedule,'N/A'),'Y','N')
ELSE 0 END,0) as SLA_Exceed_Closed_Time_Min,
'0' AS SLA_Exceed_Closed_Time_Min_2,
ISNULL(
CASE
WHEN Resolved_Time IS NOT NULL THEN
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA (Open_Time,
Resolved_Time,ISNULL(service_schedule,'N/A'),'Y','N')
WHEN Resolved_Time IS NULL AND Close_Time IS NOT NULL THEN
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA (Open_Time,
Close_Time,ISNULL(service_schedule,'N/A'),'Y','N')
ELSE 0 END,0) as Event_Duration_Resolved_Time_Min,
ISNULL(
CASE
WHEN Close_Time IS NOT NULL THEN
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA (Open_Time,
Close_Time,ISNULL(service_schedule,'N/A'),'Y','N')
ELSE 0 END,0) as Event_Duration_Closed_Time_Min,
ISNULL(
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA (Action_Open_Time,
Action_Close_Time,ISNULL(service_schedule,'N/A'),'Y','N'),0) as
Action_Duration_Min,
CASE
WHEN Event_Process_Type = 'Call'
THEN DATEDIFF (ss , '4000-01-01 00:00:00', call_handle_time) ELSE 0
END as Call_Handling_Sec,
ISNULL(Planned_Hours,0) as Event_Planned_Hours,
ISNULL(Spent_Hours,0) as Event_Spent_Hours,
ISNULL(Remaining_Hours,0) as Event_Remaining_Hours,
ISNULL(sysmodtime,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!