search for changed prices

  • Thread starter Thread starter Bernie Yaeger
  • Start date Start date
B

Bernie Yaeger

I have a table with items, perhaps 500 or so. Inside each item is the
monthly price running approx 36 months. I want to loop through the table to
get only months where the price changed for each item. So, if item a had a
price of 12.95 jan 2001 and 12.95 feb 2001 but 13.95 mar 2001, I only want
that row; however if apr 2001 was 13.95 and may 2001 was 13.50, I also want
may 2001, as there was a price change in that month also.

Any ideas how to put these into datatables or dataviews and then loop
through them to find this info?

Tx for any help.

Bernie Yaeger
 
How's the table structured? If it's a flat structure, your strategy will
much different then if you have a PK/FK type structure. However, let's say
that you have a parent table with an order number and a few other details.
In the child table you have all fo the records with their prices. You can
use a datarelation to link the tables on Order number. Then, whenver the
item changes, you can simply loop through the Dataview and test that all of
the Prices are the same. if not, then your criteria is met. not sure if
you want the parent record 'selected' or if you want the child records
highlighted. You'd need to determine the baseline if it's the latter so you
know what 'changed' and what to highlight.

The key things you'll want to use are any/all of the following.

DataTable.Select, DataView.RowFilter DataColumn.Expression

This link will help you along
http://www.ondotnet.com/pub/a/dotnet/2003/05/26/datacolumn_expressions.html

If you can show a basic example of the table(s) structure(s), then I can be
of more help. I'll be up for a while tomorrow, and around all day tomorrow.

Cheers,

Bill
 
Try this select (replace :

select * from table where row.price <> (select row.price from table as t
where date = dateadd("m", -1, row.date)
 
Hi Bill,

Thanks for your response. I had actually been trying to use both
datatable.select and dataviews to help solve the problem, but I came up with
a simple 'for i' increment loop with a datatable.select datarow loop inside
it and it's now working fine.

Tx again,

Bernie
 
Hi Miha,

Thanks for your response. I actually misled you, I'm afraid, because I
wanted to simplify the question. In actuality, some items have jan, feb,
mar but others have jan, mar, apr, june/july, etc, so it's too uneven for
the approach you recommended - but it's a perfect approach for other such
requirements. In any event, I did develop an outer and inner loop structure
that gets the job done.

Thanks again,

Bernie
 
Back
Top