Re: Help needed to find minimum in a series

From: Thomas Sommerfeld <TSommerfeld_at_gmx.de>
Date: Wed, 18 Apr 2007 15:00:01 +0200
Message-ID: <f054vl$df0$1@online.de>

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.de

Received on Wed Apr 18 2007 - 08:00:01 CDT