Greetings!
I'm having a hard time to figure out how to do a lookup with a range of date. Here is the detail:
from an incoming row, i have a specific date, say, budget_dt, and then I want to get the budget_period, which is in a calendar type table, in the calendar table, I only have a begin_dt and end_dt, so the lookup condition will be: begin_dt <= budget_dt < end_dt. It seems pretty simple lookup, but I don't have a way to get this lookup to work.
Can anyone help?
p.s. I don't have Enterprise Edition ( I heard that there is range_lookup built in with enterprise).
Thanks in advance!
How to do a lookup with a range of date
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 15
- Joined: Wed Feb 20, 2008 3:33 pm
I've done this in the past and it works great. Now, it does take a little bit of dedicated time to check it out and fully grok... err, understand it, then get it setup on your system but once you do it's cake to re-use and works as advertised. Nice, speedy range lookups of any kind.
I'm sure there are other favorite techniques out there but wanted to point this one out to folks.
Two snaps up from me.
:D
I'm sure there are other favorite techniques out there but wanted to point this one out to folks.
Two snaps up from me.
:D
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Since you used the magic word - Table - you will be able to do the relational operator directory in the corresponding RDBMS stage.
In simple steps
1.) Copy the budget date in 2 separate columns - say budget_dt1, budget_dt2
2.) In the lookup, mention both begin_dt and end_dt as key values
3.) Link budget_dt1 and 2 to the begin and end dates
4.) Login to the database stage and change the SQL to match your relational operation.
In simple steps
1.) Copy the budget date in 2 separate columns - say budget_dt1, budget_dt2
2.) In the lookup, mention both begin_dt and end_dt as key values
3.) Link budget_dt1 and 2 to the begin and end dates
4.) Login to the database stage and change the SQL to match your relational operation.
-
- Premium Member
- Posts: 15
- Joined: Wed Feb 20, 2008 3:33 pm
This is quite an interesting way to handle this case and it is also pretty complex.
chulett wrote:I've done this in the past and it works great. Now, it does take a little bit of dedicated time to check it out and fully grok... err, understand it, then get it setup on your system but once you do it's cake to re-use and works as advertised. Nice, speedy range lookups of any kind.
I'm sure there are other favorite techniques out there but wanted to point this one out to folks.
Two snaps up from me.
:D
-
- Premium Member
- Posts: 15
- Joined: Wed Feb 20, 2008 3:33 pm
Very good suggestion. this should work just fine.
What I did was join this "calendar" table to another full calendar and put them in a hash file, so the lookup will become very straightforward.
Thanks!
What I did was join this "calendar" table to another full calendar and put them in a hash file, so the lookup will become very straightforward.
Thanks!
Sainath.Srinivasan wrote:Since you used the magic word - Table - you will be able to do the relational operator directory in the corresponding RDBMS stage.
In simple steps
1.) Copy the budget date in 2 separate columns - say budget_dt1, budget_dt2
2.) In the lookup, mention both begin_dt and end_dt as key values
3.) Link budget_dt1 and 2 to the begin and end dates
4.) Login to the database stage and change the SQL to match your relational operation.
As noted, though, it's a "one time complex" in my experience. After that, not so much.ufl_developer wrote:This is quite an interesting way to handle this case and it is also pretty complex.
Like all things, there's more than one way to skin a cat, good to see you found something that worked for you. The "calendar table/dimension" seems like it should be on the endangered species list lately, good to see you had one to solve this with.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers