Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 838
  • Last Modified:

Max rehire date for employee in a case statement

I NEED  a case statement that returns the max rehire date when the condition = 101.
here is the original case statement:

case when eec.EecJobChangeReason  = '100' then  CONVERT(VARCHAR(25),eec.EecDateOfOriginalHire, 101)
             when eec.EecJobChangeReason  <> '101' then CONVERT(VARCHAR(25),eec.eecdateoflasthire, 101)
             end AS 'Hire Date',
            case when eec.EecJobChangeReason  = '101' then CONVERT(VARCHAR(25),eec.eecdateoflasthire      , 101)
            end as 'ReHire-Date',
            CONVERT(VARCHAR(25), eec.EecDateOfSeniority, 101)                  AS 'Seniority Date',

******I need  this part of the case statement to return the max rehire date for that employee**************

            case when eec.EecJobChangeReason  = '101' then CONVERT(VARCHAR(25),eec.eecdateoflasthire      , 101)
            end as 'ReHire-Date',
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you need the "max" rehire date, you need to apply MAX() around the case expression (and put the relevant GROUP BY to your sql also, of course.
Scott PletcherSenior DBACommented:
You'll need to delay the use of CONVERT so that you can accurately get the MAX ... you SHOULD delay the use of CONVERT when doing GROUPing and other processing anyway for performance reasons.  Make your original query a derived table and add an outer query that converts dates, and does other formatting:

SELECT ..., CONVERT(varchar(10), [ReHire-Date], 101) AS [ReHire-Date], ...
    --existing query
    SELECT ...
    MAX(case when eec.EecJobChangeReason  = '101' then eec.eecdateoflasthire end) as [ReHire-Date],
    FROM ...
) AS derived

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now