Create new rows in a table in the script

[#1]

Hi,

I have a challenging task that I would like to get your input to solve!

I am trying to create new rows with specific data dependent on a certain logic. Below I have tried to simplify what I am after.

I have the following tables:

transactions

product_id	amount	date
123	200	2012-01-25
123	-200	2012-02-09

product conversion date

product_id	product_conversion_date
123	2012-02-05

If the "product_id" converts to another product I want the transactions to move to the new product type and I want the new transactions table to look like this:

transactions_new

product_id	amount	date	product
123	200	2012-01-25	A
123	-200	2012-02-05	A
123	200	2012-02-05	B
123	-200	2012-02-09	B

Note: The product conversions are only from A to B so no logic is needed to termine that.

Any idéas how I can do this?

Cheers

Anna

[gandalfgray · Accepted solution]

Hi Anna

(assuming your first example transaction record has "product: A" and the second "product: B")

I did it with a loop over the product_conversion_date table.

There may be other ways as well.

see attachment!

[#3]

Thanks!!