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
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)
