Set Date To Ansi
Set Century On
Set Safety Off
CLOSE DATABASES
Create Table tt1 (cname c(6),cyear c(6), Cmonth c(6),nnewsal N (9,2))
Insert Into tt1 Values ("hans", "1998","2",300)
Insert Into tt1 Values ("hans", "1998","3",300)
Insert Into tt1 Values ("hans", "1998","4",300)
Insert Into tt1 Values ("hans", "1998","5",300)
Insert Into tt1 Values ("hans", "1998","6",300)
Insert Into tt1 Values ("hans", "1998","7",1400)
Insert Into tt1 Values ("hans", "1998","8",1400)
Insert Into tt1 Values ("hans", "1998","9",1400)
Insert Into tt1 Values ("hans", "1998","10",1400)
Insert Into tt1 Values ("hans", "1998","11",1400)
Insert Into tt1 Values ("hans", "1998","12",1400)
Insert Into tt1 Values ("hans", "1999","1",1400)
Insert Into tt1 Values ("hans", "1999","2",1400)
Insert Into tt1 Values ("hans", "1999","3",1400)
Insert Into tt1 Values ("hans", "1999","4",1400)
Insert Into tt1 Values ("hans", "1999","5",1400)
Insert Into tt1 Values ("hans", "1999","6",1400)
Insert Into tt1 Values ("hans", "1999","7",500)
Insert Into tt1 Values ("hans", "1999","8",500)
Insert Into tt1 Values ("hans", "1999","9",1200)
Insert Into tt1 Values ("hans", "1999","10",1200)
Insert Into tt1 Values ("hans", "1999","11",1200)
Insert Into tt1 Values ("hans", "1999","12",1200)
Insert Into tt1 Values ("hans", "2000","1",1200)
Insert Into tt1 Values ("hans", "2000","2",1200)
Insert Into tt1 Values ("hans", "2000","3",500)
Insert Into tt1 Values ("hans", "2000","4",500)
Insert Into tt1 Values ("hans", "2000","5",500)
Insert Into tt1 Values ("hans", "2000","6",500)
Insert Into tt1 Values ("hans", "2000","7",500)
Insert Into tt1 Values ("hans", "2000","8",500)
Insert Into tt1 Values ("hans", "2000","9",500)
Insert Into tt1 Values ("hans", "2000","10",500)
Insert Into tt1 Values ("hans", "2000","11",500)
Insert Into tt1 Values ("hans", "2000","12",500)
Insert Into tt1 Values ("hans", "2001","1",500)
 
 
Insert Into tt1 Values ("dick", "1999","5",200)
Insert Into tt1 Values ("dick", "1999","6",200)
Insert Into tt1 Values ("dick", "1999","7",800)
Insert Into tt1 Values ("dick", "1999","8",800)
Insert Into tt1 Values ("dick", "1999","9",800)
Insert Into tt1 Values ("dick", "1999","10",800)
Insert Into tt1 Values ("dick", "1999","11",800)
Insert Into tt1 Values ("dick", "1999","12",800)
Insert Into tt1 Values ("dick", "2000","1",800)
Insert Into tt1 Values ("dick", "2000","2",800)
Insert Into tt1 Values ("dick", "2000","3",800)
Insert Into tt1 Values ("dick", "2000","4",800)
Insert Into tt1 Values ("dick", "2000","5",800)
Insert Into tt1 Values ("dick", "2000","6",800)
Insert Into tt1 Values ("dick", "2000","7",1500)
Insert Into tt1 Values ("dick", "2000","8",1500)
Insert Into tt1 Values ("dick", "2000","9",1500)
Insert Into tt1 Values ("dick", "2000","10",1500)
Insert Into tt1 Values ("dick", "2000","11",1500)
Insert Into tt1 Values ("dick", "2000","12",1500)
Insert Into tt1 Values ("dick", "2001","1",1500)


* 说明：对tt1表 按名字、按规定的时间段归纳，时间段是这样的，按每年的7月1日至次年的6月30日为一段，如tt1表中，该名字（hans）的第一个时间不是7月1日的，该时间为开始时间（1998-2），则第一段是1998-2-1至1998-6-30，第二段是1998-7-1至1999-6-30，第三段是1999-7-1至2000-6-30，而，tt1表中，hans的最后时间是2001-1，那么，第四段是2000-7-1至2001-1-31；
* dick的分段如此类推。


* 最后结果
CREATE table tt2 (cname c(6), date1 d ,date2 d, nnewsal n (9,2))
INSERT INTO tt2 VALUES ("hans", {^1998-2-1},{^1998-6-30},300) 
INSERT INTO tt2 VALUES ("hans", {^1998-7-1},{^1999-6-30},1400)
INSERT INTO tt2 VALUES ("hans", {^1999-7-1},{^2000-6-30},1500)
INSERT INTO tt2 VALUES ("hans", {^2000-7-1},{^2001-1-31},5000)


INSERT INTO tt2 VALUES ("dick", {^1999-5-1},{^1999-6-30},200)
INSERT INTO tt2 VALUES ("dick", {^1999-7-1},{^2000-6-30},800)
INSERT INTO tt2 VALUES ("dick", {^2000-7-1},{^2001-1-31},1500)

解决方法

*----------------------------------------------------------------
* 方法一：VFP+SQL方法


if type("tt1.nCnt")="U"
    alter table tt1 add ncnt N(3)
endif
if type("tt1.date1")="U"
    alter table tt1 add date1 D
endif
replace all date1 with gomonth(date(val(cyear),val(cmonth),1),-6)
go top


* 1-Scatter Name 属性方法


*!*	scatter Name obj
*!*	lncnt=obj.ncnt
*!*	scan
*!*	   if (nnewsal!=obj.nnewsal) or (year(date1)!=year(obj.date1)) or (cname!=obj.cname)
*!*	       scatter Name obj
*!*	       lncnt=lncnt+1
*!*	   endif
*!*	   replace nCnt with lncnt
*!*	endscan


* 2-Scatter Memvar 同名变量方法
scatter memvar
lncnt=m.ncnt
scan
    if (nnewsal!=m.nnewsal) or (year(date1)!=year(m.date1)) or (cname!=m.cname)
        scatter memvar
        lncnt=lncnt+1
    endif
    replace ncnt with lncnt
endscan
select cname,gomonth(min(date1),6) date1, gomonth(max(date1),7)-1 date2,nnewsal,ncnt from tt1 group by ncnt,cname