with

SpecimensWithMultipleBatches

as

(select K_RESULT.specimenId,count(K_RESULT.SpecimenID)

as

SpecimenBatchesCount

from K_RESULT

inner join

K_Specimen

on

K_RESULT.SpecimenId = K_Specimen.SpecimenId

where K_RESULT.testid = 10078

group by K_Result.SpecimenId

having count(K_RESULT.SpecimenID) > 1 )

select K_SPECIMEN.SpecimenId,K_Specimen.Barcode,SpecimensWithMultipleBatches.SpecimenBatchesCount

from K_SPECIMEN

inner join SpecimensWithMultipleBatches

on

K_SPECIMEN.SpecimenID = SpecimensWithMultipleBatches.SpecimenID

order by SpecimensWithMultipleBatches.SpecimenBatchesCount desc

Advertisements