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
Telegram: DB developers channel