/* Script to extract the Findings information from the DWH database
  
   Date: 8th August 2020
*/
SELECT dfi.FindingNumber
    ,dfi.FindingRiskName
    ,dfi.FindingRiskScore
    ,dfi.FindingText AS [Finding Description]
    ,dfs.FindingStatusName AS [Status]
    ,dou.OrgUnitName AS [OrgUnit]
    ,dme.EntityName AS [Entity]
    ,det.EntityTypeName AS [EntityType]
    ,de.EventNumber AS [Event ID]
    ,de.EventName AS [Event Name]
FROM DimFinding dfi
LEFT JOIN DimFindingCustomField dfcf ON dfi.DimFindingId = dfcf.DimFindingId
LEFT JOIN DimFindingStatus dfs ON dfi.DimFindingStatusId = dfs.DimFindingStatusId
LEFT JOIN DimEventSegment devs ON dfi.DimEventSegmentId = devs.DimEventSegmentId
LEFT JOIN DimEventSection dese ON devs.DimEventSectionId = dese.DimEventSectionId
LEFT JOIN DimEvent de ON dese.DimEventId = de.DimEventId
LEFT JOIN DimOrgUnit dou ON de.DimOrgUnitId = dou.DimOrgUnitId
LEFT JOIN DimEntity dme ON de.DimEntityId = dme.DimEntityId
LEFT JOIN BridgeEntityEntityType bet ON bet.DimEntityId = de.DimEntityId
LEFT JOIN DimEntityType det ON det.DimEntityTypeId = bet.DimEntityTypeId
GROUP BY dfi.FindingNumber
    ,dfi.FindingRiskName
    ,dfi.FindingRiskScore
    ,dfi.FindingText
    ,dfcf.FindingCustomFieldValue
    ,dfs.FindingStatusName
    ,dou.OrgUnitName
    ,dme.EntityName
    ,de.EventNumber
    ,de.EventName
    ,det.EntityTypeName