Post reply

how to write sql select with min/max?

  • October 11, 2005 at 12:14 pm

    #89477

    I have a set of rows from a select that looks as follows

    Resource task taskStart taskend

    -------------------------------------------------

    John Doe task1a 09/01/05 12/01/05

    John Doe task1c 8/01/05 10/01/05

    Sue Smith task1b 7/01/05 8/1/05

    Sue Smith task1c 9/1/05 11/01/05

    ----------------------------------------------

    I'd like to get a result table as follows

    Resource taskMinStart taskMaxend

    -------------------------------------------------

    John Doe 08/01/05 12/01/05

    Sue Smith 7/01/05 11/01/05

    Any suggestions will be appreciated.

  • October 11, 2005 at 12:25 pm

    #596624

    DECLARE @t TABLE( Resource varchar(25), Task varchar(10), TaskStart smalldatetime, TaskEnd smalldatetime)

    INSERT INTO @t

    SELECT 'John Doe', 'task1a', '09/01/05', '12/01/05' UNION ALL

    SELECT 'John Doe', 'task1c', '8/01/05', '10/01/05' UNION ALL

    SELECT 'Sue Smith', 'task1b', '7/01/05', '8/1/05' UNION ALL

    SELECT 'Sue Smith', 'task1c', '9/1/05', '11/01/05'

    SELECT Resource, CONVERT( varchar(10), MIN(  TaskStart), 101) AS TaskMinStart, CONVERT( varchar(10), MAX( TaskEnd), 101) AS TaskMaxEnd

    FROM @t

    GROUP BY Resource

    I wasn't born stupid - I had to study.

  • October 11, 2005 at 1:42 pm

    #596648

    Thank you.  that works nicely.

  • October 11, 2005 at 2:50 pm

    #596663

    Gald to help.

    I wasn't born stupid - I had to study.

Viewing 4 posts - 1 through 3 (of 3 total)

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