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
 Help! Sql needed to process the test data.

Author  Topic 

jazzclassics
Starting Member

3 Posts

Posted - 2013-04-07 : 04:47:43
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-07 : 21:07:49
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-08 : 02:03:05
[code]
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;[/code]
Go to Top of Page

jazzclassics
Starting Member

3 Posts

Posted - 2013-04-08 : 22:19:53
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 - 2013-04-08 : 22:21:17
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
   

- Advertisement -