Hi there,
I need to join 2 tables, one of which contains a 'From Date' and 'To Date' column which indicates the date range that the record is active for. Example below illustrated with Table A and Table B, both records should associate.
There are millions of records in Table A, and although I know it is possible to create a calendar table with a iterative loop to populate 30 dates (for 1 April to 30 April), there are concerns with scalability as each record in Table A could require 30 - 360 dates within a year.
Are there other ways or best practice to join such tables with a 'From Date' 'To Date' concept?