PedroCGD
Dont worry, maybe I didnt explain you correctly... I will try it again...:-)
Imagine this tables:
FactTable |
|
|
|
Date |
бн |
бн |
Rate |
10-10-2005 |
|
|
|
10-10-2005 |
|
|
|
12-10-2005 |
|
|
|
DimensionTable |
|
Date |
Rate |
10-10-2005 |
1,234 |
14-10-2005 |
2,8 |
15-10-2005 |
2,3 |
20-10-2005 |
3,1 |
For example I have a lookup to check each rate for my factTable based on each date, and currency (but forget the currency right now).
and the result is:
FactTable |
|
|
|
Date |
бн |
бн |
Rate |
10-10-2005 |
|
|
1,234 |
10-10-2005 |
|
|
1,234 |
12-10-2005 |
|
|
|
and the date 12-10-2005 was not found...
In this cases I need to get the next date, and in this case, for the date 12-10-2005, the next date is 14-10-2005.
I created the query
SELECT
TOP 1 Data, Currencies_Name, RevalRate
FROM Spot_Rates
WHERE (Currencies_Name = @MyCurrency) AND (Data > CONVERT(DATETIME, @MyDate, 102))
ORDER BY Data ASC
that gives me for each date, the next date and the respective revalrate... (forget the currency, isn't to much important)
So, I need the final result like:
FactTable |
|
|
|
Date |
бн |
бн |
Rate |
10-10-2005 |
|
|
1,234 |
10-10-2005 |
|
|
1,234 |
12-10-2005 |
|
|
2,8 |
Understood
How can I do it :-)
Using a script component that exexute the query and returns the output for the dataflow, I thing is one option.. what you think I never used script component as a transformation...
Regards!