Need some with query

23 Nov 2005 11:49 PM

Rick Shaw

Hi, I need some help formulating a query. Below is an example records.
What I am trying to do is to come up with a query that will tell me which
record has a position_startdate that is less than position_enddate from the
record above.

ID    EMP_ID    POSITION        POSITION_STARTDATE POSITION_ENDDATE
--    ----------    ------------        ---------------------------- ------
-------------------
18    18                SALES            1/21/03
6/5/04
25    18                SALES MGR   6/5/04
12/28/04
31    18                DEPT SUP       10/18/04
8/8/05
45    18                STORE MGR    8/8/05
null

In this example, the query should pickup ID = 31 since 10/18/04 is less than
the previous postion_enddate which is 12/28/04.

Also, I am running this query with tons or employees.

I am currently working on this and thought that I might post it and get a
quick response back.

Thanks,

JB..

24 Nov 2005 12:07 AM

Stijn Verrept

Rick Shaw wrote:

> Below is an example
> records. What I am trying to do is to come up with a query that will
> tell me which record has a position_startdate that is less than
> position_enddate from the record above.

You should provide more information to get a better answer but I think
this should do it, assuming that when you say 'record above' you mean a
record with a lower ID.

Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
T2.Position_EndDate from Table T2 where T2.ID < T1.ID order by T2.ID
Desc)

--

HTH,

Stijn Verrept.

24 Nov 2005 12:26 AM

Stijn Verrept

Stijn Verrept wrote:

Show quote

> Rick Shaw wrote:
>
> > Below is an example
> > records. What I am trying to do is to come up with a query that
> > will tell me which record has a position_startdate that is less than
> > position_enddate from the record above.
>
> You should provide more information to get a better answer but I think
> this should do it, assuming that when you say 'record above' you mean
> a record with a lower ID.
>
> Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
> T2.Position_EndDate from Table T2 where T2.ID < T1.ID order by T2.ID
> Desc)

Maybe

Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
T2.Position_EndDate from Table T2 where T2.ID < T1.ID and T1.Emp_ID =
T2.Emp_ID order by T2.ID
Desc)

is better, depends if you need to compare it with the same Emp_ID or not

--

Kind regards,

Stijn Verrept.

24 Nov 2005 3:42 PM

Rick Shaw

Thanks Stijn. It worked great.

Thanks again for the hand.

Rick..

Show quote

"Stijn Verrept" <st***@entrysoft.com> wrote in message
news:v7KdnRH9lLMwlRjenZ2dnUVZ8qudnZ2d@scarlet.biz...
> Stijn Verrept wrote:
>
> > Rick Shaw wrote:
> >
> > > Below is an example
> > > records. What I am trying to do is to come up with a query that
> > > will tell me which record has a position_startdate that is less than
> > > position_enddate from the record above.
> >
> > You should provide more information to get a better answer but I think
> > this should do it, assuming that when you say 'record above' you mean
> > a record with a lower ID.
> >
> > Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
> > T2.Position_EndDate from Table T2 where T2.ID < T1.ID order by T2.ID
> > Desc)
>
> Maybe
>
> Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
> T2.Position_EndDate from Table T2 where T2.ID < T1.ID and T1.Emp_ID =
> T2.Emp_ID order by T2.ID
> Desc)
>
> is better, depends if you need to compare it with the same Emp_ID or not
>
> --
>
> Kind regards,
>
> Stijn Verrept.