CODEWITHSUNDEEP
oracle-database
Cheese Daneish
Cheese Daneish

Reputation: 1030

Select records from another table between result set dates

I have a result set like this (I have to execute a query to get it from a contract table):

OBJECT_ID     START_DT     END_DT

1             01/01/2011   12/31/2011
2             08/08/2008   09/30/2013
3             10/01/2008   10/31/2013
..
..

That specifies the dates that some objects were active on a contract.

I also have another table (call it OBJ_EVENTS) like this:

OBJECT_ID     EVENT_DATE     EVENT_TYPE

1             10/10/2010     FOO EVENT
1             01/05/2011     BAR EVENT
1             01/01/2012     FOO EVENT
2             08/09/2008     FOO EVENT
4             10/10/2010     BAZ EVENT

That table tells me when some object did things I might be interested in. But, I only care about events between the contract dates START_DT and END_DT for the objects in my contract result set. So, if that was my whole OBJ_EVENTS table, my desired result set looks like this:

OBJECT_ID     EVENT_DATE     EVENT_TYPE

1             01/05/2011     BAR EVENT --between Obj 1's contract dates 01/01/2011-12/31/2011
2             08/09/2008     FOO EVENT --between Obj 2's contract dates 08/08/2008-09/30/2013

Is it possible to accomplish this in one SQL query or do I need to do some kind of cursor? I don't have a ton of sql experience.

Upvotes: 0

Views: 244

Answers (1)

Politank-Z
Politank-Z

Reputation: 3729

You could use the query you're using to obtain your first result set as a sub-query, something like:

select oe.OBJECT_ID, oe.EVENT_DATE, oe.EVENT_TYPE
from OBJ_EVENTS oe inner join
( *result set query here*) rs
on oe.OBJECT_ID = rs.OBJECT_ID and
  oe.EVENT_DATE between rs.START_DT and rs.END_DT;

That might not be the best way to do it, but it should get the job done. In particular, if your result set doesn't change often, it may be worth caching that query's results in a regular table. Whether it changes often or not, if the result set is referenced often, it could be useful to create a view for it.

Upvotes: 2

Related Questions