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)
