SELECT
CONCAT(
CONCAT("--------------------- Direct -------------- Indirect ----------- Admin -------------- Total"),"
",
CONCAT("Units --------------- ", a.Direct, " -----------------", b.Indirect, "------------------ " , c.Admin , "------------------ " , (a.Direct+b.Indirect+c.Admin)), "
",
CONCAT("Amount ----------- ","$", (a.Direct*32.5), " -------------","$", (b.Indirect*32.5), "----------- " ,"$", (c.Admin*32.5) , "----------- " , "$", ((a.Direct+b.Indirect+c.Admin)*32.5)), "
",
CONCAT(" Percentage admin time over total time ------------ ",ROUND((c.Admin/(a.Direct+b.Indirect+c.Admin))*100)),"%","
"
) "Billing type"
FROM
(
select sum(billing_unit) as Direct
from billingmaster
where dx_code1 = 'L23'
and billing_code ='98010'
and practitioner_no ='{ohip_no}'
and billing_unit <> 0
and service_date between REPLACE('{start}','-','') and REPLACE('{finish}','-','')
)a,
(
select sum(billing_unit) as Indirect
from billingmaster
where dx_code1 = 'L23'
and billing_code ='98011'
and practitioner_no ='{ohip_no}'
and billing_unit <> 0
and service_date between REPLACE('{start}','-','') and REPLACE('{finish}','-','')
)b,
(
select COALESCE(sum(billing_unit), 0) as Admin
from billingmaster
where dx_code1 = 'L23'
and billing_code ='98012'
and practitioner_no ='{ohip_no}'
and billing_unit <> 0
and service_date between REPLACE('{start}','-','') and REPLACE('{finish}','-','')
)c
SELECT ohip_no, concat(last_name,',',first_name,'(',provider_no,')')
FROM provider
WHERE status='1'
AND provider_type='doctor'
AND ohip_no>1
ORDER BY provider_no
;