i'm new datatables , enumerated lists in c#. i've looked lot of can't seem anywhere problem, is.... have simple table dates,code,price see codes ticker symbols. i'm trying calculate return or change in price between date , next date i.e. date t+1 /date t specific code. adding table.
i'm not sure if datatable or enumerated set way go, none less tried datatable first, setup below worked create table:
private static datatable getdata(dynamic dataset) { datatable tbl = new datatable(); tbl.columns.add("evaldate", typeof(datetime)); tbl.columns.add("code", typeof(string)); tbl.columns.add("price", typeof(double)); tbl.columns.add("return", typeof(double)); (int irow=2;irow<dataset.getlength(0); irow++) { if (dataset[irow,1] == null) { break; } datetime evaldate = datetime.fromoadate((double)(dataset[irow, 1])); string code = (string)(dataset[irow, 2]); double price = (double)dataset[irow,3]; tbl.rows.add(evaldate,code,price,null); } return tbl; } but tried use lists solve problem :
var dataset = getrng("a1:e50000", "data"); datatable tbl = getdata(dataset); var uniquecodes = (from items in tbl.asenumerable() select items["code"]).distinct().tolist(); list<object> running = new list<object>(); foreach (var code in uniquecodes) { var ts = (from items in tbl.asenumerable() orderby items.field<datetime>("evaldate") items.field<string>("code") == (string)code select items); ts.elementat(0).setfield<double>("priceret", 1); (int idx = 1; idx < ts.count(); idx++) { double price0 = ts.elementat(idx - 1).field<double>("price"); double price1 = ts.elementat(idx).field<double>("pice"); double delta = price1 / price0; ts.elementat(idx).setfield<double>("priceret", delta); } running.add(ts); it didn't crash, it's hopelessly slow , inefficient.
the data looks this: 
my sql knowledge good, struggling turn workable code above. appreciated.
so achieved desired outcome, through sql.... used following statement/query :
select row_number() on (partition by[code] order [evaldate]) [id], [evaldate], [code], [price] #temp mytable code in ('aaa','bbb','ccc'); -- list of codes calc return ... select t1.[evaldate],t1.[code],t1.[price]/t.[price] [delta] #temp t1 inner join #temp t on t1.id = t.id +1 , t1.code = t.code drop table #temp this correctly gives me unpivoted list, applicable change in price each change in date.
i have expected datatable easy , quick sql?
read answer question. code uses dynamic objects, , makes slow. think find getdata() function bottleneck, , if can re-factor code can pass getdata statically known data type, run faster.
