I have 4 columns that i sum on, 3 of which need to be hidden. in my 5th column (not being summed) i want to display the result of dividing 2 of the hidden columns. If i hide the 3 columns i get incorrect data. If the columns are unhidden the data looks fine.
select 'dbr.crosstab', 3; select 'dbr.crosstab.col', mo, mo1, mo2, mo3, mo4, mo5, mo6; select 'dbr.hdr', 1; select 'dbr.sum', 4, 6, 7, 8; select 'dbr.colstyle', 5, '%.2f%%'; select 'dbr.summary.calc', 5, 'round((c7/c8)*100,2)'; /*coverages over total sales per month*/ select 'dbr.hidecolumns', 3;
select v.generic as 'Generic' , v.description as 'Coverage' , concat(year(c.activity_purchase_dt), if(month(c.activity_purchase_dt)<10, '/0', '/'), month(c.activity_purchase_dt)) as 'Date' , count(1) as 'Deals' , (a.c / b.c)* 100 as '%' , a.c as 'Cov C' /*coverage count per month*/ , b.c as 'Gen C' /*generic deals count per month*/ , c.c as 'All C' /*all deal count per month*/ from dw_auto.client c inner join dw_auto.coverage v on v.plan = c.coverage /*count of coverages per month*/ left outer join (select c.coverage as 'cov' , concat(year(c.activity_purchase_dt), if(month(c.activity_purchase_dt)<10, '/0', '/'), month(c.activity_purchase_dt)) as 'd' , count(1) as 'c' from dw_auto.client c where c.clear_dt is null and c.current_status = 'Funded' group by 1, 2) as a on a.cov = c.coverage and a.d = concat(year(c.activity_purchase_dt), if(month(c.activity_purchase_dt)<10, '/0', '/'), month(c.activity_purchase_dt)) /*count of generics per month*/ left outer join (select v.generic as 'gen' , concat(year(c.activity_purchase_dt), if(month(c.activity_purchase_dt)<10, '/0', '/'), month(c.activity_purchase_dt)) as 'd' , count(1) as 'c' from dw_auto.client c inner join dw_auto.coverage v on v.plan = c.coverage where c.clear_dt is null and c.current_status = 'Funded' group by 1, 2) as b on b.gen = v.generic and b.d = concat(year(c.activity_purchase_dt), if(month(c.activity_purchase_dt)<10, '/0', '/'), month(c.activity_purchase_dt)) /*all deals*/ left outer join (select concat(year(c.activity_purchase_dt), if(month(c.activity_purchase_dt)<10, '/0', '/'), month(c.activity_purchase_dt)) as 'd' , count(1) as 'c' from dw_auto.client c where c.clear_dt is null and c.current_status = 'Funded' group by 1) as c on c.d = concat(year(c.activity_purchase_dt), if(month(c.activity_purchase_dt)<10, '/0', '/'), month(c.activity_purchase_dt)) where c.clear_dt is null and c.activity_purchase_dt >= '2012-01-01' and v.generic <> 'none' and c.current_status = 'Funded' group by 1, 2, 3 order by 1, 4 desc;
