history handled output required

Database

history handled output required

Hi ,

We have two tables with below data in given date intervals  and the output required is as single report  with its individual valid date intervals. Can anyone help on the query after joining Table A and Table B

Table A:

Cust_id   Currency   Start_date      End_date

1             USD         2014-01-01   9999-12-31

Table B:

Cust_id  Country   Start_date      End_date

1             US         2014-05-02   9999-12-31

Final Output required:

Cust_id   Currency  Country   Start_date       End_date

1            USD          NULL      2014-01-01    2014-05-01

1             USD         US          2014-05-02    9999-12-31

Tags (1)
9 REPLIES

Re: history handled output required

Can anyone plz help on this
N/A

Re: history handled output required

You didn't specify any rules how you determine the expected result set.

E.g. what if there are multiple/no rows per cust_id(?) per table, what if they are conflicting, ...?

Re: history handled output required

Yes we can also have duplicates ón cust_id, Table A and B  will have cust attributes for each customer with  different intervals also and here cust_id is the key which can be utilzed for picking the attributes 

Table A:

Cust_id   Currency   Start_date      End_date

1             USD         2014-01-01   2014-02-20

1             TWD         2014-02-21   9999-12-31

Table B:

Cust_id  Country   Start_date      End_date

1             US         2014-05-02   9999-12-31

Final Output required:

Cust_id   Currency  Country   Start_date       End_date

1            USD          NULL      2014-01-01    2014-02-20

1            TWD          NULL      2014-02-21    2014-05-01

1            TWD          US        2014-05-02    9999-12-31

Re: history handled output required

Hi Dieter,

We need achieve a resut set in such a way all possible values for each Cust_id must be retrived with start date and end date.

N/A

Re: history handled output required

This looks similar to maintaining a Slowly Changing Dimension, this might involve quite complex logic.

Maybe it's enough to do a UNION ALL and then apply some logic using OLAP to the previous rows value...

Re: history handled output required

Can u plz get  me one sample  query  for this one. It  would  be helpfull here 

Re: history handled output required

Hi Suji,

Can you give some more details about the 3rd result. How you mapped US to TWD? Also what is the rule to get country as NULL for the first 2 results. Usually currency as USD will map to country as US and currency as TWD to some other country. Please elaborate?

1            TWD          US        2014-05-02    9999-12-31

Re: history handled output required

There is no basic rule in the country or currency mapped to cust_id. Table A and Table B are two standalone tables , which has the cust_id as common key. We have to create  a view which will show the cust_id with its county and currency valid for different time intervals. If one of the attribute  is not valid for a time interval , it must  be stapped as NULL.

Re: history handled output required

Please get me sample query for this case.