May 3, 2004 at 9:48 am
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
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
Sorry, that I did not mention: The first and last record for each day for each person
May 3, 2004 at 12:35 pm
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
Just what I Need. Thanks for the solution.
Bye
May 3, 2004 at 10:23 pm
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy