Question on using CASE

Hello All,

I am pretty new to SQL Server and I am trying to create a view to gather the necessary data I need. I am not sure if CASE is what I should be using, or if I can even do what I need, but I am trying to capture the following information (I know the data looks a bit wacky, but I cannot post the real data so it is just an example).

Example data:
ID - DESC - STARTDATE - ENDDATE
1A - Pool - 9/21/06 - 9/23/06
1A - Pool - 9/21/05 - 9/23/05
1B - Garden - 9/2/06 - 9/4/06

I want to return the following data:
ID - DESC - STARTDATE - ENDDATE
1A - Pool - 9/21/05 - 9/23/05
1B - Garden - 9/4/06 - 9/4/06

Basically in my mind I am thinking along the lines of:
IF DESC = "Pool" THEN STARTDATE = "minimum STARTDATE"
ELSE STARTDATE = "ENDDATE"

I am having an issue trying to figure out how to create the syntax for this CASE statement.

Any help is appreciated.

ThanksWell you don't need a CASE statement for this. A simple aggregate query will handle the problem you describe:
select ID,
DESC,
min(STARTDATE) as STARTDATE,
max(ENDDATE) as ENDDATE
from YourTable
group by ID,
DESC...but I bet you will find that your problem is more complex than you describe, and that you are going to have to deal with gaps between date ranges that a simple MIN and MAX will overlook.|||Thanks for the reply.

I had tried that type of query but the real issue is I need the min STARTDATE if the DESC = "Pool". If the DESC is anything else I want the STARTDATE to take the ENDDATE.

Probably not possible?|||select ID,
DESC,
case when DESC = 'Pool' then STARTDATE else ENDDATE end as STARTDATE,
ENDDATE
from (select ID,
DESC,
min(STARTDATE) as STARTDATE,
max(ENDDATE) as ENDDATE
from YourTable
group by ID,
DESC) Subquery

You could probably do this without the subquery as well, but it is a little odd mixing aggregate and non-aggregate values in a single column.