Hi all,
I have a table called Groups with columns GroupID and release date.
I need to write a query which searches the group id based on oldest release date and the result should show latest release date for the group. For example,
GroupID ReleaseDate
101 7/30/2012
101 8/1/2012
102 7/31/2012
102 8/2/2012
102 8/5/2012
103 8/1/2012
103 8/6/2012
Query -
Select GroupID, ReleaseDate
From Groups
Where ReleaseDate Between '7/28/2012' and '7/31'2012'
Result should be as follows -
GroupID ReleaseDate
101 8/1/2012
102 8/5/2012
Please note that Serach criteria is based on Oldest date. i.e. it should check if MIN(ReleaseDate) between '7/28/2012' and '7/31'2012' for each Group. Here only 101 and 102 satisfies the condition but for 103 Min(ReleaseHoldDate) is '8/1/2012'
But in the result it should show MAX(Release Date) for the group
Query -
Select GroupID, ReleaseDate
From Groups
Where ReleaseDate Between '8/1/2012' and '8/5/2012'
Result should be as follows -
GroupID ReleaseDate
103 8/6/2012
I have tried like below for the 1st query, but did not work.
SELECT GroupID, MAX(ReleaseHoldDate)
FROM Groups
GROUP BY GroupID,ReleaseHoldDate
HAVING MIN(ReleaseHoldDate) BETWEEN '7/28/2012' AND '7/31/2012'
This did not work. Please help.
Thanks in advance,
SR