Home Forums SQL Server 2008 SQL Server Newbies Finding the MIN and MAX date - How do I approach this? RE: Finding the MIN and MAX date - How do I approach this?

  • December 10, 2013 at 1:31 pm

    #1672429

    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!