In my load script, I would need to append new records to this SalesPrices table. Those records need to be all those ItemRef that do not have a PriceListName betweenStartingDate and EndDate.
I have thought about selecting all distinct PriceListName values in a separate table (let us name it TariffNames).
For every ItemRef in SalesPrices, I need to check that every PriceListName in TariffNames exist in SalesPrices. For every non-existent PriceListName in SalesPrices, I need to add a new record in SalesPrices with:
The ItemRef field,
01/01/2013 as StartingDate,
31/12/2013 as EndingDate,
PriceListName (non-existing one)
Price (zero value)
How can I look for those non existing values and the TariffNames table, and append those records to SalesPrices table?