Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2010-02-08 : 10:32:43
|
| Hi,Suppose I have two tables Training(TrainingID, TrainingCloseDate) and TrainingDetail(TrainingDetailID,TrainingID, LKTrainingClassCode, CompletedDt). For one TrainingID, there can be multiple values in TrainingDetail with different LKTrainingClassCode. I need to list all the different Training Dates for one TrainingID as a single row. It requires a pivoting (rows to column). Hence I am using a GROUP BY and then MAX and CASE. So I need group of TrainingIDs which have at least one CompletedDt 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? Following is the query I tried SELECT T.TrainingID, MAX (CASE WHEN TD.LKTrainingClassCode = 'TraningDetailXYZ' THEN CompletedDtELSE NULL END) AS [XYZCompleted], MAX (CASE WHEN TD.LKTrainingClassCode = 'TraningDetailPQR' THEN CompletedDtELSE 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' What if I need to find all the TrainingID groups which have 'atleast one TrainingDetailID with CompletionDate between 1/1/2009 and 1/1/2010 or the TrainingCloseDate = '5/5/2009' '? Please help ThanksLijo |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-08 : 10:37:00
|
| just make it as HAVING SUM(CASE WHEN CompletedDt BETWEEN '1/1/2009' AND '1/1/2010' THEN 1 ELSE 0 END) > 0 |
 |
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-02-09 : 00:49:15
|
| Thanks !!! That's brillinat answer.One extension to the question. What if the condition is as follows - "All" the completed date should be betweeen 1/1/2009 and 1/1/2010 instead of "ANY" completed date? I think, EVERY key word is not available in SQL Server 2005. |
|
|
|
|
|
|
HAVING with ANY