USE [Tfs_BeDev]
GO

/****** Object:  View [dbo].[vw_denorm_WorkItemCoreLatest]    Script Date: 21/07/2016 16:55:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************************
** Warning: the contents of this view are critical to its functioning.
** Modifying the contents of view could result in data corruption, performance issues,
** or other bugs in your Team Foundation Server deployment.
** DO NOT MODIFY IT.
******************************************************************************************************/
-- Hash: 45BF9CD86418AD5A60AB4806A5582B08E32E1BA6
ALTER VIEW [dbo].[vw_denorm_WorkItemCoreLatest]
AS
SELECT W.Id                                     AS [System.Id],
       W.Rev                                    AS [System.Rev],
       W.AuthorizedDate                         AS [System.AuthorizedDate],
       W.RevisedDate                            AS [System.RevisedDate],
       W.WorkItemType                           AS [System.WorkItemType],
       W.CreatedDate                            AS [System.CreatedDate],
       W.ChangedDate                            AS [System.ChangedDate],
       W.State                                  AS [System.State],
       W.Reason                                 AS [System.Reason],
       W.AuthorizedAs                           AS [System.PersonId],
       W.Watermark                              AS [System.Watermark],
       W.AreaId                                 AS [System.AreaId],
       W.IterationId                            AS [System.IterationId],
       Tree.TeamProject                         AS [System.TeamProject],
       Tree.NodeName                            AS [System.NodeName],
       Tree.IterationLevel7                     AS [System.IterationLevel7],
       Tree.IterationLevel6                     AS [System.IterationLevel6],
       Tree.IterationLevel5                     AS [System.IterationLevel5],
       Tree.IterationLevel4                     AS [System.IterationLevel4],
       Tree.IterationLevel3                     AS [System.IterationLevel3],
       Tree.IterationLevel2                     AS [System.IterationLevel2],
       Tree.IterationLevel1                     AS [System.IterationLevel1],
       Tree.AreaLevel7                          AS [System.AreaLevel7],
       Tree.AreaLevel6                          AS [System.AreaLevel6],
       Tree.AreaLevel5                          AS [System.AreaLevel5],
       Tree.AreaLevel4                          AS [System.AreaLevel4],
       Tree.AreaLevel3                          AS [System.AreaLevel3],
       Tree.AreaLevel2                          AS [System.AreaLevel2],
       Tree.AreaLevel1                          AS [System.AreaLevel1],
       Tree.AreaPath                            AS [System.AreaPath],
       Tree2.IterationPath                      AS [System.IterationPath],
       ISNULL(AttachedFileCount.FileCount, 0)   AS [System.AttachedFileCount],
       ISNULL(HyperlinkCount.FileCount, 0)      AS [System.HyperlinkCount],
       ISNULL(ExternalLinkCount.FileCount, 0)   AS [System.ExternalLinkCount],
       ISNULL(RelatedLinkCount.RelatedCount, 0) AS [System.RelatedLinkCount],
       C_AssignedTo.DisplayPart                 AS [System.AssignedTo],
       C_AssignedTo.TeamFoundationId            AS [System.AssignedTo_TeamFoundationId],
       C_AssignedTo.ConstId                     AS [System.AssignedTo_ConstId],
       C_CreatedBy.DisplayPart                  AS [System.CreatedBy],
       C_CreatedBy.TeamFoundationId             AS [System.CreatedBy_TeamFoundationId],
       C_CreatedBy.ConstId                      AS [System.CreatedBy_ConstId],
       C_ChangedBy.DisplayPart                  AS [System.ChangedBy],
       C_ChangedBy.TeamFoundationId             AS [System.ChangedBy_TeamFoundationId],
       C_ChangedBy.ConstId                      AS [System.ChangedBy_ConstId],
       C_AuthorizedAs.DisplayPart               AS [System.AuthorizedAs],
       C_AuthorizedAs.TeamFoundationId          AS [System.AuthorizedAs_TeamFoundationId],
       C_AuthorizedAs.ConstId                   AS [System.AuthorizedAs_ConstId],
	     0 as IsDeleted, -- ADDED THIS LINE
       W.*
FROM dbo.tbl_WorkItemCoreLatest W
LEFT JOIN dbo.tbl_ClassificationNodePath Tree
    ON Tree.PartitionId = W.PartitionId
    AND Tree.DataspaceId = W.DataspaceId
    AND Tree.ID = W.AreaId
LEFT JOIN dbo.tbl_ClassificationNodePath Tree2
    ON Tree2.PartitionId = W.PartitionId
    AND Tree2.DataspaceId = W.DataspaceId
    AND Tree2.ID = W.IterationId
LEFT JOIN dbo.vw_WorkItemFileCountLatest AttachedFileCount
    ON AttachedFileCount.PartitionId = W.PartitionId
    AND AttachedFileCount.DataspaceId = W.DataspaceId
    AND AttachedFileCount.Id = W.Id
    AND AttachedFileCount.FieldId = 50
LEFT JOIN dbo.vw_WorkItemFileCountLatest HyperlinkCount
    ON HyperlinkCount.PartitionId = W.PartitionId
    AND HyperlinkCount.DataspaceId = W.DataspaceId
    AND HyperlinkCount.Id = W.Id
    AND HyperlinkCount.FieldId = 51
LEFT JOIN dbo.vw_WorkItemFileCountLatest ExternalLinkCount
    ON ExternalLinkCount.PartitionId = W.PartitionId
    AND ExternalLinkCount.DataspaceId = W.DataspaceId
    AND ExternalLinkCount.Id = W.Id
    AND ExternalLinkCount.FieldId = 58
LEFT JOIN dbo.vw_WorkItemRelatedLinkCountLatest RelatedLinkCount
    ON RelatedLinkCount.PartitionId = W.PartitionId
    AND RelatedLinkCount.DataspaceId = W.DataspaceId
    AND RelatedLinkCount.Id = W.Id
LEFT JOIN dbo.Constants C_AssignedTo
    ON C_AssignedTo.PartitionId = W.PartitionId
    AND C_AssignedTo.ConstId = W.AssignedTo
LEFT JOIN dbo.Constants C_CreatedBy
    ON C_CreatedBy.PartitionId = W.PartitionId
    AND C_CreatedBy.ConstId = W.CreatedBy
LEFT JOIN dbo.Constants C_ChangedBy
    ON C_ChangedBy.PartitionId = W.PartitionId
    AND C_ChangedBy.ConstId = W.ChangedBy
LEFT JOIN dbo.Constants C_AuthorizedAs
    ON C_AuthorizedAs.PartitionId = W.PartitionId
    AND C_AuthorizedAs.ConstId = W.AuthorizedAs

GO