Hello everyone,
I need to link two tables with dates. The first table (Table 1) contains the price of the product, the product code and the account and the second table (Table 2) contains the number of price transactions, the product code and the account. The price of the product (Table 1) can be changed throughout the month. The only date on Table 2 is the beginning of the month (1st of the month) as this is when the price transactions is executed.
The price used in Table 2 will be the last available price for the product in Table 1 i.e. if there were two prices during the month the latest price needs to be used.
If a price date in Table 1 is before the 21st of the month then the price is effective in Table 2 from the beginning of the next month (1st of the month). If the price date in Table 1 is after or the 21st of the month then the price is effective in Table 2 from the beginning of month +2.
Is anyone able to assist with the required Script to link this two dates and price as explained above? I have started trying to link these two Tables with the below code:
if(mid(EffectiveDate,9,2)<21,makedate(left(EffectiveDate,4),(mid(EffectiveDate,6,2)+1),01),makedate(left(EffectiveDate,4),(mid(EffectiveDate,6,2)+2),01)) as [ToDate],
Regards,
Michael
