/*
 * Broadcast Raw Data
 *
 * Data Source: YellowfinMetaDataReadOnly
 * View: Admin Audit View
 * 2019-08-20 12:40:08
 */

SELECT DISTINCT TOP 10000 
   "Organisation_DataSource"."OrgName",
   "Report Broadcast"."UserName",
   "Report Broadcast"."BroadcastTypeCode",
   "Report Broadcast"."DeliveryRuleTypeCode",
   "Report Broadcast"."RequestTypeCode",
   "Report Broadcast"."BroadcastCode",
   "Report Broadcast"."StatusNotificationCode",
   "Report"."ReportName",
   "Content Folder"."ContentTypeCode",
   "Content Folder"."ContentCode",
   "Report Broadcast"."SubjectLine",
   "Report Broadcast"."ReportId",
   "Report Broadcast"."BroadcastId",
   "Report Broadcast"."LastModifiedDate",
   "Report Broadcast"."EndDate",
   "Report Broadcast"."StartDate",
   "Report Broadcast Result"."BroadcastSuccess",
   "Report Broadcast Result"."ResultCode",
   "Report Broadcast Result"."EmailAddress",
   MAX("Report Broadcast Result"."BroadcastTimestamp"),
   "Report Broadcast Result"."RecipientTypeCode"
FROM "ReportHeader" AS "Report"
LEFT OUTER JOIN "ReportBroadcast" AS "Report Broadcast"
ON (
   "Report"."ReportId" = "Report Broadcast"."ReportId"
)
LEFT OUTER JOIN "ContentManagement" AS "Content Folder"
ON (
   "Report"."CategoryCode" = "Content Folder"."ContentTypeCode"
   AND "Report"."SubCategoryCode" = "Content Folder"."ContentCode"
)
LEFT OUTER JOIN "ReportBroadcastResult" AS "Report Broadcast Result"
ON (
   "Report Broadcast"."BroadcastId" = "Report Broadcast Result"."BroadcastId"
)
LEFT OUTER JOIN "ReportView" AS "View"
ON (
   "Report"."ViewId" = "View"."ViewId"
)
LEFT OUTER JOIN "ReportViewSource" AS "Data Source"
ON (
   "View"."SourceId" = "Data Source"."SourceId"
)
LEFT OUTER JOIN "dbo"."Organisation" AS "Organisation_DataSource"
ON (
   "Data Source"."IpOrg" = "Organisation_DataSource"."IpOrg"
)
WHERE (
   "Report"."RoleCode" != N'SUBQUERY'
)
AND (
   "Report Broadcast"."BroadcastId" IS NOT NULL
)
GROUP BY 
   "Report Broadcast Result"."BroadcastSuccess",
   "Report Broadcast Result"."ResultCode",
   "Report Broadcast"."ReportId",
   "Report Broadcast"."DeliveryRuleTypeCode",
   "Report Broadcast"."BroadcastId",
   "Report Broadcast"."EndDate",
   "Report Broadcast"."UserName",
   "Report Broadcast"."BroadcastCode",
   "Report Broadcast"."SubjectLine",
   "Report Broadcast"."RequestTypeCode",
   "Content Folder"."ContentTypeCode",
   "Report Broadcast"."LastModifiedDate",
   "Report"."ReportName",
   "Report Broadcast Result"."RecipientTypeCode",
   "Organisation_DataSource"."OrgName",
   "Report Broadcast"."BroadcastTypeCode",
   "Content Folder"."ContentCode",
   "Report Broadcast Result"."EmailAddress",
   "Report Broadcast"."StartDate",
   "Report Broadcast"."StatusNotificationCode"