add calculated column to datatable 2

[DotNetGnat]

Hello All,

I have a datatable as shown below

Date | price
_________________
1/1/2010 | 10
1/2/2010 | 11
1/3/2010 | 9
1/4/2010 | 12

I want to add a column to this datatable to result

Date | price | change
______________________
1/1/2010 | 10 |
1/2/2010 | 11 | 1
1/3/2010 | 9 | -2
1/4/2010 | 12 | 3

Any suggestions...

Thanks

-DNG

 

[jbenson001]

I would do this in the database if possible. Also I would add some sort of key as well.

 

[DotNetGnat]

thanks for your input. I also intially thought of doing this at the database level but later changed my mind. Currently in my web app, I generate Datatable on the fly specific for each user based on their inputs. If I could save this table on the database and run sp's on it and return calculated values..which by the way sounds good...but what if I have 100 simultaneous users??

Do you have any suggestions...

-DNG

 

[DotNetGnat]

by the way the datatable I generating is sorted by date asc...

-DNG

 

[jmeckley]

var table = GetDataTable();
table.Columns.Add("difference", typeof(int));

if(table.Rows.Count < 2) return table;

for(var i=1; i<table.Rows.Count-1; i=i+2)
{
   var currentRow = table.Rows[i];
   var previous = (int)table.Rows[i-1]["price"];
   var current = (int)currentRow["price"];
   currentRow["difference"] = current-previous;
}
return table;

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[ca8msm]

If your database is in Oracle, you can use the LAG function or if it is SQL Server, you can use a Common Table Expression (CTE).

I'd do some testing to find out which method performs the fastest.

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[DotNetGnat]

Thank you Jason...with little tweaking it worked fine...

ca8msm, thanks for the info.

-DNG