Hi Steve,

Maybe I'm missing something, but according to your explanation, there could be from 2 to 4 inspection for last 3 years per building with 12 month cycle. It doesn't look like selecting those inspections by year would work.

>I have a table of buiding inspections (insmstr). Each inspection
>is done once a year. The next inpsection is done either 12 or 24
>months from the previous inspection. The inspection can occur
>in any month and can be early or late. (Almost sounds like real
>life.) It has a foreign key of imbdkey which links the inspection
>record to the building. I have another table of buildings
>(buildings). The primary key is building number (bdkey).
>
>
>What I am trying to do is to place the last 3 years inspections
>into one record in one select statement. What I propose to do
>is:
>
>SELECT bd.bdkey, bd.bdname, im.iminspdt, im.iminsptype, ;
> ( SELECT im.iminspdt FROM insmstr imyr WHERE ;
> imyr.iminspdt >= {01/01/2000} AND imyr.iminspdt ;
> <= {12/31/2000} AND imyr.imbdkey = bd.bdkey ) ;
> as YR1date , ;
> ( SELECT im.iminspdt FROM insmstr imyr WHERE ;
> imyr.iminspdt >= {01/01/2001} AND imyr.iminspdt ;
> <= {12/31/2002} AND imyr.imbdkey = bd.bdkey ) ;
> as YR2date , ;
> FROM building bd INNER JOIN insmstr im ON ;
> bd.bdkey = im.imbdkey WHERE im.iminspdt >= {01/01/2002}
>
>Needless to say, the above generates an error.
>
>I know I can achieve the results I want in a lot of ways, but I believe
>that a single SELECT statement may be the fastest way to get all of the
>data I need. The above is a simplfied version of the final statement
>which actually joins about 6 tables and works quite well as long as I
>leave out the attempt to get the year 2000 and 2001 columns. I figure if
>I get the dates in one pass without calling any functions or multi-cursor
>scenerios it would be the fastest way to go. I'm open to dialogue,
>a little mentoring, or even the feedback that it can't be done this way.
>
>Thanks,
>
>sjwilson