Thanks ... that's what I needed. / Jim
>>Sergey --
>>
>>I always start from the current month and look backward -- for right now, at least, I'm only looking back one year.
>>
>>So, indeed, if 200412 were missing for a partno, I'd want to get 2004121 as my result for that partno.
>
>Hi Jim,
>
>There're a few ways you can do that. You can create a cursor with list of last 12 periods with second column representing previous period and than run a query
CREATE CURSOR Parts (partno C(2), YYYYMM n(6), YYYYMMprev N(6))
>... Insert records here for the last 12 periods (month)
>INSERT VALUES (200412, 200411)
>...
>INSERT VALUES (200401, 200312)
>
>SELECT pn.partno, MAX(cp.YYYYMM) ;
>FROM Parts pn ;
> JOIN crsPeriods cp ON cp.YYYYMMprev = pn.YYYYMM ;
> LEFT JOIN Parts pn2 ON pn.partno = pn2.partno ;
> AND cp.YYYYMM = pn2.YYYYMM ;
> WHERE pn2.partno IS NULL ;
>GROUP BY pn.partno
>
You can also follow Hilmar's suggestions.
Jim Nelson
Newbury Park, CA