SQLize Online / PHPize Online  /  SQLtest Online

select a.description, b.request_dt, b.err_count from (select 'Accounts without a Package ID' description,'' as dt, 0 as count1 union all select 'Accounts without a Book ID' description,'' as request_dt, 0 as count1 union all select 'New Puerto Rico accounts not found on the Account Hold table' description,'' as request_dt, 0 as count1 union all select 'New replacement kit requests made within 19 days from last request' description,'' as request_dt, 0 as count1 ) as a full outer join ( SELECT 'Daily Error Report' AS Title, description, err_count, 'ERROR_COUNT' AS ERROR_COUNT FROM (SELECT 'Accounts without a Package ID' AS description, COUNT(*) AS err_count, cast(request_dt as varchar(20)) FROM conf.c_f_wmk_segment_history WHERE package_id IS NULL AND (cast(kx_create_dt as date) = cast('04-04-2023' as date) or cast(kx_update_dt as date) = cast('04-04-2023' as date)) and suppression_flag = 'Y' GROUP BY request_dt UNION select 'Accounts without a Book ID' AS description, COUNT(*) AS err_count,cast(request_dt as varchar(20)) FROM conf.c_f_wmk_segment_history WHERE nullif(book_id, '') IS NULL AND (cast(kx_create_dt as date) = cast('04-04-2023' as date) or cast(kx_update_dt as date) = cast('04-04-2023' as date)) and suppression_flag = 'Y' GROUP BY request_dt UNION select 'New Puerto Rico accounts not found on the Account Hold table' AS description, COUNT(*) AS err_count,cast(request_dt as varchar(20)) from conf.c_f_wmk_segment_history where PR_Hold_ind = 2 AND (cast(kx_create_dt as date)= cast('04-04-2023' as date) or cast(kx_update_dt as date) = cast('04-04-2023' as date)) GROUP BY request_dt UNION SELECT 'New replacement kit requests made within 19 days from last request' AS description, count(d_chid) as err_count, cast(request_dt as varchar(20)) from ( select distinct aa.chid as d_chid, request_dt from conf.c_f_wmk_segment_history aa join conf.c_f_membership_card_request cr on aa.chid = cr.chid join ( select chid, request_dt from conf.c_f_wmk_segment_history bb where bb.segment = 'REPLACE' and (datediff(dd,bb.kx_create_dt,cast('04-04-2023' as datetime)) >= 1 and datediff(dd,bb.kx_create_dt,cast('04-04-2023' as datetime)) <= 19) and bb.chid not in (select chid from conf.c_f_wmk_segment_history dd where (datediff(dd,dd.kx_create_dt,cast('04-04-2023' as datetime)) >= 1 and datediff(dd,dd.kx_create_dt,cast('04-04-2023' as datetime)) <= 8) and dd.segment = 'REPLACE' and dd.suppression_flag in ('Y', 'R')) GROUP BY bb.request_dt, bb.chid ) cc on aa.chid = cc.chid where cast(cr.crt_ts as date) = cast('04-04-2023' as date) and aa.segment = 'REPLACE' and aa.suppression_flag in ('Y', 'R') and (cast(aa.kx_create_dt as date) = cast('04-04-2023' as date) or cast(aa.kx_update_dt as date) = cast('04-04-2023' as date)) GROUP BY aa.request_dt, aa.chid ) yy ) zz GROUP BY yy.request_dt ) as b on a.description = b.description