请教，如何把aa表分解成aa1表？
各位老师，俺想把以下aa表以整年日期期限表示的表格，分解成aa1按月份表示的表格，请各位老师指导、赐教，感谢。
CLOSE ALL
clear
Create Table aa (cname c (12),date1 D,date2 D,rate n(8,2))
INSERT INTO aa VALUES ("dd1",{^2013-06-15},{^2014-06-14},1500 )
INSERT INTO aa VALUES ("dd2",{^2013-08-25},{^2014-08-24},1600 )
INSERT INTO aa VALUES ("dd3",{^2013-09-01},{^2014-08-31},1700 )
use aa
brow
Create Table aa1 (cname c (12),date1 D,date2 D,rate n(8,2))
INSERT INTO aa1 VALUES ("dd1",{^2013-06-15},{^2013-07-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2013-08-15},{^2013-09-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2013-09-15},{^2013-10-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2013-10-15},{^2013-11-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2013-11-15},{^2013-12-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2013-12-15},{^2014-01-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2014-01-15},{^2014-02-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2014-02-15},{^2014-03-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2014-03-15},{^2014-04-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2014-04-15},{^2014-05-14},1500 )
INSERT INTO aa1 VALUES ("dd1",{^2014-05-15},{^2014-06-14},1500 )
INSERT INTO aa1 VALUES ("dd2",{^2013-08-25},{^2013-09-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2013-09-25},{^2013-10-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2013-10-25},{^2013-11-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2013-11-25},{^2013-12-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2013-12-25},{^2014-01-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2014-01-25},{^2014-02-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2014-02-25},{^2014-03-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2014-03-25},{^2014-04-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2014-04-25},{^2014-05-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2014-05-25},{^2014-06-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2014-06-25},{^2014-07-24},1600 )
INSERT INTO aa1 VALUES ("dd2",{^2014-07-25},{^2014-08-24},1600 )
INSERT INTO aa1 VALUES ("dd3",{^2013-09-01},{^2013-09-30},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2013-10-01},{^2013-10-31},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2013-11-01},{^2013-11-30},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2013-12-01},{^2013-12-31},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2014-01-01},{^2014-01-31},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2014-02-01},{^2014-02-28},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2014-03-01},{^2014-03-31},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2014-04-01},{^2014-04-30},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2014-05-01},{^2014-05-31},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2014-06-01},{^2014-06-30},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2014-07-01},{^2014-07-31},1700 )
INSERT INTO aa1 VALUES ("dd3",{^2014-08-01},{^2014-08-31},1700 )
use aa1
brow
CLOSE ALL
CLEAR
[解决办法]
自己修改一下不就行了
SELECT A.CNAME,;
IIF(b.id=1,a.date1,GOMONTH(A.DATE1,B.ID-1)) as newdate1,;
IIF(A.PM=B.ID,A.DATE2,GOMONTH(A.DATE1,B.ID)-1) AS ADATE2;
,rate FROM SS A INNER JOIN LSB B ON A.PM>=B.ID
[解决办法]
Close Data
Create Table aa (cname c (12),date1 D,date2 D,rate N(8,2))
Insert Into aa Values ("dd1",{^2013-06-15},{^2014-06-14},1500 )
Insert Into aa Values ("dd2",{^2013-08-25},{^2014-08-24},1600 )
Insert Into aa Values ("dd3",{^2013-09-01},{^2014-08-31},1700 )
Create Table aa1 (cname c (12),date1 D,date2 D,rate N(8,2))
Select aa
Scan
Scatter Memv
Do While m.date1<m.date2
If Gomonth(m.date1,1)<m.date2
Insert Into aa1 Values (m.cname,m.date1,Gomonth(m.date1,1)-1,m.rate )
Else
Insert Into aa1 Values (m.cname,m.date1,m.date2,m.rate )
Endif
m.date1=Gomonth(m.date1,1)
Enddo
Endscan
