Database Reference
In-Depth Information
Accessing data using Microsoft Excel query code
Use
DWDataMart
:
GO
CREATE VIEW v_Custom_r_AllIncidents AS
SELECT
I.Id AS [ID],
CONVERT(smalldatetime, I.CreatedDate) AS [Created Date],
IStatusDS.DisplayName AS [Status],
AffectedUser.DisplayName AS [Affected User],
I.Title AS [Title],
I.Description AS [Description],
IImpactDS.DisplayName AS [Impact],
IUrgencyDS.DisplayName AS [Urgency],
I.Priority AS [Priority],
ISourceDS.DisplayName AS [Source],
ITierQueueDS.DisplayName [Support Group],
AssignedTo.DisplayName AS [Assigned To],
IClassificationDS.DisplayName AS [Classification Category],
CONVERT(smalldatetime, I.ResolvedDate) AS [Resolved Date],
IResolutionCategoryDS.DisplayName AS [Resolution Category],
I.ResolutionDescription AS [Resolution Description],
CONVERT(smalldatetime, I.ClosedDate) AS [Closed Date],
CreatedBy.DisplayName AS [Created By],
ResolvedBy.DisplayName AS [Resolved By]
FROM
IncidentDimvw I
LEFT OUTER JOIN WorkItemDimvw WI ON
I.EntityDimKey = WI.EntityDimKey
LEFT OUTER JOIN WorkItemAffectedUserFactvw ON
WI.WorkItemDimKey =
WorkItemAffectedUserFactvw.WorkItemDimKey
AND WorkItemAffectedUserFactvw.DeletedDate IS NULL
