SQL & PL/SQL

Hi,

We are working on a data warehousing project and am wondering how to join multiple tables each of which are versioned independently(SCD type 2 with a valid from and valid to date).

For eg, we get our client information (client id , name etc) from one source and the client rating information from another source.  Since the sources are different,we have separate tables for them and each of them gets versioned independently.

Here is my Client table ( with its own valid from and valid to columns).

And similarly the Client Rating has the ID and the rating information.

I would like to merge the above two tables, and present the information in a single view.  I am having some difficulty in determing the valid from and valid to columns.

And this is the query I used to get the above output:

SELECT client.id,

       client.name,

       crm.tier,

       Greatest(client.vld_fm, crm.vld_fm),

       Least(client.vld_to, crm.vld_to)

FROM   client client,

       client_rating crm

WHERE  client.id = crm.id

       AND ( client.vld_fm <= crm.vld_fm

             AND client.vld_fm <= crm.vld_to

             AND client.vld_fm >= crm.vld_fm

             AND client.vld_fm >= crm.vld_to )

        OR ( client.vld_fm BETWEEN crm.vld_fm AND crm.vld_to )

        OR ( client.vld_to BETWEEN crm.vld_fm AND crm.vld_to );

The problem really is we have multiple sources of data (and each with its own versioning) and then the joins become very very complex.  Is there a better way of writing the query ?

Or perhaps a better way of designing our tables?

Thanks for your help.

Anand