--
-- Non-Target Hits containing Chlorine in Formula
--

SELECT b.BatchID, bs.SampleID, bsc.DeconvolutionMethodID, bsc.ComponentID, bsch.HitID, 
REPLACE(b.TargetBatchFileName,'.batch.bin','') AS TargetBatchName, 
bs.SampleName,
-- bs.DataFileName,  bs.SampleType, bs.SampleGroup, bs.MatrixSpikeGroup, 
-- bs.Dilution, bs.ISTDDilution, bs.Comment, bs.UserDefined, bs.SampleInformation, 
-- bs.SamplingTime, bs.SamplingDateTime, bs.Vial, bs.PlateCode, bs.PlatePosition, bs.TrayName, 
-- bs.InstrumentName, bs.InstrumentType, bs.SampleAmount, bs.TotalSampleAmount, 
-- CAST(bsch.HitID AS Bit) AS IsHit, 
-- CAST(bsc.PrimaryHitID AS Bit) AS IsPrimaryHit, 
-- CAST(bsch.TargetCompoundID AS Bit) AS IsTargetHit,

bsch.CompoundName As Compound, 
bsch.CASNumber AS CAS, 
bsch.Formula AS Formula, 
bsch.MolecularWeight AS MW, 
CONVERT(decimal(3,1), bsch.LibraryMatchScore) AS LibraryMatch, 
CONVERT(decimal(7,4),bsc.RetentionTime) AS RT, 
CONVERT(decimal(5,4), bsch.EstimatedConcentration) AS EstimatedConc

FROM Batch AS b  JOIN Sample AS bs ON b.BatchID = bs.BatchID    
JOIN Component AS bsc ON bs.BatchID = bsc.BatchID AND bs.SampleID = bsc.SampleID    
JOIN Hit AS bsch ON bsc.BatchID = bsch.BatchID AND bsc.SampleID = bsch.SampleID AND bsc.DeconvolutionMethodID = bsch.DeconvolutionMethodID AND bsc.ComponentID = bsch.ComponentID  

WHERE bsch.Formula LIKE '%Cl%' AND
      bsc.PrimaryHitID IS NOT NULL AND
      bsch.TargetCompoundID IS NULL

ORDER BY bsc.RetentionTime, bs.SampleName, bsch.CompoundName