How to do a lookup with a range of date

ufl_developer · Post by **ufl_developer** » Fri Mar 19, 2010 7:49 am

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!

chulett · Post by **chulett** » Fri Mar 19, 2010 8:10 am

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Mar 19, 2010 8:41 am

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.

ufl_developer · Post by **ufl_developer** » Fri Mar 19, 2010 9:40 am

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

ufl_developer · Post by **ufl_developer** » Fri Mar 19, 2010 9:42 am

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!

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.

chulett · Post by **chulett** » Fri Mar 19, 2010 10:00 am

ufl_developer wrote:This is quite an interesting way to handle this case and it is also pretty complex.

As noted, though, it's a "one time complex" in my experience. After that, not so much.

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.