SELECT '['+ISNULL(Upper(I.ItemNumber),'QA') +'] ['+Upper(ISNULL(I."Lab Number",'QA') +' '+ ISNULL(I.LatentLetter,'  '))+'] - '+ISNULL(Upper(I.ItemSource),'QA') AS "[ITEM#] [LAB#] - DESCRIPTION"
		,Upper(Case 
					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]
		,Upper(Case 
					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]
		,UPPER(Case
					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
		INNER 
			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
RIGHT 
  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]