In some business scenarios, it is required to create a report listing all the dates and the related events happening on each date, when the records are not stored in the data warehouse directly. Instead, there are only two tables that are related: one table is LU_DAY which stores Day ID and DESC; the other is LU_COMPANY_RANK which stores the Company Name, relative Company Rank and its term of validity that is identified by StartDate and EndDate, as shown below.
And the data in table LU_COMPANY_RANK is as below:
Follow the steps below to create a report to meet the requirement:
1. Create a logical view to join LU_DAY and LU_COMPANY_RANK. The key here is to get all the valid dates for each Company Rank by using the conditionality below:
Where a12.Day_Date>=a11.Test_Start_Date and a12.Day_date<=a11.Test_End_Date
Select a11.Company_DESC, a11.Company_Rank,
a12.Day_Date
from LU_COMPANY_RANK a11,
LU_DAY a12
Where a12.Day_Date>=a11.Test_Start_Date and a12.Day_date<=a11.Test_End_Date
