Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Hierarchy Select in a subquery
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

26 Posts

Posted - 07/09/2013 :  17:28:06  Show Profile  Reply with Quote
I've painted myself into an SQL corner and need some help. My dilemma is this. I have database where I store the results when compare fingerprint impressions to subjects. I store the results in a junction table EvidenceSubjects. I want to generate a report that shows my Evaluations, but I want to do this in a hierarchy/prioritized way.

For instance, when the evaluation = 1, I only want that that result to show, nothing else and I want to append some text and the name of the person..I can do that. However that's all I can do. When there's more than 1 evaluation I need the select statement to be smart enough to prioritize

Evaluation values: 1 = Identification, 2=exclusion, 3 = inconclusive, 4 = incomplete

The priority is this:

When an impression (evidenceID) has an evaluation =1 and anything else only show the identification info to the person

When an impression has an associated evaluation = 4 but not 1 append the text 'Outstanding issue'

When an impression has an associated evaluation = 2 or 3, but not 1 or 4 display the text 'No'

When an impression has a null association display the text 'No'

Notes: The caseID is static in my example so I can test my code. If I don't hard code my Evaluation =1 in my subquery I get multiple results so that's why it's there.

Here's my code

SELECT '['+ISNULL(Upper(I.ItemNumber),'QA') +'] ['+Upper(ISNULL(I."Lab Number",'QA') +' '+ ISNULL(I.LatentLetter,'  '))+'] - '+ISNULL(Upper(I.ItemSource),'QA') AS "[ITEM#] [LAB#] - DESCRIPTION"
					WHEN I.ItemDescriptions = 1 THEN 'Latent Lift'
					WHEN I.ItemDescriptions = 2 THEN 'Film Negative'
					WHEN I.ItemDescriptions = 3 THEN 'Digital Image'
					WHEN I.ItemDescriptions = 1 THEN 'Other'
				End) as [TYPE]
					WHEN I.LatentAnalysis = 1 THEN 'NO Value'
					WHEN I.LatentAnalysis In (2,3) AND I.Finger =1 THEN 'Finger'
					WHEN I.LatentAnalysis In (2,3) AND I.Palm = 1 Then 'Palm'
					WHEN I.LatentAnalysis In (2,3) AND I.Impression = 1 Then 'Unsourced'
					Else 'QA'
				End) as [ANALYSIS]
					WHEN I.LatentAnalysis = 2 AND I.AFISRun = 1 THEN 'AFIS Quality - AFIS Searched'
					WHEN I.LatentAnalysis = 2 AND I.AFISRun IS null THEN 'AFIS Quality - NO AFIS SEARCH'
					WHEN I.LatentAnalysis = 2 AND I.AFISRun IS null AND I.AdCaptOf = 1 THEN 'SEE Glossary - NO AFIS SEARCH'
					Else 'NOT AFIS Quality - No AFIS SEARCH'
				End) AS [AFIS INFO]
			,ISNULL((SELECT (CASE E2.Evaluation
				WHEN 1
					THEN 'IND'
				END)  + ' to ' + ISNULL(CASE E2.IndTo
											WHEN 1
												THEN 'R. Thumb of '
											WHEN 2
											    THEN 'R. Index of '
											WHEN 3
												THEN 'R. Middle of '
											WHEN 4
											    THEN 'R. Ring of '
											WHEN 5
												THEN 'R. Little of '
											WHEN 6
											    THEN 'L. Thumb of '											    
											WHEN 7
												THEN 'L. Index of '
											WHEN 8
											    THEN 'L. Middle of '
											WHEN 9
												THEN 'L. Ring of '
											WHEN 10
											    THEN 'L. Little of '											    
											WHEN 12
												THEN 'L. Palm of '
											WHEN 13
											    THEN 'R. Palm of '
											WHEN 14
												THEN 'L. Foot of '
											WHEN 15
											    THEN 'R. Foot of '											    
										END ,'') + '' + Upper(S.SubjectLastName +', '+S.SubjectFirstName + CHAR(10))
 		FROM LIMS_DATA_Production.dbo.EvidenceSubjects E2
			JOIN LIMS_DATA_Production.dbo.Subjects S
			  ON E2.SubjectID = S.ID
		WHERE E2.Evaluation = 1 AND S.CaseID = I.CaseID AND S.SDelete = 0 AND E1.EvidenceID = E2.EvidenceID),'NO') AS [INDIVIDUALIZATIONS]
FROM  LIMS_DATA_Production.dbo.EvidenceSubjects E1
  JOIN LIMS_DATA_Production.dbo.Impressions I
    ON E1.EvidenceID = I.ID  
Where I.CaseID = 18247 and I.SDelete = 0
GROUP BY I.ItemNumber,I.AFISRun,I.CaseID, I.AdCaptOf,I.ItemDescriptions, E1.EvidenceID, I."Lab Number", I.LatentLetter, I.ItemSource,I.Finger, I.Palm, I.Impression, I.LatentAnalysis
Order by [Lab Number]

Thanks in advance.

Starting Member

47 Posts

Posted - 07/16/2013 :  08:37:36  Show Profile  Reply with Quote
It is not clear what you are trying to do. You have many things you are trying to do which require a thorough explanation. Remember you understand your problem very well. No one else knows what you are trying to do, nor what your database looks like nor what the expected results are.

Did you try UNION?
Go to Top of Page
  Previous Topic Topic Next 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.03 seconds. Powered By: Snitz Forums 2000