Returning a Summary Row - 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)

Returning a Summary Row

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2010-07-14 : 09:45:00

I have data in a table that looks like the following sample data:


 varchar(20)    | DateTime               | varchar(20) | varchar(20)     |varchar(255)
_Serial_Number__|_Date_Time______________|_System_ID___|_Op_ID___________|_Test_Result________________
C035993 0703 05 |2005-08-18 13:43:33.717 |VTI-Chamber1 | Robert Halton   | (BLUE) TEST ABORTED, LEAKFP SPUN DOWN
C035993 0702 05 |2005-08-18 13:51:52.640 |VTI-Chamber1 | Robert Halton   | FAIL: Squirt Test.
C035993 0704 05 |2005-08-18 14:18:13.607 |VTI-Chamber1 | Robert Halton   | TEST ABORTED
C035993 0705 05 |2005-08-18 14:30:43.717 |VTI-Chamber1 | Robert Halton   | B=FAIL, Final N2 Fill after Settle, W=PASS,
C035993 0707 05 |2005-08-18 14:41:59.310 |VTI-Chamber1 | Robert Halton   | FAIL: Fine Test.
C035878 0775 05 |2005-08-18 15:38:25.810 |VTI-Chamber1 | Bryan Templeton | Chamber Calibration Factor Too High
C035878 0774 05 |2005-08-18 15:43:23.000 |VTI-Chamber1 | Bryan Templeton | FAIL Pressure Decay Test
C035993 0674 05 |2005-08-18 15:51:49.467 |VTI-Chamber1 | Bryan Templeton | FAIL: Squirt Test.
BLANKTEST       |2005-08-18 15:58:40.793 |VTI-Chamber3 | TYSON MAYFIELD  | Pass.
C035993 0706 05 |2005-08-18 15:59:03.200 |VTI-Chamber1 | Bryan Templeton | Pass.

I need to create a couple of scripts go through all of the records for a given Serial_Number and determine if it passed or failed. There are generally multiple entries for each part.

One test needs to determine the status of the part, or whether the last test result was a PASS or a FAIL, ignoring data such as 'TEST ABORTED' or 'Chamber Calibration Factor Too High'.

The second test needs to determine the quality of the part, and the criteria we use for that is to check to see if the part passed on the first test, again ignoring erroneous data such as 'TEST ABORTED' or 'Chamber Calibration Factor Too High'.

I feel like I need to create something that selects the distinct serial number, then write a while loop that iterates over the data.

Is that an efficient approach? How would I go about doing this?

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2010-07-14 : 12:28:08

I've got something working, but I do not currently have a way to get my returned data to be sorted by the Date_Time field.

If I can get that part figured out, I should be set.

Could someone kindly show me the way?

declare @result varChar(10), @serialNum varChar(20), @testResult varChar(255)
declare snList cursor for
	select distinct TR.Serial_Number
	from Test_Results TR
	left join ACP_Parts AP on (TR.Serial_Number=AP.Serial_Number)
	where (AP.Serial_Number is not null)
open snList
fetch next from snList into @serialNum
while (@@fetch_status=0) begin
	set @result=''
	declare resultList Cursor for
		select Test_Result
		from Test_Results
		where (Serial_Number=@serialNum) and (System_ID Like '%Chamb%')
	open resultList
	fetch next from resultList into @testResult
	while (@@fetch_status=0) and (@result<>'PASS') begin
		set @result=case
			when (0<CharIndex('fail', @testResult)) then 'FAIL'
			when (0<CharIndex('pass', @testResult)) then 'PASS'
			else ''
		end
	end
	close resultList
	select @serialNum as 'Serial_Number', @result as 'Test_Result'
	fetch next from snList into @serialNum
end
close snList

~Joe

Avoid Sears Home Improvement (read why)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-15 : 03:07:45

as a starter break it down into parts.

find the max date for a serial_number
select a.serial_number, max(a.date_time) from mytable a
where a.test_result in (test','fail')
group by a.serial_number

find the full serial details to go with the summary info returned above
select * from mytable b
inner join
(
select a.serial_number, max(a.date_time) as maxdate from mytable a
where a.test_result in (test','fail')
group by a.serial_number
) c on b.serial_number = c.serial_number and b.date_time = c.maxdate

finding the final part of the problem may be herder....especially since you haven't shown any test data that show "repeat tests"....but the above should get you moving/thinking. come bak with your next effort + more input date + matching expected results. remeber english (narrative & descriptions) can be somewhat vague....sample data is really the best way to describe problems.

ps...try to stay away from cursors....devils invention

jp2code
Posting Yak Master

175 Posts

Posted - 2010-07-15 : 10:32:45

LOL. Gotcha, AndrewMurphy! Cursors away!

So now, after spending most of yesterday researching online, I've come up with something that (to me) seems quite complicated.

On the surface, it works fine; however, it is only returning a fraction of the part numbers I would expect (123 rows instead of my 44011 distinct serial numbers).

What part of my SQL is throwing out all of my records? Are my JOINs correct? If a System_ID does not have a Serial_Number, it should still show the Serial_Number, but the data under that column should be NULL.

My goal here is to determine if there is *any* PASS at a 'DECAY' station (otherwise FAIL), and read the first 'CHAMBER' station record as either PASS or NULL (it may have eventually passed, but that does not go into a quality test).

SELECT

A.Serial_Number, 
D.Op_ID AS 'DecayOp',
D.Date_Time AS 'DecayDate',
D.System_ID AS 'DecayID',
CASE WHEN D.Test_Result LIKE '%fail%' THEN 'Fail' WHEN D.Test_Result LIKE '%pass%' THEN 'Pass' ELSE NULL END AS 'DecayResult',
C.Op_ID AS 'ChambOp',
C.Date_Time AS 'ChambDate',
C.System_ID AS 'ChambID',
CASE WHEN C.Test_Result LIKE '%pass%' THEN 'Pass' ELSE NULL END AS 'ChambResult'

FROM 

ACP_Parts AS A
LEFT OUTER JOIN Test_Results D ON
	(A.Serial_Number=D.Serial_Number)
LEFT OUTER JOIN Test_Results D0 ON
	(D.Serial_Number=D0.Serial_Number) AND
	(D0.Test_Result LIKE '%pass%' OR D0.Test_Result LIKE '%fail%') AND -- pass or fail
	(D.Date_Time<D0.Date_Time) -- D is less
LEFT OUTER JOIN Test_Results C ON
	(A.Serial_Number=C.Serial_Number)
LEFT OUTER JOIN Test_Results C0 ON
	(C.Serial_Number=C0.Serial_Number) AND
	(C0.Test_Result LIKE '%pass%' AND C0.Test_Result NOT LIKE '%fail%') AND -- pass and NOT fail
	(C0.Date_Time<C.Date_Time) -- C0 is less

WHERE 

(A.Serial_Number in (
	select distinct Serial_Number from ACP_Parts
	where
		(DATEADD(yy, - 1, GETDATE()) < Date_Time) AND
		(Serial_Number is not null) AND
		(CHARINDEX(' ', Serial_Number, CHARINDEX(' ', Serial_Number) + 1) = 13))
) AND
(D.Test_Result LIKE '%pass%' OR D.Test_Result LIKE '%fail%') AND (D.System_ID like '%decay%') AND (D0.Serial_Number IS NULL) AND
(C.Test_Result LIKE '%pass%' AND C.Test_Result NOT LIKE '%fail%') AND (C.System_ID like '%chamb%') AND (C0.Serial_Number IS NULL)

~Joe

Avoid Sears Home Improvement (read why)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-16 : 05:05:53

This bit will behave like an INNER JOIN

(D.Test_Result LIKE '%pass%' OR D.Test_Result LIKE '%fail%') AND (D.System_ID like '%decay%') AND (D0.Serial_Number IS NULL) AND
(C.Test_Result LIKE '%pass%' AND C.Test_Result NOT LIKE '%fail%') AND (C.System_ID like '%chamb%') AND (C0.Serial_Number IS NULL)

when you go
select * from mytablea a
left join mytalbeb b on b.key = a.key
where a.field = b.field

then SQL AUTOMATICALLY converts it into "an inner join" unless the where clause is "b.field is null"

select * from mytablea a
left join mytalbeb b on b.key = a.key and a.field = b.field
will stay as a left join

To debug what is failing with your code, work through some of your expected results and refine the code using specific example of the input data. Since we can't see either your inpur nor output, we would only be guessing as to the true point of failure.

Also I can't see the point of your "C" table....you "might" get away with re-using "D"

jp2code
Posting Yak Master

175 Posts

Posted - 2010-07-16 : 09:47:31

quote:
SQL AUTOMATICALLY converts it into "an inner join" unless the where clause is "b.field is null"

You know, I have seen that in a few scripts, but I never new what it was doing or how that worked.

What is that called? I'd like to look it up to make sure I'm using it correctly.

~Joe

Avoid Sears Home Improvement (read why)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources