SQL Case on Where clause to different columns

Question

I've been trying to get a query that, based on a given condition (if isCurrent = 1 or not) should give me just one value/row based on the CurriculumId /which will be a parameter on a stored procedure). This value should, in case isCurrent = 1 return to me the item with the most current StartDate but if isCurrent = 0 then it should give me the one with the most current EndDate.

The thing is that I only want one item per CurriculumId, ideally the one with isCurrent = 1 and the most current StartDate (ignoring the remaining rows) but, if there are no experiences with isCurrent = 1, then it should return to me the one with the most current EndDate.

My previous query was almost working but I still got the one with the most current StartDate (isCurrent = 1) AND the one with the most current EndDate when I want to retrieve just one or another.

I've come to the query bellow:

SELECT table.IntProfessionalExperienceId,
    table.IsCurrent,
    table.StartDate,
    table.EndDate
FROM table
WHERE table.CurriculumId = 12
AND
CASE table.IsCurrent
WHEN 1
THEN
    table.StartDate = (
            SELECT max(table.StartDate)
            FROM table
            WHERE table.IsCurrent = 1 
            AND table.CurriculumId = 12
            GROUP BY table.CurriculumId
    )
ELSE
      table.EndDate = (
        SELECT max(table.EndDate)
        FROM table
        WHERE table.CurriculumId = 12
        GROUP BY table.CurriculumId
    )
END

Individually, the queries seem to be working OK and returning the supposed value although when ran as a whole I get the following errors:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ')'.

What in my syntax is wrong? I know from reading the errors what is wrong with the query but I just don't know how to fix it. And is it just the syntax or am I doing the query wrong to start with?

Answer 1

Give each row a rank in its curriculumid group, using ROW_NUMBER with an appropriate order by clause. Then only take the records ranked 1 (i.e. best matching).

select
  intprofessionalexperienceid,
  iscurrent,
  startdate,
  enddate
from
(
  select mytable.*
  row_number() over 
  (
    partition by curriculumid 
    order by 
      case when iscurrent = 1 then 1 else 2 end, 
      case when iscurrent = 1 then startdate else enddate end desc
  ) as rn
  from mytable
) ranked
where rn = 1;

(I know this doesn't actually answer your question, but is the straight-forward way to approach the problem in my opinion.)

Answer 2

Split this into multiple conditions, like this:

SELECT 
    table.IntProfessionalExperienceId,
    table.IsCurrent,
    table.StartDate,
    table.EndDate
FROM table
WHERE 
    table.CurriculumId = 12 AND
      (
        (
            Table.IsCurrent = 1 AND 
            table.StartDate = 
              (
                SELECT max(table.StartDate)
                FROM table
                WHERE 
                    table.IsCurrent = 1 AND 
                    table.CurriculumId = 12
                GROUP BY table.CurriculumId
              )
        ) OR 
        (
            ISNULL(table.IsCurrent,0) != 1 AND
            table.EndDate = 
              (
                SELECT max(table.EndDate)
                FROM table
                WHERE table.CurriculumId = 12
                GROUP BY table.CurriculumId
              )
        )
      )

EDIT: another, arguably simpler approach would be to pre-aggregate the data you want in your WHERE clause so that you only need to call it a single time, rather than evaluate each row separately. Something like the following:

SELECT 
    table.IntProfessionalExperienceId,
    table.IsCurrent,
    table.StartDate,
    table.EndDate
FROM 
    table
     INNER JOIN 
      (
        SELECT 
            MAX(table.EndDate) AS MaxEndDate, 
            MAX(CASE WHEN table.IsCurrent = 1 THEN table.StartDate END) AS MaxCurrentStartDate
        FROM table 
        WHERE CurriculumID = 12 
      ) MaxDates ON 
        (Table.IsCurrent = 1 AND Table.StartDate = MaxDates.MaxCurrentStartDate) OR
        (ISNULL(Table.IsCurrent, 0) != 1 AND Table.EndDate = MaxDates.MaxEndDate)
WHERE 
    table.CurriculumId = 12

Answer 3

Try to use CASE statemen this way:

SELECT table.IntProfessionalExperienceId,
    table.IsCurrent,
    table.StartDate,
    table.EndDate
FROM table
WHERE table.CurriculumId = 12
AND table.EndDate = CASE 
                      WHEN table.IsCurrent = 1
                      THEN (
                             SELECT max(table.StartDate)
                             FROM table
                             WHERE table.IsCurrent = 1 
                             AND table.CurriculumId = 12
                             GROUP BY table.CurriculumId
                           )
                      ELSE
                           (
                             SELECT max(table.EndDate)
                             FROM table
                             WHERE table.CurriculumId = 12
                             GROUP BY table.CurriculumId
                           )
                    END