Testing for Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Testing for Values

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-23 : 16:09:21

Can someone tell me how they might approach this problem: I am running Sigma Yield calculations on values in one table comparing the yield result to another table containing standard 6 sigma yields and Sigma values.

For Example:

SELECT (1-(@DefectOpp/(SUM(low_lip+high_lip+v_cuts+channel+leakage+torn+bubbles+Other)*
(parts_per_sheet*sht_per_stk*No_Stacks_run))))*100 As [Act_Yield]
FROM prod_data
WHERE
(Item_no = 'ISI0246')
Group by parts_per_sheet, sht_per_stk, No_Stacks_run

Tells me what the current yield is based on a 6-sigma calculation. Now i need to look values up in another table and compare it to the closest value found. Here would be an example result set from the query above:

Act_Yield
97.64705882352941176
99.97849462365591398
99.40828402366863905
99.55555555555555556

The other table (tbl_TheSigmaTable) would have values such as:

Sigma DPMO Yield
3.6 17864.0 98.20000
3.7 13903.0 98.60000
3.8 10724.0 98.90000
3.9 8198.0 99.18000
4.0 6210.0 99.38000
4.1 4661.0 99.53000
4.2 3467.0 99.65000
4.3 2555.0 99.74000
4.4 1866.0 99.81000
4.5 1350.0 99.87000
4.6 968.0 99.90000
4.7 687.0 99.93100
4.8 483.0 99.95200
4.9 337.0 99.96600
5.0 233.0 99.97700
5.1 159.0 99.98400
5.2 108.0 99.98920
5.3 72.0 99.99280
5.4 48.0 99.99520
5.5 32.0 99.99680
5.6 21.0 99.99790
5.7 13.0 99.99866
5.8 8.5 99.99915
5.9 5.4 99.99946
6.0 3.4 99.99966

Im looking to compare the value in the first table (Act_Yield) to the value in the second table (Yield) and select the sigma value that is the closest in yield.

For example, i would expect these results:

Act_Yield Sigma
97.64705882352941176 3.5
99.97849462365591398 5
99.40828402366863905 4
99.55555555555555556 4.1

Any thoughts on how i might generate a compare statement for this?

Thanks!!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-23 : 23:39:19

You can try by finding the min absolute value between the 2 Yield values.

I have written some code here, I am not sure how to join these tables so I have assumed the joins can only happen on the yield values where the difference between the 2 is the lowest, this could be very cpu intensive because of the use of the cross join on tbl_TheSigmaTable, I am hoping that this table is rather small - If this is the case then you can perhaps try something like this.

--*************************************************************************************

SELECT c.Act_Yield, c.Sigma, MIN(c.Yield_Diff)
FROM
(SELECT a.Act_Yield, b.Sigma, ABS((a.Act_Yield) - b.Yield) as Yield_Diff
FROM (SELECT (1-(@DefectOpp/(SUM(low_lip+high_lip+v_cuts+channel+leakage+torn+bubbles+Other)*
(parts_per_sheet*sht_per_stk*No_Stacks_run))))*100 As [Act_Yield]
FROM prod_data
WHERE
(Item_no = 'ISI0246')
Group by parts_per_sheet, sht_per_stk, No_Stacks_run) a
CROSS JOIN tbl_TheSigmaTable b) c
GROUP BY c.Act_Yield, c.Sigma

--**************************************************************************

But i think that the easiest would be to somehow solve it by finding the lowest absolute value per sigma
ie MIN(ABS(Yield1 - Yield2))

Duane.

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-24 : 08:47:57

Thanks for your input Duane.

I have narrowed my query by selecting only one record to make it easier to work with. Note modified WHERE Clause:
WHERE
(Item_no = 'ISI0246' AND work_order_no = 1203 AND stack_no = 30060)

I think your idea is along the right lines. In fact, I modified the query a bit so i can see the actual delta values and this is what i get:

Modified Query:

Declare @DefectOpp numeric (18,0)
Set @DefectOpp = 8

SELECT b.Sigma, MIN(ABS((a.Act_Yield) - b.Yield)) as Yield_Diff
FROM
(
SELECT (1-(@DefectOpp/(SUM(low_lip+high_lip+v_cuts+channel+leakage+torn+bubbles+Other)*
(parts_per_sheet*sht_per_stk*No_Stacks_run))))*100 As [Act_Yield]
FROM prod_data
WHERE
(Item_no = 'ISI0246' AND work_order_no = 1203 AND stack_no = 30060)
Group by parts_per_sheet, sht_per_stk, No_Stacks_run
) a
Cross join tbl_TheSigmaTable b
Group By b.Sigma

Results:
Sigma Yield_Diff
.1 91.0111111111111111
.2 89.4111111111111111
.3 87.6111111111111111
.4 85.5111111111111111
.5 83.2111111111111111
.6 80.7111111111111111
.7 77.9111111111111111
.8 74.9111111111111111
.9 71.7111111111111111
1.0 68.2111111111111111
1.1 64.6111111111111111
1.2 60.9111111111111111
1.3 57.0111111111111111
1.4 53.1111111111111111
1.5 49.1111111111111111
1.6 45.1111111111111111
1.7 41.2111111111111111
1.8 37.3111111111111111
1.9 33.6111111111111111
2.0 30.0111111111111111
2.1 26.5111111111111111
2.2 23.3111111111111111
2.3 20.3111111111111111
2.4 17.5111111111111111
2.5 15.0111111111111111
2.6 12.7111111111111111
2.7 10.6111111111111111
2.8 8.8111111111111111
2.9 7.2111111111111111
3.0 5.8111111111111111
3.1 4.6111111111111111
3.2 3.6111111111111111
3.3 2.7111111111111111
3.4 2.0111111111111111
3.5 1.4111111111111111
3.6 .9111111111111111
3.7 .5111111111111111
3.8 .2111111111111111
3.9 .0688888888888889
4.0 .2688888888888889
4.1 .4188888888888889
4.2 .5388888888888889
4.3 .6288888888888889
4.4 .6988888888888889
4.5 .7588888888888889
4.6 .7888888888888889
4.7 .8198888888888889
4.8 .8408888888888889
4.9 .8548888888888889
5.0 .8658888888888889
5.1 .8728888888888889
5.2 .8780888888888889
5.3 .8816888888888889
5.4 .8840888888888889
5.5 .8856888888888889
5.6 .8867888888888889
5.7 .8875488888888889
5.8 .8880388888888889
5.9 .8883488888888889
6.0 .8885488888888889

Here's what I get with out the sigma column:

Query:
SELECT MIN(ABS((a.Act_Yield) - b.Yield)) as Yield_Diff
FROM
(
SELECT (1-(@DefectOpp/(SUM(low_lip+high_lip+v_cuts+channel+leakage+torn+bubbles+Other)*
(parts_per_sheet*sht_per_stk*No_Stacks_run))))*100 As [Act_Yield]
FROM prod_data
WHERE
(Item_no = 'ISI0246' AND work_order_no = 1203 AND stack_no = 30060)
Group by parts_per_sheet, sht_per_stk, No_Stacks_run
) a
Cross join tbl_TheSigmaTable b

Results:

Yield_Diff
.0688888888888889

This would imply for this particular record the Sigma value (actually a 6 sigma value) should be 3.9 as per the table above. (1)Im still not sure how I would display this. And (2), I'm not sure how I would handle multiple records if i open the WHERE clause back up to just the item_no.

Thoughts??

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources