Can anyone plz help on this
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, ...?
Dieter
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
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.
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...
Dieter
Can u plz get me one sample query for this one. It would be helpfull here
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
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.
Please get me sample query for this case.
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