Finding the MIN and MAX date - How do I approach this?

  • I have a table with the following data:

    EMPIDEffectiveDate PrimaryRater

    1234510/10/2001 A12345

    1234507/12/2013 A12345

    1234508/18/2002 A12345

    1234507/17/1966 A12345

    1234501/01/1966 B12345

    What I want to do is create a table that looks like this:

    EMPIDStartDate PrimaryRater EndDate

    1234507/17/1966 A12345 07/12/2013

    1234501/01/1966 B12345 07/17/1966

    Not knowing SQL. I am not sure what approach is best.

    What approach do you recommend?

  • well, whenever you use any of the aggregate functions(MIN,MAX,COUNT)

    you have to have one or more GROUP BY column(s).

    IF you group by just EMPID, you can only end up getting the MIN/MAX PrimaryRater column.

    if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the question

    ;WITH MyCTE([EMPID],[EffectiveDate],[PrimaryRater])

    AS

    (

    SELECT '12345',convert(datetime,'10/10/2001'),'A12345' UNION ALL

    SELECT '12345','07/12/2013','A12345' UNION ALL

    SELECT '12345','08/18/2002','A12345' UNION ALL

    SELECT '12345','07/17/1966','A12345' UNION ALL

    SELECT '12345','01/01/1966','B12345'

    )

    SELECT EMPID,

    Min(EffectiveDate) AS StartDate,

    PrimaryRater,

    max(EffectiveDate)

    FROM MyCTE

    GROUP BY

    EMPID,

    PrimaryRater

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (12/10/2013)


    if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the question

    Why not? It seems correct to me.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Would this approach work in production?

    The actual table that I want to use has 82K of rows.

    One row for every EffectiveDate, PrimaryRater, PrimaryReviewer combination for every EMPID in the company.

    My example was just one employee. Some employees have 20 rows of combinations and some just have one row.

    Reconstructing your answer for 82K of rows of data. Doesn't seem optimal. Any suggestions?

  • Just to be clear.

    Lowell used the following code as sample data:

    ;WITH MyCTE([EMPID],[EffectiveDate],[PrimaryRater])

    AS

    (

    SELECT '12345',convert(datetime,'10/10/2001'),'A12345' UNION ALL

    SELECT '12345','07/12/2013','A12345' UNION ALL

    SELECT '12345','08/18/2002','A12345' UNION ALL

    SELECT '12345','07/17/1966','A12345' UNION ALL

    SELECT '12345','01/01/1966','B12345'

    )

    And the following is the actual solution:

    SELECT EMPID,

    Min(EffectiveDate) AS StartDate,

    PrimaryRater,

    max(EffectiveDate)

    FROM MyCTE

    GROUP BY

    EMPID,

    PrimaryRater

    This will work with millions of rows and any combinations. Be sure that PrimaryRater is always the same for each employee, otherwise, you'll get a row for evey EMPID and PrimaryRater combination.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (12/10/2013)


    Lowell (12/10/2013)


    if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the question

    Why not? It seems correct to me.

    Lius, it was his expected results that made me wonder: he said his expected results for one of the rows was this data:

    1234501/01/1966 B12345 07/17/1966

    , but since i grouped by PrimaryRater, and there's only one row, the min/max dates are teh same value (01/01/1966)

    that's why i thought either his expected results might be incorrect, or there's some biz rule not yet described, like 'if there's no [?date], use a different row?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you,

    I tried the code on my table:

    SELECT EMPID,

    CONVERT(VARCHAR,Min(CONVERT(DATETIME,EffectiveDate)),107) AS StartDate,

    PrimaryRater,

    CONVERT(VARCHAR,MAX(CONVERT(DATETIME,EffectiveDate)),107) AS EndDate

    FROM ReportingTable

    GROUP BY

    EMPID,

    PrimaryRater

    I received the following results:

    EMPIDStartDate PrimaryRater EndDate

    12345Jul 17, 1966 A12345 Jul 12, 2013

    12345Jan 01, 1966 B12345 Jan 01, 1966

    I think the only piece missing is this. I would like the StartDate to be the EndDate for the Previous Primary Rater.

    Like this:

    EMPIDStartDate PrimaryRater EndDate

    12345Jul 17, 1966 A12345 Jul 12, 2013

    12345Jan 01, 1966 B12345 Jul 17, 1966

    This will provide a sequential history of all PrimaryRater, EffectiveDate combinations.

    How would I achieve this?

  • I had totally missed the point of the different EndDate for PrimaryRater B12345.

    Here's an option:

    ;WITH MyCTE([EMPID],[EffectiveDate],[PrimaryRater])

    AS

    ( --This is sample data

    SELECT '12345',convert(datetime,'10/10/2001'),'A12345' UNION ALL

    SELECT '12345','07/12/2013','A12345' UNION ALL

    SELECT '12345','08/18/2002','A12345' UNION ALL

    SELECT '12345','07/17/1966','A12345' UNION ALL

    SELECT '12345','01/01/1966','B12345'

    ),

    --Solution starts here

    Aggregated AS(

    SELECT EMPID,

    Min(EffectiveDate) AS StartDate,

    PrimaryRater,

    max(EffectiveDate) EndDate,

    DENSE_RANK() OVER( PARTITION BY EMPID ORDER BY Min(EffectiveDate)) RN

    FROM MyCTE

    GROUP BY

    EMPID,

    PrimaryRater

    )

    SELECT a.EMPID,

    a.StartDate,

    a.PrimaryRater,

    CASE WHEN a.StartDate = a.EndDate THEN b.StartDate ELSE a.EndDate END,

    ISNULL( NULLIF(a.StartDate, a.EndDate), b.StartDate)

    FROM Aggregated a

    LEFT

    JOIN Aggregated b ON a.EMPID = b.EMPID AND a.RN = b.RN - 1

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you very much.

    That was the solution I was looking for.

    I appreciate the help.

  • One more question/example.

    If I have the following table:

    EMPIDEffectiveDate PrimaryRater PrimaryReviewer

    1234510/10/2001 A12345 A67890

    1234507/12/2013 A12345 B67890

    1234508/18/2002 A12345 C67890

    1234507/17/1966 A12345 D67890

    1234501/01/1966 B12345 E67890

    Business rule(s):

    1. The EffectiveDate is the sysdate of the update. It does not distinguish between the EffectiveDate of change for either the Primary Rater/Reviewer.

    2. The only way to determine who was actually changed is to compare the IDs of the PrimaryRater and PrimaryReviewer at the time of the change with the previous PrimaryRater or PrimaryReviewer.

    So, in this example the EffectiveDate of 01/01/1966 is the effective date of both Rater and Reviewer because there was no record prior. EffectiveDate 07/17/1966 is the EffectiveDate of a new set of Rater/Reviewers. Thus the Start and End dates of the previous are:

    EMPIDStartDate PrimaryRater PrimaryReviewer EndDate

    1234501/01/1966 B12345 E67890 07/17/1966

    PrimaryRater A12345 has an EffectiveDate of 07/17/1966 but no EndDate. All other EffectiveDates are for the Reviewer not the Rater. Thus he has no EndDate and is still the most current Rater.

    How would I create this table.

    EMPIDEffectiveDate PrimaryRater PrimaryReviewer EndDate

    1234507/17/1966 A12345 B67890 Current (No EndDate)

    1234501/01/1966 B12345 E67890 07/17/1966

    B67890 is the most Current Reviewer based on MAX date 07/12/2013

    A12345 is the most Current Rater based on Start Date of 07/17/1966

    B12345 & E67890 are the most Previous set based on EffectiveDate 01/01/1966 with no Previous sets.

    The example you provided is excellent for gaining the date sets for Raters but not for adding in the most current Reviewer.

    What modification would add? Thanks in advance for your considerations and help on this matter.

  • Would this help?

    WITH SampleData([EMPID],[EffectiveDate],[PrimaryRater], [PrimaryReviewer])

    AS

    ( --This is sample data

    SELECT '12345',convert(datetime,'10/10/2001'),'A12345', 'A67890' UNION ALL

    SELECT '12345','07/12/2013','A12345', 'B67890' UNION ALL

    SELECT '12345','08/18/2002','A12345', 'C67890' UNION ALL

    SELECT '12345','07/17/1966','A12345', 'D67890' UNION ALL

    SELECT '12345','01/01/1966','B12345', 'E67890'

    )

    --Solution starts here

    ,CTE AS(

    SELECT EMPID,

    PrimaryRater,

    MIN( EffectiveDate) StartDate,

    MAX( EffectiveDate) EndDate,

    DENSE_RANK() OVER( PARTITION BY EMPID ORDER BY Min(EffectiveDate)) RN

    FROM SampleData

    GROUP BY EMPID,

    PrimaryRater

    )

    SELECT c.EMPID,

    c.StartDate,

    c.PrimaryRater,

    s.PrimaryReviewer,

    ISNULL( c2.StartDate, GETDATE()) EndDate

    FROM CTE c

    JOIN SampleData s ON c.EMPID = s.EMPID

    AND c.PrimaryRater = s.PrimaryRater

    AND c.EndDate = s.EffectiveDate

    LEFT

    JOIN CTE c2 ON c.EMPID = c2.EMPID

    AND c.RN = c2.RN - 1

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply