ITEM#	LAB#	LISTED AS:	     ANALYSIS	               LIFTED BY	   VISUAL COMPARISON RESULTS
AB123	AA02 A	DOOR HANDLE	FINGER / VALUE / NO AFIS RUN		        IND to R. Palm of JUSTICE, BUFORD T, EXC to PUBLIC, JOHN Q ICP to DOE, JOHN 
AB123	AA02 A	DOOR HANDLE	FINGER / VALUE / NO AFIS RUN	BC 12345	IND to R. Palm of JUSTICE, BUFORD T, EXC to PUBLIC, JOHN Q ICP to DOE, JOHN

SELECT  Upper(I.ItemNumber) as [ITEM#],Upper(I."Lab Number" +' '+ ISNULL(I.LatentLetter,' '))AS [LAB#] 	,Upper(I.ItemSource) As [LISTED AS:]
	,Upper(ISNULL((Case I.Finger 
						WHEN 1 
							THEN'Finger'
						Else (Case I.Palm 
									WHEN 1 
										THEN 'Palm' 
							  ELSE (Case I.Impression 
										WHEN 1 
											THEN'Impression' 
										ELSE NULL 
									END)
							  END)
					 END),'NLPC') + ' / ' +
	+  (Case I.LatentAnalysis
			WHEN 1 
				THEN 'NLPC'
			WHEN 2
				THEN 'VALUE'
			WHEN 3
				THEN 'VALUE'
			ELSE ' '
			END)) + ' / ' +  (Case WHEN I.AFISRun <> '' THEN 'AFIS RUN   ' ELSE 'NO AFIS RUN' END)  AS [ANALYSIS]
	,ISNULL(UPPER(E1.LiftedBy), '')	as [LIFTED BY]	
	,ISNULL((SELECT (CASE E2.Evaluation
				WHEN 1
					THEN 'IND'
				WHEN 2
					THEN 'EXC'
				WHEN 3
					THEN 'INC'
				WHEN 4
					THEN 'ICP'
				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. Pinky 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. Pinky 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 S.CaseID = I.CaseID AND S.SDelete = 0 AND E1.EvidenceID = E2.EvidenceID
		FOR XML PATH ('') ),'NO RESULTS RECORDED') AS [VISUAL COMPARISON RESULTS]
FROM  LIMS_DATA_Production.dbo.EvidenceSubjects E1
RIGHT 
  JOIN LIMS_DATA_Production.dbo.Impressions I
    ON E1.EvidenceID = I.ID  
WHERE ((I.CaseID = 12345) and (I.SDelete = 0))
GROUP BY I.ItemNumber,I.AFISRun,I.CaseID, E1.EvidenceID, I."Lab Number", I.LatentLetter, I.ItemSource,I.Finger, I.Palm, I.Impression, I.LatentAnalysis,E1.LiftedBy
ORDER BY [LAB#]