PBI queries 1

Quarantined Records:

WITH datarange as (SELECT run_id from ddu_quarantine.batch_log where time > current_date() - 7 )

,alltable AS (SELECT ddu_mode, ddu_error, ddu_run_id, name as KeyValue, 'name' as KeyColumn, 'BusinessUnit' AS TableName FROM ddu_quarantine.BusinessUnit UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_messagecode as KeyValue, 'new_messagecode' as KeyColumn, 'new_certmessagetext' AS TableName FROM ddu_quarantine.new_certmessagetext UNION ALL
SELECT ddu_mode, ddu_error, ddu_run_id, domainname as KeyValue, 'domainname' as KeyColumn, 'SystemUser' AS TableName FROM ddu_quarantine.SystemUser UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_mwaid as KeyValue, 'new_mwaid' as KeyColumn, 'contact' AS TableName FROM ddu_quarantine.contact UNION ALL
SELECT ddu_mode, ddu_error, ddu_run_id, new_id as KeyValue, 'new_id' as KeyColumn, 'new_certmessage' AS TableName FROM ddu_quarantine.new_certmessage UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_brokeragedetail' AS TableName FROM ddu_quarantine.new_brokeragedetail UNION ALL
SELECT ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_dstaccountdetail' AS TableName FROM ddu_quarantine.new_dstaccountdetail UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_varannuitydetail' AS TableName FROM ddu_quarantine.new_varannuitydetail UNION ALL
SELECT ddu_mode, ddu_error, ddu_run_id, name as KeyValue, 'name' as KeyColumn, 'Territory' AS TableName FROM ddu_quarantine.Territory UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_plancode as KeyValue, 'new_plancode' as KeyColumn, 'new_plantypes' AS TableName FROM ddu_quarantine.new_plantypes UNION ALL
SELECT ddu_mode, ddu_error, ddu_run_id, name as KeyValue, 'name' as KeyColumn, 'Team' AS TableName FROM ddu_quarantine.Team UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_memberproduct' AS TableName FROM ddu_quarantine.new_memberproduct UNION ALL
SELECT ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_memberproduct' AS TableName FROM ddu_quarantine.new_memberproductupdate UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_abcbilling' AS TableName FROM ddu_quarantine.new_abcbilling UNION ALL
SELECT ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_abcrelatedcerts' AS TableName FROM ddu_quarantine.new_abcrelatedcerts UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_agentlicense' AS TableName FROM ddu_quarantine.new_agentlicense UNION ALL
SELECT ddu_mode, ddu_error, ddu_run_id, new_name as KeyValue, 'new_name' as KeyColumn, 'new_beneficiary' AS TableName FROM ddu_quarantine.new_beneficiary UNION ALL
SELECT
ddu_mode, ddu_error, ddu_run_id, new_addressstring as KeyValue, 'new_addressstring' as KeyColumn, 'new_household' AS TableName FROM ddu_quarantine.new_householdinsert
)

SELECT * FROM alltable a INNER JOIN datarange d on a.__ddu_run_id = d.run_id