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
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, ...?
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
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...
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.