Post reply

First and last record

  • May 3, 2004 at 9:48 am

    #101991

    OK, first this is the situation:

     

    Table with two fields date and person, in the same day I found multiple record for each person (the date is complete, including minutes ans seconds), so this look like this

     

    23/04/2004 10:00:00                      Peter

    23/04/2004 10:07:15                      Peter

    and so on (Also with different users)

    What I need is the first and the last record for each person, I hope that the info is OK.

  • May 3, 2004 at 10:43 am

    #505026

    Don't know if this is what you are looking for, but try this.

    Declare @t1 table (

     i datetime,

     k varchar(5)

    )

    insert into @t1 (i, k) values (getDate(), 'Peter')

    insert into @t1 (i, k) values (getDate(), 'Paul')

    insert into @t1 (i, k) values (DateAdd(mi, 5, getDate()), 'Peter')

    insert into @t1 (i, k) values (DateAdd(mi, 5, getDate()), 'Paul')

    insert into @t1 (i, k) values (DateAdd(mi, 10, getDate()), 'Peter')

    insert into @t1 (i, k) values (DateAdd(mi, 10, getDate()), 'Paul')

    SELECT * FROM @T1

    SELECT MAX(I), MIN(I), K from @T1 GROUP BY K

  • May 3, 2004 at 12:15 pm

    #505028

    Sorry, that I did not mention: The first and last record for each day for each person

  • May 3, 2004 at 12:35 pm

    #505029

    Try This

    Declare @t1 table (

     i datetime,

     k varchar(5)

    )

    insert into @t1 (i, k) values (getDate(), 'Peter')

    insert into @t1 (i, k) values (getDate(), 'Paul')

    insert into @t1 (i, k) values (DateAdd(mi, 5, getDate()), 'Peter')

    insert into @t1 (i, k) values (DateAdd(mi, 5, getDate()), 'Paul')

    insert into @t1 (i, k) values (DateAdd(mi, 10, getDate()), 'Peter')

    insert into @t1 (i, k) values (DateAdd(mi, 10, getDate()), 'Paul')

    insert into @t1 (i, k) values (DateAdd(d, 1, getDate()), 'Peter')

    insert into @t1 (i, k) values (DateAdd(d, 1, getDate()), 'Paul')

    insert into @t1 (i, k) values (DateAdd(d, 1, DateAdd(mi, 5, getDate())), 'Peter')

    insert into @t1 (i, k) values (DateAdd(d, 1, DateAdd(mi, 5, getDate())), 'Paul')

    insert into @t1 (i, k) values (DateAdd(d, 1, DateAdd(mi, 10, getDate())), 'Peter')

    insert into @t1 (i, k) values (DateAdd(d, 1, DateAdd(mi, 10, getDate())), 'Paul')

    SELECT * FROM @T1

    SELECT MAX(I), MIN(I), k from @T1 GROUP BY day(i), k

  • May 3, 2004 at 12:47 pm

    #505030

    Just what I Need. Thanks for the solution.

    Bye

  • May 3, 2004 at 10:23 pm

    #505066

    It would work in case you have data for one month. If you have data for multiple months, I think you need to do it as follows:

    SELECT MAX(I), MIN(I), k from @T1 GROUP BY month(i),day(i), k


    Regards,
    Amit Khan

    Ontrack Systems Limited
    276B Lake Gardens
    Kolkata - 700045
    India
    Phone - 91-33-24178434,35
    Fax - 91-33-24221274
    Mobile - 91-33-9830105090

  • May 4, 2004 at 7:12 am

    #505126

    good point.  and actually to be completely correct, you would have to add year to that group by too.  nice catch.

Viewing 7 posts - 1 through 6 (of 6 total)

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