## Calculating Yield

 Author Message kabaari SSC Eights! Group: General Forum Members Points: 997 Visits: 411 Hi all,I'm running into an issue calculating yield. `;with totalcount as (select cast(count(sn) as decimal) as totalfrom completedUnit)select (100 - (cast(count(distinct sn) as decimal)/(select * from totalcount))*100) as 'yield'from productiondefect`The following works however, I need to group weekly and by operatorThis does not work`;with totalcount as (select cast(count(sn) as decimal) as totalfrom completedUnit)select (100 - (cast(count(distinct sn) as decimal)/(select * from totalcount))*100) as 'yield'from productiondefectwhere sn = (select c.sn from completedUnit as c join productiondefect as d on c.sn = d.sn where c.operator = 'Operator')` kabaari SSC Eights! Group: General Forum Members Points: 997 Visits: 411 Ok. I got the below to run. The formula returns weekly defects / overall production totals. I want to calculate weekly defect / weekly production. Is this possible?`;with totalcount as (select cast(count(sn) as decimal) as totalfrom completedUnitwhere MONTH(dateentered) = '10' and operator = 'operator')select (100 - (cast(count(distinct d.sn) as decimal)/(select * from totalcount))*100) as 'yield'from productiondefect as djoin completedUnit as c on d.sn = c.snwhere MONTH(c.dateentered) = '10' and c.operator = 'operator'GROUP BY DATEADD(wk, DATEDIFF(wk, 7, c.dateentered), 7)ORDER BY DATEADD(wk, DATEDIFF(wk, 7, c.dateentered), 7)` kabaari SSC Eights! Group: General Forum Members Points: 997 Visits: 411 Below are my results:yield94.400000000000000097.600000000000000098.400000000000000096.8000000000000000Now, I need to get rid of the zeros and show dates. Luis Cazares SSC Guru Group: General Forum Members Points: 95966 Visits: 21205 kabaari (10/24/2012)Below are my results:yield94.400000000000000097.600000000000000098.400000000000000096.8000000000000000Now, I need to get rid of the zeros and show dates.You should do formatting on the front end.But if you must do it here, an easy way is `CAST( yield AS decimal( 3,1))` Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 Luis Cazares SSC Guru Group: General Forum Members Points: 95966 Visits: 21205 kabaari (10/24/2012)Ok. I got the below to run. The formula returns weekly defects / overall production totals. I want to calculate weekly defect / weekly production. Is this possible?To help you, we need to know more about this. You should provide DDL and sample data.You can read how to post it and obtain better responses in the article linked in my signature. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 kabaari SSC Eights! Group: General Forum Members Points: 997 Visits: 411 thanks Luis.