Difficult problem or simple solution? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Difficult problem or simple solution?

Author

Topic

vortistic
Starting Member

3 Posts

Posted - 2007-11-14 : 14:18:17

Hi! I am rather new to SQL, and could use some help. I have a table with lots of records, with fields like this:

TableA.Batch
TableA.Wafer
TableA.DieID
TableA.Param
TableA.Value

For each batch, there are ~25 wafers. For each wafer, there are thousands of dies. For each die there are several parameters, and for each die and parameter there is a value.

I want to calculate the median and robust sigma (inter-quartile range/1.35) per wafer, for a parameter. The following code works perfectly for me:

DECLARE @Q1 FLOAT, @MEDIAN FLOAT, @Q3 FLOAT, @SIGMA FLOAT, @x INT, @y INT, @z INT

SET @Q1 = SELECT TOP 1 value FROM (
	SELECT TOP 25 PERCENT value
	FROM TableA
	WHERE batch=@x
	AND wafer=@y
	AND param=@z
	ORDER BY value) Quartile1
ORDER BY value DESC


SET @MEDIAN = SELECT TOP 1 value FROM (
	SELECT TOP 50 PERCENT value
	FROM TableA
	WHERE batch=@x
	AND wafer=@y
	AND param=@z
	ORDER BY value) Median
ORDER BY value


SET @Q3 = SELECT TOP 1 value FROM (
	SELECT TOP 25 PERCENT value
	FROM TableA
	WHERE batch=@x
	AND wafer=@y
	AND param=@z
	ORDER BY value DESC) Quartile3
ORDER BY value

SET @SIGMA = (@Q3-@Q1)/1.35

SELECT @MEDIAN, @SIGMA

Ok, if you are still there: I need to do this for hundreds of wafers, and hopefully without having to manually set the batch and wafer numbers. I'm using MS SQL server by the way. As far as I've understood, there is no for-loop in SQL, and I can't see how I can do this by using GROUP BY.

Any help is very appreciated.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-11-14 : 14:39:50

Below is one way not the most efficient but it will work.

Declare GetData cursor for
select batch, wafer from TableA
OPEN GetData;
FETCH NEXT FROM GetData INTO @x, @y;
WHILE @@FETCH_STATUS >= 0
BEGIN
--------- DO YOUR CODE HERE
FETCH NEXT FROM GetData INTO @x, @y;
END
close GetData
deallocate GetData

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

vortistic
Starting Member

3 Posts

Posted - 2007-11-14 : 15:27:17

Thank you.
So does the GetData cursor have to be filled with something? How does it know which x and y's to use?

EDIT: Wait, I figured it out. I can write the following, right?

Declare GetData cursor for 
select distinct batch, wafer from TableA where batch>27000

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-11-14 : 15:38:47

Yes
Or you can try this

select
batch,
wafer,
param,
MEDIAN = (SELECT TOP 1 value FROM (
SELECT TOP 50 PERCENT value
FROM TableA
WHERE batch=a.batch
AND wafer=a.wafer
AND param=a.param
ORDER BY value) Median ),
SIGMA = (((SELECT TOP 1 value FROM (
SELECT TOP 25 PERCENT value
FROM TableA
WHERE batch=a.batch
AND wafer=a.wafer
AND param=a.param
ORDER BY value DESC) Quartile3 ) - (SELECT TOP 1 value FROM (
SELECT TOP 25 PERCENT value
FROM TableA
WHERE batch=a.batch
AND wafer=a.wafer
AND param=a.param
ORDER BY value) Quartile1 )) / 1.35 )
from TableA a

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-14 : 20:40:16

Are you using SQL Server 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

vortistic
Starting Member

3 Posts

Posted - 2007-11-15 : 15:45:49

thanks again, jhocutt!
your solution worked flawlessly. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources