thank you very much,
but i hope to help me again if you have a time.
it works.
i try as below i hope this help to rewrit with new code
SELECT ID1 ,tY2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=2 AND ID>0 into cursor crs1 readwrite
replace all Row with recno() in crs1
brow
SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=3 AND ID>0 into cursor crs2 readwrite
replace all Row with recno() in crs2
select c2.ID1, c2.Y2010 + c1.tY2010 as Total from crs2 c2 ;
inner join crs1 c1 on c2.Row = c1.Row into cursor crsResult nofilter
update NewTest set ty2010 = c1.Total ;
from NewTest inner join crsResult c1 on NewTest.ID1 = c1.ID1
local lnRecNo
lnRecno = 1
select NewTest
scan for ID1 = 3
if between(m.lnRecno, 1, reccount('crsResult'))
goto lnRecno in crsResult
replace ty2010 with crsResult.Total
endif
lnRecno = lnRecno + 1
endscan>Sorry, if you want to go this route, you may try it yourself.
>
>Still, how exactly do you want to match records for each month? The Row approach I took is wrong, because I didn't use any order.
>
>>thank you for idea,
>>this code work for id1=1 and id1=2
>>
>>SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=1 AND ID>0 into cursor crs1 readwrite
>>replace all Row with recno() in crs1
>>
>>SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=2 AND ID>0 into cursor crs2 readwrite
>>replace all Row with recno() in crs2
>>
>>select c2.ID1, c2.Y2010 + c1.Y2010 as Total from crs2 c2 ;
>>inner join crs1 c1 on c2.Row = c1.Row into cursor crsResult nofilter
>>
>>
>>you can help me to
>>make another 2 select as
>>if id1=3
>>select id1,ty2010 wher id1=2
>>
>>SELECT ID1 ,Y2010, cast(0 as int) as Row FROM NEWTEST WHERE ID1=3 AND ID>0 into cursor crs2 readwrite
>>replace all Row with recno() in crs2
>>
>>step3
>>select and sum from the last steps and replace sum with ty2010 where id1=3
>>
>>
>>>Ok, here is a quick idea - you need to take it and adjust.
>>>
>>>create cursor crsCummulative(Month N(2), Name C(10), YTD N(20,3))
>>>
>>>index on str(Month) + Name tag MonthName
>>>
>>>select NewTest
>>>set order to MonthName
>>>scan while not eof()
>>> if seek(str(ID1) + Nam, 'csrCummulative', 'MonthName')
>>> replace YTD with crsCummulative.YTD + NewTest.y2010 in crsCummulative
>>> else
>>> insert into crsCummulative values (NewTest.ID1, NewTest.Nam, NewTest.y2010)
>>> endif
>>> replace TY2010 with crsCummulative.YTD
>>>endscan
>>>
>>>
>>>>thank you for reply
>>>>may be a one scan solution will be better?? how this is the qustion
>>>>
>>>>i need to update just the newtest as what i ask before, thank you for code
>>>>
>>>>
>>>>m.tnNumber1=1
>>>> m.tnNumber2=2
>>>> m.tnNumber2=3
>>>>.
>>>>.
>>>>.
>>>> m.tnNumber2=12
>>>>
>>>> SELECT ID1 ,Y2010 FROM NEWTEST WHERE ID1=m.tnNumber1 AND ID>0
>>>>id1 y2010
>>>> 1 10.3
>>>> 1 20
>>>> 1 15
>>>>
>>>> SELECT ID1 ,Y2010 FROM NEWTEST WHERE ID1=m.tnNumber2 AND ID>0
>>>>
>>>>id1 y2010
>>>> 2 12
>>>> 2 11.5
>>>> 2 18
>>>>
>>>>i need to sum y2010 where ID1=m.tnNumber1 + y2010 where ID1=m.tnNumber2 and replace at the same table to field ty2010
>>>>
>>>>id1 ty2010
>>>>2 22.3
>>>>2 31.5
>>>>2 33
>>>>
>>>>*****************************************if id1=3
>>>>
>>>>
>>>> SELECT ID1 ,Y2010 FROM NEWTEST WHERE ID1=m.tnNumber3 AND ID>0
>>>>
>>>>id1 y2010
>>>> 3 4
>>>> 3 3
>>>> 3 6
>>>>
>>>>now
>>>>id1 ty2010
>>>>3 27.3
>>>>3 34.5
>>>>3 39
>>>>
>>>>.
>>>>.
>>>>.
>>>>.
>>>>id1 ty2010
>>>>12 12
>>>>12
>>>>
>>>>
>>>>
>>>>>I understand, that it may work, but you don't need to use scan/endscan in that code at all. Removing scan/endscan should produce the same result.
>>>>>
>>>>>Anyway, use the idea from my last post to do the same for your NewTest. I'm not sure what is GroupId field in your case, it may be Nam field.
>>>>>
>>>>>Also, be aware that performance may suffer with this solution - may be a one scan solution will be better.
>>>>>
>>>>>>hi,
>>>>>>Sorry the last code i send it works ,
>>>>>>
>>>>>>i want to show how i get values monthly and replace ment at newtest.
>>>>>>
>>>>>>
>>>>>>i need to update just the newtest as what i ask before, thank you for code
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Sorry - it looks like you don't need to use SCAN in that code, but anyway, you lost me.
>>>>>>>
>>>>>>>Let's assume this structure
>>>>>>>
>>>>>>>GroupID Month Value YTD (values we want to have)
>>>>>>>1 1 10 10
>>>>>>>2 1 15 15
>>>>>>>1 2 20 30
>>>>>>>2 2 5 20
>>>>>>>
>>>>>>>
>>>>>>>select T1.GroupID, (select sum(Value) from myTable T2 where T2.GroupID = T1.GroupID and T2.Month <=T1.Month ) as YTD
>>>>>>>from myTable T1
>>>>>>>
>>>>>>>
>>>>>>>>hi,
>>>>>>>>
>>>>>>>>evrye values for filelds partofname is 2009 it is old values just i used to compare ,
>>>>>>>>
>>>>>>>>but every month i fill(replace) the newtest form another table have 6 values via this code
>>>>>>>>
>>>>>>>>SET DELETED ON
>>>>>>>>USE newtest
>>>>>>>>UPDATE newtest SET y2010 = T.val1 from newtest inner join hamad T on newtest.ID = T.ID WHERE id1=2
>>>>>>>>
>>>>>>>>UPDATE newtest SET PER1= (Y2010-Y2009)/Y2009 from newtest WHERE ID1=2
>>>>>>>>*brow
>>>>>>>>
>>>>>>>>***********************************************************************
>>>>>>>>scan
>>>>>>>>
>>>>>>>>SET FILTER TO ID1=2 AND FLG=1
>>>>>>>> SUM Y2010 TO M.D FOR id1=2
>>>>>>>>
>>>>>>>>
>>>>>>>>SET FILTER TO
>>>>>>>> REPLACE Y2010 WITH M.D FOR NAM="TOT1" AND id1=2
>>>>>>>>
>>>>>>>>endscan
>>>>>>>>
>>>>>>>>scan
>>>>>>>>
>>>>>>>>SET FILTER TO ID1=2 AND FLG=2
>>>>>>>> SUM Y2010 TO M.D FOR id1=2
>>>>>>>>
>>>>>>>>
>>>>>>>>SET FILTER TO
>>>>>>>> REPLACE Y2010 WITH M.D FOR NAM="TOT2" AND id1=2
>>>>>>>> endscan
>>>>>>>>
>>>>>>>>
>>>>>>>>scan
>>>>>>>>
>>>>>>>>SET FILTER TO ID1=2 AND FLG=3
>>>>>>>> SUM Y2010 TO M.D FOR id1=2
>>>>>>>>
>>>>>>>>
>>>>>>>>SET FILTER TO
>>>>>>>> REPLACE Y2010 WITH M.D FOR NAM="TOT3" AND id1=2
>>>>>>>> endscan
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>The output above was not really helpful. Do you match records by name? In other words, for each month you have many records. How do you match each month?
>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>>Field Field Name Type Width Dec Index Collate Nulls Next Step
>>>>>>>>>>>> 1 ID1 Numeric 3 No
>>>>>>>>>>>> 2 ID Numeric 2 No
>>>>>>>>>>>> 3 NAM Character 10 No
>>>>>>>>>>>> 4 Y2009 Numeric 15 3 No
>>>>>>>>>>>> 5 Y2010 Numeric 15 3 No
>>>>>>>>>>>> 6 PER1 Numeric 15 3 No
>>>>>>>>>>>> 7 TY2009 Numeric 15 3 No
>>>>>>>>>>>> 8 TY2010 Numeric 15 3 No
>>>>>>>>>>>> 9 TPER Numeric 15 3 No
>>>>>>>>>>>> 10 FLG Numeric 2 No
>>>>>>>>>>>>** Total ** 108
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>Ok, and also some data (copy to 'temp.txt' delimited with tab)
