SQLTeam.com | Weblogs | Forums

Price Updation as per Date


#1

I have two table T1 and T2. T1 contains the daily data and T2 is the master. Now situation is
if I am updating a new price in item2 with effective from T1 price should get updated based on effective data

Please suggest

T1
Item_No
Sale_DateDate
Price

T2
Item_No
Price
Effective_From


#2

If you want the update to T1 to happen automatically, you will need to use a trigger on Table T2. That would guarantee the updates. Alternatively, you can update T1 manually, e.g.

Update T2 Set Price = 123, Effective_From = '20160101' WHERE Item_No = 456;
Update T1 Set Price = 123 WHERE ITem_No = 456 and Sale_DateDate >= '20160101';

The problem with the latter, of course, is that if you forget to update T1, the data can go out of sync.


#3

Is there a join query for this


#4

Do you mean this?

UPDATE t1 SET
	t1.Price = t2.Price
FROM
	Table1 AS T1
	INNER JOIN Table2 AS T2
		ON T1.Item_No = T2.Item_No
		AND T1.Sale_DateDate >= t2.Effective_From;