First and last record

Question

Post reply

First and last record

Victor Sebastiani

SSC Veteran

Points: 251
More actions
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.

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

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

Aggie D SSC-Addicted Points: 443 More actions · Answer 1

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

Victor Sebastiani SSC Veteran Points: 251 More actions · Answer 2

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

Aggie D SSC-Addicted Points: 443 More actions · Answer 3

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

Victor Sebastiani SSC Veteran Points: 251 More actions · Answer 4

Victor Sebastiani

SSC Veteran

Points: 251

May 3, 2004 at 12:47 pm

#505030

Just what I Need. Thanks for the solution.

Bye

amitkhan SSC Enthusiast Points: 155 More actions · Answer 5

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

Aggie D SSC-Addicted Points: 443 More actions · Answer 6

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