Query returning a number - 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

Transact-SQL (2000)

Query returning a number

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-01-12 : 13:47:38

I have a query I need to run, but I'm not sure how to create it.

Each part number in our database can be tested multiple times at each of our test stations.

Declare Serial_Number varchar(20) -- this is the part number
Declare Test_Result varchar(255) -- this stores the test result
Declare System_ID varchar(50) -- the test station name
Declare Date_Time Datetime -- when test was performed

One of the floor managers wants me to generate a one time report that shows the number of parts that passed the first time for all parts that start with 'C710303 %'.

I made a quick call to

select count(distinct Serial_Number)
from dbo.Results
where (Serial_Number Like 'C710303 %')

and I see the number 476.

How could I write a query that counts the number of parts where the first Test_Result entry for it contains the text 'pass'?

Select Serial_Number
from dbo.results
where (Test_Result Like '%pass%')

will return all parts, because everything eventually passes.

I'm happy to explain further, if this doesn't make sense.

Avoid Sears Home Improvement

acollins74
Yak Posting Veteran

82 Posts

Posted - 2009-01-12 : 14:04:56

at quick glance try this and test your results.

select count(serial_number)
from results a
inner join
select serial_number,min(date_time) as mydate
from Results
group by serial_number) b
on a.serial_number = b.serial_number
and a.date_time = b.mydate
where a.Serial_Number Like 'C710303 %'
and a.serial_number like '%pass%'

jp2code
Posting Yak Master

175 Posts

Posted - 2009-01-12 : 14:25:38

Thanks! That will certainly get me started.

BTW: I saw the 'max(date_time)' before you edited it, but I knew what you meant.

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources