Script: how to filter to load only if value is in one of several ranges
I have two data sources, one containing transactional data of cost centre and amount, and another parameter table listing the ranges of cost centres that I actually want to load. Records where the cost centre is not in any of the ranges should be dropped.
Transactional Data:
Cost Centre, Amount
1001, 25.7
1002, 45.6
1003, 34.6
1004,45.1
1005, 1.2
1006, 33.9
1007, 33.4
1008, 1.8
1009, 88.4
1010, 5.4
Cost Center Ranges to be loaded:
Min, Max
1002, 1004
1007, 1009
In this simple example, my loaded table would be:
Cost Centre, Amount
1002, 45.6
1003, 34.6
1004,45.1
1007, 33.4
1008, 1.8
1009, 88.4
All I can come up with is to convert the ranges into individual values (e.g. 1002 to 1004 becomes 1002, 1003, 1004) and do a join or where exists. Any suggestions of an easier way to accomplish this? The list of ranges can change so there can be no hard-coding in the script.
