SELECT t.id,
t.group_id,
t.name,
t.task_count,
t.status,
t.project_id,
t.send_velocity,
to_char(t.create_time, 'YYYY-MM-DD HH24:MI'),
to_char(t.send_date, 'YYYY-MM-DD HH24:MI'),
u.login_id,
s.successcount,
s.softbouncecount,
s.hardbouncecount,
s.sendedfailcount,
s.sendedcount,
s.opencount,
s.sumopencount,
s.clickcount,
s.sumclickcount,
s.registercount,
CASE
WHEN s.sendedcount != 0 THEN
ROUND(s.successcount / s.sendedcount * 100, 2)
ELSE
0
END successcountrate,
CASE
WHEN s.sendedcount != 0 THEN
ROUND(s.sendedfailcount / s.sendedcount * 100, 2)
ELSE
0
END sendedfailcountrate,
CASE
WHEN s.sendedcount != 0 THEN
ROUND(s.softbouncecount / s.sendedcount * 100, 2)
ELSE
0
END softbouncecountrate,
CASE
WHEN s.sendedcount != 0 THEN
ROUND(s.hardbouncecount / s.sendedcount * 100, 2)
ELSE
0
END hardbouncecountrate,
CASE
WHEN s.successcount != 0 THEN
ROUND(s.opencount / s.successcount * 100, 2)
ELSE
0
END opencountrate,
CASE
WHEN s.successcount != 0 THEN
ROUND(s.sumopencount / s.successcount * 100, 2)
ELSE
0
END sumopencountrate,
CASE
WHEN s.successcount != 0 THEN
ROUND(s.clickcount / s.successcount * 100, 2)
ELSE
0
END clickcountrate,
CASE
WHEN s.successcount != 0 THEN
ROUND(s.sumclickcount / s.successcount * 100, 2)
ELSE
0
END sumclickcountrate,
CASE
WHEN t.task_count != 0 THEN
ROUND(s.sendedcount / t.task_count * 100, 2)
ELSE
0
END sendedrate,
CASE
WHEN t.task_count != 0 THEN
ROUND(s.registercount / s.clickcount * 100, 2)
ELSE
0
END registercountrate
FROM tasks t,
users u,
(SELECT SUM(
CASE
WHEN active > 0
AND active != 2 THEN 1
ELSE 0
END) successcount,
SUM(
CASE
WHEN(active > -5000
AND active < -500) OR active = -1 OR active = -9999 OR active < -20000 THEN 1
ELSE 0
END) softbouncecount,
SUM(
CASE
WHEN(active > -500
AND active < -50) OR active = -10001 OR active = -10002 THEN 1
ELSE 0
END) hardbouncecount,
SUM(
CASE
WHEN active < 0 THEN 1
ELSE 0
END) sendedfailcount,
SUM(
CASE
WHEN active != 0
AND active != 2 THEN 1
ELSE 0
END) sendedcount,
SUM(
CASE
WHEN
OPEN > 0 THEN 1
ELSE 0
END) opencount,
SUM(
CASE
WHEN
OPEN > 0 THEN nvl(
OPEN, 0)
ELSE 0
END) sumopencount,
SUM(
CASE
WHEN click > 0 THEN nvl(
OPEN, 0)
ELSE 0
END) clickcount,
SUM(
CASE
WHEN click > 0 THEN nvl(click, 0)
ELSE 0
END) sumclickcount,
SUM(
CASE
WHEN emigration IS NOT NULL THEN nvl(click, 0)
ELSE 0
END) registercount
FROM task_emails_143)
s
WHERE t.user_id = u.id
AND t.id = '143'