hi everyone,
i have a query when ran in pgAdmin i get results, but its otherwise when i run the same query in metabase…
i get this error
No results were returned by the query.
this is the query im running
------------------
--statement report -customer
------------------
DROP TABLE IF EXISTS valid_clients;
DROP TABLE IF EXISTS valid_customers;
CREATE TEMP TABLE valid_clients AS (
select t.*, tier2+tier3+tier4 count from (
select distinct clients.id as client_id,
clients.name,
case when (registration_categories.name is null) then 'None' else registration_categories.name end as category_name,
(COALESCE((SELECT 1 WHERE EXISTS(SELECT 1 FROM public. ins_tier2_contrib where client = clients.id limit 1)), 0)) tier2
,(COALESCE((SELECT 1 WHERE EXISTS(SELECT 1 FROM public. ins_tier3_contrib where client = clients.id limit 1)), 0)) tier3
,(COALESCE((SELECT 1 WHERE EXISTS(SELECT 1 FROM public. ins_tier4_contrib where client = clients.id limit 1)), 0)) tier4
from public.client clients
left join public.client_products client_products on client_products.client = clients.id
left join public.products products on products.id = client_products.product
left join public.client_reg registration_info on registration_info.client = clients.id
left join public.reg_category registration_categories on registration_categories.id = registration_info.category
where clients.name not ilike ('%available%') and clients.name not ilike ('%simple sample%') and clients.name not ilike ('%test%') and clients.name not ilike ('%unassigned%')
group by clients.id, registration_categories.name
)t where tier2+tier3+tier4 > 0
);
CREATE TEMP TABLE valid_customers AS (
select
clients.client_id as client_id,
customers.id as customer_id,
clients.name as client_name,
clients.category_name as category_name
from
valid_clients clients
left join public.employment_current currents on currents.client = clients.client_id
join public.customer customers on customers.id = currents.customer
where customers.fullname not ilike '%available%' and customers.fullname not ilike '%customer%' and customers.fullname not ilike '%simple sample%' and customers.fullname not ilike '%test%'
group by clients.client_id, customers.id, clients.name, clients.category_name
order by clients.client_id
);
WITH customer_contact as (
select
customer_id,
sum(case when (has_email>0) then 1 else 0 end) as has_email,
sum(case when (has_mobile>0) then 1 else 0 end) as has_mobile,
sum(case when (has_telephone>0) then 1 else 0 end) as has_telephone,
sum(case when (has_mobile+has_telephone>0) then 1 else 0 end) as has_phone,
sum(case when (has_contact>0) then 1 else 0 end) as has_contact
from(
select
customers.customer_id as customer_id,
sum(case when (customer_contacts.email is not null or customer_contacts.email != '')
then 1
else 0
end) as has_email,
sum(case when (customer_contacts.mobile is not null or customer_contacts.mobile!= '')
then 1
else 0
end) as has_mobile,
sum(case when (customer_contacts.telephone is not null or customer_contacts.telephone != '')
then 1
else 0
end) as has_telephone,
sum(case when ((customer_contacts.email is not null or customer_contacts.email != '') or (customer_contacts.mobile is not null or customer_contacts.mobile!= '') or (customer_contacts.telephone is not null or customer_contacts.telephone != ''))
then 1
else 0
end) as has_contact
from
valid_customers customers
join public.customer_contact customer_contacts on customer_contacts.customer = customers.customer_id and status='active' and type!='mobile_money'
group by customers.customer_id
order by customers.customer_id)
tbl_contact
group by customer_id
),
sent_statement as (
select customers.customer_id
,coalesce(_customer_statement.sms_sent::integer, 0) sms_sent
,coalesce(_customer_statement.email_sent::integer, 0) email_sent
,case when (_customer_statement.sms_sent or _customer_statement.email_sent)
then 1 else 0 end notice_sent
from
valid_customers customers
left join _customer_statement
on _customer_statement.customer = customers.customer_id
and {{year}}
and {{quarter}}
--and _customer_statement.year = ?
--and _customer_statement.quarter=?
)
SELECT tbl.*,
100*tbl."Sent"::float/tbl."Number of Customers"::float "%Sent to Customers" from
(
SELECT
customers.category_name,
count(customers.customer_id) "Number of Customers",
sum(customer_contact.has_contact) "Has Contact",
sum(sent_statement.sms_sent) "Sent SMS",
sum(sent_statement.email_sent) "Sent Email"
,sum(sent_statement.notice_sent) "Sent"
,count(case when sent_statement.notice_sent = 0 then sent_statement.customer_id else null end) "Outstanding Customers"
from valid_customers customers
left join customer_contact on customer_contact.customer_id = customers.customer_id
left join sent_statement on sent_statement.customer_id = customers.customer_id
group by customers.category_name) tbl;