Effective Dates Generation
An effective dates generation rule is an ad hoc rule that generates related date data for two columns. The effective dates are sequences of dates that consist of start dates and end dates. You can create effective dates rules with the date data type.
An effective dates generation rule generates a random start date within a range. The rule creates an end date that is after the start date and another start date that immediately follows the end date. The number of rows in the grouping column that have the same value determines the number of dates in the effective dates sequence.
The column in which you create the rule must be either the effective start date or the effective end date. If you create the rule on the effective start date column, you must select a different column as the effective end date column. If you create the rule on the effective end date column, you must select a different column as the effective start date column. The rule generates date values for both columns. You select the grouping column to generate sequences of dates in multiple rows.
The following image shows the effective dates generation parameters:
Effective Dates Generation Parameters
Create effective dates generation rule for the date data type.
The following table describes the parameters that you can configure to generate data for effective dates:
Parameter | Description |
|---|
Start Date/Time | The date and time at which you can start to generate dates. |
End Date/Time | The date and time at which you can stop to generate dates. |
Exclude | The days of the week that you do not want to include in the generated dates. You can enter up to two days, and both the days cannot be same. |
Effective Start Date | The column name in the target table where you want to put the generated effective start date. Default is the column name in which you create the effective dates generation rule. You can change it to any other available column name. |
Effective End Date | The column name in the target table where you want to put the generated effective end date. You can select any column other than the effective start date. |
Grouping Column | The column that determines how you group the data to generate effective dates in the target columns. |
Invalid Values | Any value with effective end date greater than the effective start date. If effective start date is less than effective end date of the previous record for the grouping column, the values are not valid. This parameter has default rule selected and you cannot change the rule. You enter the percent for values that are not valid. |
Effective Dates Generation Example
You want to generate start dates and end dates for customer addresses.
The target table has two customers. Each customer has one identification number. The table has three address rows for each customer. You choose a date range to generate a random effective start date for each customer. You select an effective start date column to put the start date into. You select an effective end date column to put an end date into. You select the customer identification number as the grouping column.
The rule generates a random start date for the first address row for each customer. The rule generates a random end date that occurs after the start date. The rule generates a start date for the next row that is after the end date of the previous row.
The following table shows a sample table with the generated data:
Customer ID | Address | Start Date | End Date |
|---|
1111 | 55 Crestwood Dr | 11/20/2001 | 06/17/2003 |
1111 | 200 Larkin St | 06/18/2003 | 04/16/2004 |
1111 | 1000 Cooke Ln | 04/17/2004 | 08/16/2012 |
2222 | 12708 Danbrooke Ct | 03/06/2008 | 05/19/2010 |
2222 | 1200 S Sixth Ave | 05/20/2010 | 07/06/2011 |
2222 | 2243 Creeksie Ct | 07/07/2011 | 11/24/2013 |
