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.
>>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&&step1 >> >>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&&step2 >> >>step3 >>select and sum from the last steps and replace sum with ty2010 where id1=3 >>>>
>>>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 && similar tag >>>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 >>>>>>
>>>>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 >>>> >>>>>>>>
>>>>>>>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 >>>>>>>>>>>>>>
>>>>>>>>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 >>>>>>>>>>>>>>>>
>>>>>>>>>>>>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 >>>>>>>>>>>>>>>>>>>>>>