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'
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
END) + ' to ' + ISNULL(CASE E2.IndTo
THEN 'R. Thumb of '
THEN 'R. Index of '
THEN 'R. Middle of '
THEN 'R. Ring of '
THEN 'R. Little of '
THEN 'L. Thumb of '
THEN 'L. Index of '
THEN 'L. Middle of '
THEN 'L. Ring of '
THEN 'L. Little of '
THEN 'L. Palm of '
THEN 'R. Palm of '
THEN 'L. Foot of '
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.