August 17, 2007 at 3:37 pm
Hi,
I am using Sql server 2000 and I am trying to find the best way to optimize my views. I am new at this but I have a view calling another view calling another view because of the manipulations I need to do. I realize the performance hits that I am experiencing.
Is there a better way where I can pull the data from my main table with the first set of manipulations and then utilize the results for the next pull with those changes and then have the final results used in one view?
Jonathan
August 18, 2007 at 3:08 am
Hi Jonathan,
What sort of manipulations are you doing? Can you post the DDL for your Views and we might be able to get them working without a significant performance hit.
James
--
James Moore
Red Gate Software Ltd
August 30, 2007 at 7:20 pm
James,
Here is what I do.
First I convert to more readable names and create Open Time for later manipulation. Then I start using my lookups to fill in other parts (Part 2 and 3)
Then I use case statements to build other measures from the Open Time field in Step 1.
SELECT
C1 AS Case_ID,
C2 AS Submitted_For,
CONVERT(CHAR(19), DATEADD(s, C3, '1970-01-01 00:00:00.000'), 121) AS Arrival_Time,
C4 AS Assignee_Login_Name,
C5 AS Last_Modified_By, CONVERT(CHAR(19), DATEADD(s, C6, '1970-01-01 00:00:00.000'), 121) AS Modified_Time,
C7 AS Status,
C8 AS Summary,
C179 AS GUID,
C200000003 AS Category,
LTRIM(C200000004) AS Type,
C200000005 AS Item,
C200000006 AS Department,
C200000007 AS Site,
C200000012 AS Region,
C240000000 AS Requester_ID,
C240000001 AS Requester_Name,
C240000002 AS Phone_Number,
C240000004 AS Accounting_Code,
C240000005 AS Requester_Login_Name,
C240000006 AS Assigned_To_Group,
C240000007 AS Description,
C240000008 AS Work_Log,
C240000009 AS Request_Urgency,
C240000010 AS Closure_Code,
C240000011 AS Pending,
C240000012 AS Audit_Trail,
C240000015 AS Assigned_To_Individual,
C240000016 AS Assignee_Manager_Full_Name,
C240000017 AS Scope,
C240000111 AS Office,
C250000011 AS zAssigneeID,
C250000016 AS Assignee_Manager_Login,
C250000018 AS zAssignedGroupID,
C250000023 AS Escalated,
C250000030 AS Auto_Close_Days,
CONVERT(CHAR(19), DATEADD(s, C250000031, '1970-01-01 00:00:00.000'), 121) AS Auto_Close_Time,
C250000032 AS Request_Manager_to_Reassign,
C250000050 AS Confirm_Resolved_Case,
C260000000 AS Requested_Completion_Date,
C260000004 AS Hours_To_Resolve,
C260000005 AS Actual_End_Date,
C260000006 AS Actual_Start_Date,
C260000100 AS Solution_Summary,
C260000101 AS Search_Keyword,
CONVERT(CHAR(19), DATEADD(s,C260000104, '1970-01-01 00:00:00.000'), 121) AS Auto_Start_Time,
CONVERT(CHAR(19), DATEADD(s, C260000105, '1970-01-01 00:00:00.000'), 121) AS Auto_End_Time,
C260000123 AS Case_Category_Type_Item,
C260000126 AS Priority,
C260000127 AS Dup_ID,
C260000128 AS Source,
C260000129 AS Time_Spent_Min,
C260000130 AS Case_Type,
C260000133 AS Where_Am_I,
C260000500 AS Root_Cause,
C260000501 AS Resolution_Method,
CONVERT(CHAR(19), DATEADD(s, C260000502, '1970-01-01 00:00:00.000'), 121) AS Create_Time,
C260000503 AS Solution_Description,
C260000504 AS Hotlist,
C260100001 AS Orig_Submitter,
C260800007 AS Known_Status_Error,
C260800010 AS Known_Error,
C260800110 AS WAP_Type,
C269000001 AS ServiceWareSolutionID,
C290000001 AS Palm_Status,
C290000023 AS IncidentID,
C290000031 AS Incident_Count,
C300381700 AS Service, C300836800 AS Request_Impact,
CONVERT(CHAR(19), DATEADD(s, C300751700, '1970-01-01 00:00:00.000'), 121) AS Resolved_Time,
CONVERT(CHAR(19), DATEADD(s, C300751500, '1970-01-01 00:00:00.000'), 121) AS Assign_Time,
C300751300 AS Escalate_Time,
C300265600 AS Total_Time_Spent,
C300751400 AS Estimated_Total_Time,
C700001021 AS Alt_Phone_Number,
C700001800 AS Assigned_From,
CONVERT(CHAR(19), DATEADD(s, C700002200, '1970-01-01 00:00:00.000'), 121) AS Closed_Time,
C710000010 AS GSHSubmitContactMethod,
C730000001 AS Environment,
C770000600 AS PendingStatusStartTime,
C770000601 AS PendingStatusEndTime,
C770000602 AS PendingStatusTotalTimeSpent,
C777777777 AS Commandline,
C780000200 AS txtSpecialServicesCode,
C901000000 AS ZPrevStatus,
C901000004 AS zCurStatus,
DATENAME([MONTH], MONTH(CONVERT(CHAR(19), DATEADD(s, C260000502, '1970-01-01 00:00:00.000'), 121))) AS Create_Month,
YEAR(CONVERT(CHAR(19), DATEADD(s, C260000502, '1970-01-01 00:00:00.000'), 121)) AS Create_Year, DATENAME(WEEKDAY, DAY(CONVERT(CHAR(19), DATEADD(s, C260000502, '1970-01-01 00:00:00.000'), 121))) AS Create_Day,
CONVERT(CHAR(19), DATEADD(s, C3, CONVERT(DATETIME, '1899-12-30 00:00:00', 102)), 108) AS Open_Time
FROM Remedy.dbo.T374 **** SAVED AS VW_Remedy_Helpdesk (Part 1)
SELECT
dbo.Lookup_Table_Priority_Codes.Priority AS PRIORITY,
dbo.VW_Remedy_Helpdesk.Case_ID AS CASE_ID,
dbo.VW_Remedy_Helpdesk.Site AS SITE,
dbo.VW_Remedy_Helpdesk.Create_Time AS CREATE_DATE,
dbo.VW_Remedy_Helpdesk.Resolved_time AS RESOLVED_DATE,
dbo.VW_Remedy_Helpdesk.Closed_Time AS CLOSED_DATE,
CASE WHEN dbo.VW_Remedy_Helpdesk.Orig_Submitter = dbo.SYS_EYG_LOOKUP.Original_Submitter THEN 'EYG' ELSE Cast(dbo.VW_Remedy_Helpdesk.REGION AS nvarchar(25)) END REGION,
CASE WHEN dbo.VW_Remedy_Helpdesk.Orig_Submitter = dbo.SYS_EYG_LOOKUP.Original_Submitter THEN 'EYG' ELSE Cast(dbo.SYS_Lookup_Region_Codes.Region_Name AS nvarchar(25)) END REGION_NAME,
ISNULL(dbo.VW_Remedy_Helpdesk.Assigned_To_Group, N'No Data') AS ASSIGNED_TO_GROUP,
ISNULL(dbo.SYS_Lookup_Remedy_Group_Names.[Level], N'1') AS SUPPORT_LEVEL,
dbo.VW_Remedy_Helpdesk.Assigned_To_Individual AS ASSIGNED_TO_INDIVIDUAL, dbo.VW_Remedy_Helpdesk.Assign_Time AS ASSIGN_DATE,
dbo.VW_Remedy_Helpdesk.Orig_Submitter AS ORIGINAL_SUBMITTER,
dbo.VW_Remedy_Helpdesk.Requester_Name AS ORIGINAL_REQUESTER,
dbo.VW_Remedy_Helpdesk.Arrival_Time AS OPEN_DATE,
dbo.VW_Remedy_Helpdesk.Modified_Time AS LAST_MOD_DATE,
dbo.VW_Remedy_Helpdesk.Category AS CATEGORY_DESC,
dbo.VW_Remedy_Helpdesk.Item AS CAT_ITEM,
dbo.VW_Remedy_Helpdesk.Type AS CAT_TYPE,
dbo.VW_Remedy_Helpdesk.Open_Time AS OPEN_TIME,
dbo.VW_Remedy_Helpdesk.Environment AS ENVIRONMENT,
dbo.Lookup_Table_Case_Status.Status_Name AS STATUS,
ISNULL(dbo.VW_Remedy_Helpdesk.Office, N'No Data') AS OFFICE,
dbo.Lookup_Table_Case_Type.Case_Type AS CASE_TYPE,
dbo.Lookup_Remedy_Users.Remedy_Login_Name AS LAST_TOUCHED_BY,
{ fn NOW() } AS NOW,
CASE WHEN dbo.VW_Remedy_Helpdesk.Orig_Submitter = dbo.SYS_EYG_LOOKUP.Original_Submitter THEN 'EYG' ELSE Cast(dbo.SYS_LOOKUP_REGION_CODES.AREA AS nvarchar(25)) END AREA,
CAST(LEFT(dbo.VW_Remedy_Helpdesk.Arrival_Time, 10) AS DateTime) AS OPEN_DATE_ONLY,
CASE WHEN dbo.VW_Remedy_Helpdesk.Office LIKE 'CRT %' THEN 'With CRT'
WHEN dbo.VW_Remedy_Helpdesk.Office LIKE 'CED %' THEN 'With CRT'
WHEN dbo.VW_Remedy_Helpdesk.Office LIKE 'CRT Third' THEN 'Without CRT'
ELSE Cast('Without CRT' AS nvarchar(25)) END CRT_STATUS,
CASE WHEN dbo.VW_Remedy_Helpdesk.Orig_Submitter = dbo.SYS_EYG_LOOKUP.Original_Submitter THEN 'EYG' ELSE Cast(dbo.SYS_LOOKUP_REGION_CODES.SUB_AREA AS nvarchar(25)) END SUB_AREA
FROM dbo.VW_Remedy_Helpdesk LEFT OUTER JOIN
dbo.SYS_Lookup_Remedy_Group_Names ON
dbo.VW_Remedy_Helpdesk.Assigned_To_Group = dbo.SYS_Lookup_Remedy_Group_Names.Remedy_Group_Name
LEFT OUTER JOIN
dbo.SYS_EYG_Lookup ON dbo.VW_Remedy_Helpdesk.Orig_Submitter = dbo.SYS_EYG_Lookup.Original_Submitter
LEFT OUTER JOIN
dbo.Lookup_Table_Case_Type ON dbo.VW_Remedy_Helpdesk.Case_Type = dbo.Lookup_Table_Case_Type.Case_Type_ID
LEFT OUTER JOIN
dbo.Lookup_Table_Priority_Codes ON dbo.VW_Remedy_Helpdesk.Priority = dbo.Lookup_Table_Priority_Codes.Priority_ID
LEFT OUTER JOIN
dbo.Lookup_Table_Case_Status ON dbo.VW_Remedy_Helpdesk.Status = dbo.Lookup_Table_Case_Status.Status_ID
LEFT OUTER JOIN
dbo.SYS_Lookup_Region_Codes ON dbo.VW_Remedy_Helpdesk.Region = dbo.SYS_Lookup_Region_Codes.Region_Code
LEFT OUTER JOIN
dbo.Lookup_Remedy_Users ON dbo.VW_Remedy_Helpdesk.Last_Modified_By = dbo.Lookup_Remedy_Users.Remedy_Login
***** VW_HELPDESK_ALL_CALLS (Part 2)
SELECT
dbo.VW_HELPDESK_ALL_CALLS.*,
dbo.SYS_Lookup_Region_Codes.Region_Name AS SUB_AREA_NAME,
DATEDIFF(d, dbo.VW_HELPDESK_ALL_CALLS.OPEN_DATE, dbo.VW_HELPDESK_ALL_CALLS.NOW) AS CASE_AGE_IN_DAYS,
dbo.SYS_Lookup_Region_Codes.GFIS_Financial_Region AS GFIS_FINANCIAL_REGION, dbo.SYS_Lookup_Region_Codes.Wave AS WAVE,
dbo.SYS_Lookup_Region_Codes.Wave_Group AS WAVE_GROUP,
dbo.[Cat Types].Application AS APPLICATION,
dbo.[Cat Types].[Group] AS GSC_GROUP,
dbo.[Cat Types].Tab AS TAB,
dbo.[Cat Types].[Trend Type] AS TREND_TYPE,
dbo.SYS_DATE_LOOKUP_FINAL.[Date] AS [DATE],
dbo.SYS_DATE_LOOKUP_FINAL.Day_Name_Long AS DAY_NAME_LONG,
dbo.SYS_DATE_LOOKUP_FINAL.Quarter AS QUARTER,
dbo.SYS_DATE_LOOKUP_FINAL.WeekStartDate AS WEEK_START_DATE,
dbo.SYS_DATE_LOOKUP_FINAL.WeekEndDate AS WEEK_END_DATE, dbo.SYS_DATE_LOOKUP_FINAL.FYMonth_Name_Long AS FY_MONTH,
dbo.SYS_DATE_LOOKUP_FINAL.FY_Year AS FY_YEAR,
dbo.SYS_DATE_LOOKUP_FINAL.FY_Period AS FY_PERIOD,
dbo.[Cat Types].PLATFORM AS PLATFORM,
dbo.[Cat Types].APPLICATION_AREA AS APPLICATION_AREA
FROM dbo.VW_HELPDESK_ALL_CALLS
INNER JOIN
dbo.SYS_Lookup_Region_Codes ON dbo.VW_HELPDESK_ALL_CALLS.SUB_AREA = dbo.SYS_Lookup_Region_Codes.Region_Code
LEFT OUTER JOIN
dbo.SYS_DATE_LOOKUP_FINAL ON dbo.VW_HELPDESK_ALL_CALLS.OPEN_DATE_ONLY = dbo.SYS_DATE_LOOKUP_FINAL.[Date]
LEFT OUTER JOIN
dbo.[Cat Types] ON dbo.VW_HELPDESK_ALL_CALLS.CAT_TYPE = dbo.[Cat Types].[Cat Type]
***** VW_HELPDESK_ALL_CALLS_PART_2 (Part 3)
SELECT
dbo.VW_HELPDESK_ALL_CALLS_PART_2.*,
dbo.VW_Remedy_Helpdesk.Description,
{ fn MONTHNAME(dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_DATE) } AS MONTH, YEAR(dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_DATE) AS YEAR,
CASE WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.CASE_AGE_IN_DAYS = dbo.SYS_Case_Age_Lookup.CaseAgeinDays
THEN dbo.SYS_Case_Age_Lookup.CaseAgeGroup
ELSE Cast('Greater than 180' AS nvarchar(25)) END CASE_AGE_GROUP,
CASE WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '00:00:00' AND
'08:59:59'
THEN 'Overnight Shift'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '09:00:00' AND
'16:59:59'
THEN 'Day Shift' ELSE Cast('Evening Shift' AS nvarchar(25)) END SHIFT,
CASE
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '00:00:00' AND '00:59:59' THEN '01:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '01:00:00' AND '01:59:59' THEN '02:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '02:00:00' AND '02:59:59' THEN '03:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '03:00:00' AND '03:59:59' THEN '04:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '04:00:00' AND '04:59:59' THEN '05:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '05:00:00' AND '05:59:59' THEN '06:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '06:00:00' AND '06:59:59' THEN '07:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '07:00:00' AND '07:59:59' THEN '08:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '08:00:00' AND '08:59:59' THEN '09:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '09:00:00' AND '09:59:59' THEN '10:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '10:00:00' AND '10:59:59' THEN '11:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '11:00:00' AND '11:59:59' THEN '12:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '12:00:00' AND '12:59:59' THEN '13:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '13:00:00' AND '13:59:59' THEN '14:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '14:00:00' AND '14:59:59' THEN '15:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '15:00:00' AND '15:59:59' THEN '16:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '16:00:00' AND'16:59:59' THEN '17:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '17:00:00' AND '17:59:59' THEN '18:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '18:00:00' AND '18:59:59' THEN '19:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '19:00:00' AND '19:59:59' THEN '20:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '20:00:00' AND '20:59:59' THEN '21:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '21:00:00' AND '21:59:59' THEN '22:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '22:00:00' AND '22:59:59' THEN '23:00'
ELSE Cast('24:00' AS nvarchar(25)) END HOUR,
CASE
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '00:00:00' AND '00:29:59' THEN '00:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '00:30:00' AND '00:59:59' THEN '01:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '01:00:00' AND '01:29:59' THEN '01:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '01:30:00' AND '01:59:59' THEN '02:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '02:00:00' AND '02:29:59' THEN '02:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '02:30:00' AND '02:59:59' THEN '03:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '03:00:00' AND '03:29:59' THEN '03:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '03:30:00' AND '03:59:59' THEN '04:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '04:00:00' AND '04:29:59' THEN '04:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '04:30:00' AND '04:59:59' THEN '05:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '05:00:00' AND '05:29:59' THEN '05:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '05:30:00' AND '05:59:59' THEN '06:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '06:00:00' AND '06:29:59' THEN '06:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '06:30:00' AND '06:59:59' THEN '07:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '07:00:00' AND '07:29:59' THEN '07:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '07:30:00' AND '07:59:59' THEN '08:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '08:00:00' AND '08:29:59' THEN '08:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '08:30:00' AND '08:59:59' THEN '09:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '09:00:00' AND '09:29:59' THEN '09:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '09:30:00' AND '09:59:59' THEN '10:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '10:00:00' AND '10:29:59' THEN '10:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '10:30:00' AND '10:59:59' THEN '11:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '11:00:00' AND '11:29:59' THEN '11:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '11:30:00' AND '11:59:59' THEN '12:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '12:00:00' AND '12:29:59' THEN '12:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '12:30:00' AND '12:59:59' THEN '13:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '13:00:00' AND '13:29:59' THEN '13:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '13:30:00' AND '13:59:59' THEN '14:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '14:00:00' AND '14:29:59' THEN '14:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '14:30:00' AND '14:59:59' THEN '15:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '15:00:00' AND '15:29:59' THEN '15:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '15:30:00' AND '15:59:59' THEN '16:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '16:00:00' AND '16:29:59' THEN '16:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '16:30:00' AND '16:59:59' THEN '17:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '17:00:00' AND '17:29:59' THEN '17:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '17:30:00' AND '17:59:59' THEN '18:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '18:00:00' AND '18:29:59' THEN '18:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '18:30:00' AND '18:59:59' THEN '19:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '19:00:00' AND '19:29:59' THEN '19:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '19:30:00' AND '19:59:59' THEN '20:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '20:00:00' AND '20:29:59' THEN '20:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '20:30:00' AND '20:59:59' THEN '21:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '21:00:00' AND '21:29:59' THEN '21:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '21:30:00' AND '21:59:59' THEN '22:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '22:00:00' AND '22:29:59' THEN '22:30'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '22:30:00' AND '22:59:59' THEN '23:00'
WHEN dbo.VW_HELPDESK_ALL_CALLS_PART_2.OPEN_TIME BETWEEN '23:00:00' AND '23:29:59' THEN '23:30'
ELSE Cast('24:00' AS nvarchar(25)) END HALF_HOUR
FROM dbo.VW_HELPDESK_ALL_CALLS_PART_2
INNER JOIN
dbo.VW_Remedy_Helpdesk ON dbo.VW_HELPDESK_ALL_CALLS_PART_2.CASE_ID = dbo.VW_Remedy_Helpdesk.Case_ID
LEFT OUTER JOIN
dbo.SYS_Case_Age_Lookup ON dbo.VW_HELPDESK_ALL_CALLS_PART_2.CASE_AGE_IN_DAYS = dbo.SYS_Case_Age_Lookup.CaseAgeinDays
***** VW_HELPDESK_ALL_CALLS_PART_3 (Part 4)
October 27, 2008 at 1:50 pm
Hello Sir,
How did you figure out what the columns in table t374 were
ie... C700002200 is Closed_date?
Thanks,
Carl
October 27, 2008 at 3:20 pm
First, we can simplify your case statement with the following:
Declare @openTime Datetime;
Set @openTime = '2008-10-27 17:25';
Select dateadd(hour, datediff(hour, 0, @openTime) + 1, 0) As end_hour
,case when datepart(minute, @openTime) >= 30
then dateadd(hour, datediff(hour, 0, @openTime) + 1, 0)
else dateadd(minute, 30, dateadd(hour, datediff(hour, 0, @openTime), 0))
end As half_hour
You are also returning your date columns as character strings instead of actual dates. This is causing part of the problem when you later try filtering on the dates.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2008 at 3:27 pm
cpetersen (10/27/2008)
Hello Sir,How did you figure out what the columns in table t374 were
ie... C700002200 is Closed_date?
Thanks,
Carl
Considering the OP hasn't logged on in over a year, I have a feeling this particular question will go unanswered.
October 27, 2008 at 3:34 pm
A couple of other things to simplify the queries:
1) Alias the table/view references - use aliases in select and joins
Example:
SELECT t1.col1
,t2.col1
,t1.col2
FROM dbo.MyBigTableNameHere AS t1
JOIN dbo.MyOtherBigTableName AS t2 ON t2.key = t1.key
2) Do not use '*' - specify all columns in the query.
3) You are adding seconds to the seed date '1970-01-01' and converting the result to char(19). Convert the result to datetime instead as that will be easier for later date manipulation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2008 at 3:36 pm
Garadin (10/27/2008)
cpetersen (10/27/2008)
Hello Sir,How did you figure out what the columns in table t374 were
ie... C700002200 is Closed_date?
Thanks,
Carl
Considering the OP hasn't logged on in over a year, I have a feeling this particular question will go unanswered.
Thanks - didn't even see the original posters date. Guess I should look at that before posting 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply