SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help! Sql needed to process the test data.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jazzclassics
Starting Member

3 Posts

Posted - 04/07/2013 :  04:47:43  Show Profile  Reply with Quote
Hello,

I have been writting SQL for two years and cannot see a solution to the following problem. Can anybody help me? Thanks a lot.

We have a test system that test production units, and output data to the TestData table in the database. The TestData table schema is like the follows. For each unit, it may pass the test system for multiple times, and each time the system generate a row of data in the table. 'Not Found' means the unit passed that time of test, no defect is found. If a defect is found the defect will be logged in the table.

The question is: I want to form a new table named TestRslt based on the TestData. The TestRslt table should be like follows, ie. each unit should have only one row of test result. For units tested with both Defect-A and Defect-B, only the row containing Defect-B will be appear in the result table. For a unit of which Defect-B is found in multiple tests, only the first time test data will be appear in the result table.

TestData
UnitID TestNumber Defect
unit-A 1 Not Found
unit-B 1 Defect-A
unit-B 2 Defect-B
unit-B 3 Not Found
unit-B 4 Defect-A
unit-C 1 Defect-B
unit-C 2 Defect-B
unit-C 3 Defect-A



TestRslt
UnitID TestNumber Defect
unit-A 1 Not Found
unit-B 2 Defect-B
unit-C 1 Defect-B

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/07/2013 :  21:07:49  Show Profile  Reply with Quote
Query like this:
select * from (
select
	*,
	row_Number() over 
		(
			partition by unitid 
			order by case when Defect = 'Not Found' then 1 else 0 end, Defect desc
		) as RN
from
	YourTable 
) s where RN = 1;
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/08/2013 :  02:03:05  Show Profile  Reply with Quote

DECLARE @TestData TABLE(UnitID VARCHAR(10), TestNumber INT, Defect VARCHAR(15))
INSERT INTO @TestData
SELECT 'unit-A', 1, 'Not Found' union all
SELECT 'unit-A', 2, 'Defect-A' union all
SELECT 'unit-B', 1 , 'Defect-A' union all
SELECT 'unit-B', 2 , 'Defect-B' union all
SELECT 'unit-B', 3 , 'Not Found' union all
SELECT 'unit-B', 4 , 'Defect-A' union all
SELECT 'unit-C', 1 , 'Defect-B' union all
SELECT 'unit-C', 2 , 'Defect-B' union all
SELECT 'unit-C', 3 , 'Defect-A'
/*TestRslt
UnitID TestNumber Defect
unit-A 1 Not Found
unit-B 2 Defect-B
unit-C 1 Defect-B*/
SELECT UnitID, TestNumber, Defect
FROM (SELECT *, ROW_NUMBER() OVER( PARTITION BY UnitID ORDER BY CASE WHEN Defect = 'Defect-B' THEN 1 ELSE 0 END DESC ) rn
		FROM @TestData
		WHERE Defect IN ( 'Defect-B', 'Not Found')
	) t
WHERE t.rn = 1;
Go to Top of Page

jazzclassics
Starting Member

3 Posts

Posted - 04/08/2013 :  22:19:53  Show Profile  Reply with Quote
quote:
Originally posted by James K

Query like this:
select * from (
select
	*,
	row_Number() over 
		(
			partition by unitid 
			order by case when Defect = 'Not Found' then 1 else 0 end, Defect desc
		) as RN
from
	YourTable 
) s where RN = 1;




Thank you very much James. I will try your method and report back after. My data model is much more complecated than the above one. So it takes some time. Thank you
Go to Top of Page

jazzclassics
Starting Member

3 Posts

Posted - 04/08/2013 :  22:21:17  Show Profile  Reply with Quote
quote:
Originally posted by bandi


DECLARE @TestData TABLE(UnitID VARCHAR(10), TestNumber INT, Defect VARCHAR(15))
INSERT INTO @TestData
SELECT 'unit-A', 1, 'Not Found' union all
SELECT 'unit-A', 2, 'Defect-A' union all
SELECT 'unit-B', 1 , 'Defect-A' union all
SELECT 'unit-B', 2 , 'Defect-B' union all
SELECT 'unit-B', 3 , 'Not Found' union all
SELECT 'unit-B', 4 , 'Defect-A' union all
SELECT 'unit-C', 1 , 'Defect-B' union all
SELECT 'unit-C', 2 , 'Defect-B' union all
SELECT 'unit-C', 3 , 'Defect-A'
/*TestRslt
UnitID TestNumber Defect
unit-A 1 Not Found
unit-B 2 Defect-B
unit-C 1 Defect-B*/
SELECT UnitID, TestNumber, Defect
FROM (SELECT *, ROW_NUMBER() OVER( PARTITION BY UnitID ORDER BY CASE WHEN Defect = 'Defect-B' THEN 1 ELSE 0 END DESC ) rn
		FROM @TestData
		WHERE Defect IN ( 'Defect-B', 'Not Found')
	) t
WHERE t.rn = 1;




Bandi, Thank you for your detailed answer to my question. I will try your method and report back after. My data model is much more complecated than the above one. So it takes some time. Thank you
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000