HAVING ANY and HAVING EVERY in SQL Server 2005

Question

Hi,

Does SQL Server 2005 support ANY or EVERY with HAVING?

Suppose I have two tables Training(TrainingID, TrainingCloseDate) and TrainingDetail(TrainingDetailID,TrainingID,LKClassCode,CompletionDate). For one TrainingID, there can be multiple values in TrainingDetail with different LKClassCode. I need to find all the TrainingIDs which have atleast one TrainingDetailID with CompletionDate between 1/1/2009 and 1/1/2010.

When I tried with HAVING ANY, I got an error "Incorrect syntax near the keyword 'ANY'." if it does not support, could you please suggest an alternative?

What if I need to find all the TrainingIDs which have 'atleast one TrainingDetailID with CompletionDate between 1/1/2009 and 1/1/2010 or the TrainingCloseDate = '5/5/2009' '?

Thanks Lijo

Answer 1

I need to find all the TrainingIDs which have atleast one TrainingDetailID with CompletionDate between 1/1/2009 and 1/1/2010.

SELECT TrainingID FROM TrainingDetail WHERE CompletionDate BETWEEN date1 AND date2

You might need to convert dates to proper format (timestamp?).

What if I need to find all the TrainingIDs which have 'atleast one TrainingDetailID with CompletionDate between 1/1/2009 and 1/1/2010 or the TrainingCloseDate = '5/5/2009' '?

SELECT TD.TrainingID FROM TrainingDetail TD JOIN Training T ON T.TrainingID = TD.TrainingID WHERE (CompletionDate BETWEEN date1 AND date2) OR TrainingCloseDate = '5/5/2009';

Answer 2

Let me refine/redefine the requirement. I need to list all the differentTraining Dates for one trainingId as a single row. It requires a pivoting (rows to column). Hence I am using a GROUP BY amd then MAX and CASE. So I need group of trainingIDs which have atleast one CompletionDate between 1/1/2009 and 1/1/2010.

Following is the query

SELECT T.TrainingID, MAX (CASE WHEN TD.LKTrainingClassCode = 'TraningDetailXYZ' THEN CompletedDt
ELSE NULL END) AS [XYZCompleted], MAX (CASE WHEN TD.LKTrainingClassCode = 'TraningDetailPQR' THEN CompletedDt
ELSE NULL END) AS [PQRCompleted] FROM Training T LEFT OUTER JOIN TrainingDetail TD ON TD.TrainingID = T.TrainingID GROUP BY T.TrainingID -- -- HAVING ANY CompletedDt BETWEEN '1/1/2009' AND '1/1/2010'

Thanks for the immediate reply. It helped me to define my problem.

Please help

Thanks Lijo