Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help needed to find minimum in a series
Sandy80 schrieb:
> I think I have not been able to explain my question well enough. I
> will try one more time.
>
> In table 1 which is the current table the userid 1234 has his status
> as A001, so we need to look for the max date of his status A001 in
> table 2.
> Then reading backwards we need to find out which is the minimum date
> for that status in table 2 without the break in status.
> So for 1234 his max date for A001 in table 2 is 01/01/2007, then
> reading backwards we go to 01/01/2006 and that is the minimum date for
> that status because for 01/01/2005 his status was B001 and not A001.
>
> Help please!!!
>
Ok, without testing:
select Table1.Userid, Table1.Status, min(Table2.StartDate)
from Table1 inner join Table2
on Table1.Userid = Table2.Userid
and Table1.Status = Table2.Status
and not exists (select Userid
from Table2 Table2DiffStatus where Table2DiffStatus.Userid = Table2.Userid and Table2DiffStatus.Status != Table2.Status and Table2DiffStatus.StartDate > Table2.StartDate)group by Table1.Userid, Table1.Status;
HTH
Thomas
-- For answers by personal mail use: thomas.sommerfeld at domain ust-gmbh.deReceived on Wed Apr 18 2007 - 08:00:01 CDT