Max rehire date for employee in a case statement

Posted on 2012-08-21
Last Modified: 2012-08-29
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',
Question by:asullivan62
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.
    LVL 68

    Accepted Solution

    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], ...
    FROM (
        --existing query
        SELECT ...
        MAX(case when eec.EecJobChangeReason  = '101' then eec.eecdateoflasthire end) as [ReHire-Date],
        FROM ...
    ) AS derived

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now