Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Unanswered: 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.

    Thanks
    Last edited by M37ISS4; 10-27-06 at 20:22.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well you don't need a CASE statement for this. A simple aggregate query will handle the problem you describe:
    Code:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2006
    Posts
    2
    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?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •