thanks again
Is Name + Month combination unique? yes
And each Name repeats for each month?yes
>No, it's not a good approach - sorry. If you need to update YTD field for all months, then use the idea from my other message (just one simple scan).
>
>Once again - how do you match the records by month? Is Name + Month combination unique? And each Name repeats for each month?
>
>>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)
