>Hi all,
>
>I have mytable :
>
>mytable.dbf
>
>YearMonth ( character field )
>
>200609
>200609
>200608
>200608
>200607
>200607
>200606
>200606
>200605
>200605
>200604
>200604
>200603
>200603
>200602
>200602
>200601
>200601
>200512
>200512
>200511
>200511
>200510
>200510
>200509
>200509
>200508
>200508
>200507
>200507
>200506
>200506
>
>to get only last 12 months :
>
>newtable ( 2 fields )
>
>yearmonth MaxMiniMonth
>200609 12
>200608 11
>200607 10
>200606 9
>200605 8
>200604 7
>200603 6
>200602 5
>200601 4
>200512 3
>200511 2
>200510 1
>
>Select yearmonth from mytable group by yearmonth
>
>How to get MaxMiniMonth in my select SQL ?
>
>TIA

CREATE CURSOR MyTable (yearmonth C(6))
INSERT INTO MyTable VALUES('200609')
INSERT INTO MyTable VALUES('200609')
INSERT INTO MyTable VALUES('200608')
INSERT INTO MyTable VALUES('200608')
INSERT INTO MyTable VALUES('200607')
INSERT INTO MyTable VALUES('200607')
INSERT INTO MyTable VALUES('200606')
INSERT INTO MyTable VALUES('200606')
INSERT INTO MyTable VALUES('200605')
INSERT INTO MyTable VALUES('200605')
INSERT INTO MyTable VALUES('200604')
INSERT INTO MyTable VALUES('200604')
INSERT INTO MyTable VALUES('200603')
INSERT INTO MyTable VALUES('200603')
INSERT INTO MyTable VALUES('200602')
INSERT INTO MyTable VALUES('200602')
INSERT INTO MyTable VALUES('200601')
INSERT INTO MyTable VALUES('200601')
INSERT INTO MyTable VALUES('200512')
INSERT INTO MyTable VALUES('200512')
INSERT INTO MyTable VALUES('200511')
INSERT INTO MyTable VALUES('200511')
INSERT INTO MyTable VALUES('200510')
INSERT INTO MyTable VALUES('200510')
INSERT INTO MyTable VALUES('200509')
INSERT INTO MyTable VALUES('200509')
INSERT INTO MyTable VALUES('200508')
INSERT INTO MyTable VALUES('200508')
INSERT INTO MyTable VALUES('200507')
INSERT INTO MyTable VALUES('200507')
INSERT INTO MyTable VALUES('200506')
INSERT INTO MyTable VALUES('200506')

*** I assume that you have some variables which define the period you need
m.lcPeriodBegin = [200510]
m.lcPeriodEnd   = [200609]
SELECT yearmonth,;
       INT((VAL(LEFT(yearmonth,4))*12+VAL(RIGHT(yearmonth,2)))-;
       (VAL(LEFT(m.lcPeriodBegin,4))*12+VAL(RIGHT(m.lcPeriodBegin,2)))+1) AS MaxMini;
FROM MyTable;
GROUP BY yearmonth;
WHERE yearmonth BETWEEN m.lcPeriodBegin AND m.lcPeriodEnd;
ORDER BY yearmonth DESC;
INTO CURSOR crsTest
BROWSE NORMAL