SQL Server Performance

ANY and EVERY for HAVING

Discussion in 'SQL Server 2005 General Developer Questions' started by Lijo Cheeran Joseph, Feb 8, 2010.

  1. Lijo Cheeran Joseph New Member

    Hi, Does SQL Server 2005 support ANY or EVERY with HAVING?
    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 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' 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' '?
    Thanks Lijo
  2. FrankKalis Moderator

    Welcome to the forum!
    Can you post your table structures, sample data & required output please?
    I'm not aware of an EVERY keyword at least in SQL Server, but I suspect your problem can be solved using the EXISTS clause.
  3. Lijo Cheeran Joseph New Member

    Hi
    Thanks for the immediate reply
    Training(TrainingID INT, TrainingCloseDate DATETIME)
    TrainingDetail(TrainingDetailID INT,TrainingID INT, LKTrainingClassCode VARCHAR, CompletedDt DATETIME).
    TrainingIDTrainingCloseDate
    1NULL
    21/1/2010TrainingDetailID TrainingID LKTrainingClassCode CompletedDt
    11Level 11/1/2010
    21Level 21/1/2008
    31Level 35/5/2009
    41Level 47/7/2009Result
    TrainingIDLevel 1Level 2Level 3Level 4
    11/1/20101/1/20085/5/20097/7/2009 Thanks
    Lijo
  4. FrankKalis Moderator

    Still guessing a little bit, but are you looking for something like this?
    SELECT
    TD.TrainingId,
    MAX(CASE WHEN TD.LKTrainingClassCode = 'Level1' THEN TD.CompletedDt ELSE NULL END) AS Level1,
    MAX(CASE WHEN TD.LKTrainingClassCode = 'Level2' THEN TD.CompletedDt ELSE NULL END) AS Level2,
    MAX(CASE WHEN TD.LKTrainingClassCode = 'Level3' THEN TD.CompletedDt ELSE NULL END) AS Level3,
    MAX(CASE WHEN TD.LKTrainingClassCode = 'Level4' THEN TD.CompletedDt ELSE NULL END) AS Level4
    FROM
    TrainingDetail TD
    WHERE EXISTS (SELECT 1
    FROM
    TrainingDetail TD2
    WHERE
    TD.TrainingId = TD2.TrainingId AND
    TD2.CompletedDt BETWEEN '2009-01-01' AND '2009-12-31')
    OR EXISTS (SELECT 1
    FROM
    Training T
    WHERE
    TD.TrainingId = T.TrainingId AND
    T.TrainingCloseDate = '2009-05-05')
    GROUP BY
    TD.TrainingId;
  5. Lijo Cheeran Joseph New Member

    Thanks for your support. I was looking for the following -
    HAVING (SUM(CASE WHEN TD.CompletedDt BETWEEN @FromTrainingCompletedDate AND @ToTrainingCompletedDate THEN 1 ELSE 0 END) > 0)
  6. FrankKalis Moderator

    Interesting! Wouldn't have guessed so from your description and your expected result. Anyway, glad you found a solution.

Share This Page