/*
This is a sample script to extract Event Information using CMO DWH tables. 

DATE: 24-July-2020
*/
SELECT de.EventNumber AS [EventID]
    ,do.OrgUnitName AS [OrgUnit]
    ,dent.EntityName AS [Entity]
    ,substring((
            SELECT ', ' + dety.EntityTypeName
            FROM DimEntityType dety
            LEFT JOIN BridgeEntityEntityType beety ON beety.DimEntityTypeId = dety.DimEntityTypeId
            WHERE beety.DimEntityId = de.DimEntityId
            GROUP BY dety.EntityTypeName
            FOR XML path('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 3, 4000) AS [EntityType]
    ,de.EventName AS [Event Title]
    ,devt.EventTemplateName AS [Form]
    ,dcusr.FirstName + ' ' + dcusr.LastName AS [Reporter / Auditor]
    ,FORMAT(de.EventDateTime, 'HH:mm dd MMM yyyy') AS [Event Date]
    ,substring((
            SELECT ', ' + devcat.EventTemplateCategoryName
            FROM BridgeEventClassCategorySeverity devc
            LEFT JOIN DimEventTemplateCategory devcat ON devcat.DimEventTemplateCategoryId = devc.DimEventTemplateCategoryId
            WHERE devc.DimEventId = de.DimEventId
            GROUP BY devcat.EventTemplateCategoryName
            FOR XML path('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 3, 4000) AS [Event Category]
    ,substring((
            SELECT ', ' + devtcl.EventTemplateClassName
            FROM BridgeEventClassCategorySeverity devcl
            LEFT JOIN DimEventTemplateClass devtcl ON devcl.DimEventTemplateClassId = devtcl.DimEventTemplateClassId
            WHERE devcl.DimEventID = de.DimEventId
            GROUP BY devtcl.EventTemplateClassName
            FOR XML path('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 3, 4000) AS [Event Class]
    ,substring((
            SELECT ', ' + dpuser.FirstName + ' ' + dpuser.Lastname + ' (' + it.InvolvementTypeName + ')'
            FROM DimEventPartyInvolved dpi
            LEFT JOIN DimCompanyUser dpuser ON dpuser.DimCompanyUserId = dpi.DimUserId
            LEFT JOIN DimInvolvementType it ON it.DimInvolvementTypeId = dpi.DimInvolvementTypeId
            WHERE dpi.DimEventId = de.DimEventId
            GROUP BY dpuser.FirstName
                ,dpuser.Lastname
                ,it.InvolvementTypeName
            FOR XML path('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 3, 4000) AS [Parties Involved] --Parties Involved
    --,max(rs.Score) as EventScore
    ,de.EventRiskName [Risk Level]
    ,de.EventRiskScore [Score]
    ,dest.EventStatusName
FROM DimEvent de
LEFT JOIN FactEventSectionSegmentField fev ON fev.DimEventId = de.DimEventId
LEFT JOIN DimOrgUnit do ON do.DimOrgUnitId = de.DimOrgUnitId
LEFT JOIN DimEntity dent ON dent.DimEntityId = de.DimEntityId
LEFT JOIN DimEventTemplate devt ON devt.DimEventTemplateId = de.DimEventTemplateId
LEFT JOIN DimUser dusr ON dusr.DimUserId = de.DimAuditorUserId
LEFT JOIN DimCompanyUser dcusr ON dusr.DimUserId = dcusr.DimCompanyUserId
LEFT JOIN DimEventStatus dest ON dest.DimEventStatusId = de.DimEventStatusId
GROUP BY de.DimEventId
    ,de.EventNumber
    ,do.OrgUnitName
    ,dent.EntityName
    ,de.EventName
    ,devt.EventTemplateName
    ,dcusr.FirstName + ' ' + dcusr.LastName
    ,de.EventDateTime
    ,dest.EventStatusName
    ,de.DimEntityId
    ,de.EventRiskName
    ,de.EventRiskScore