Returns the historical date for a Person based upon the parameters below providing a way to get specific type of dates for a person (earliest employment date for example). If no date is found for a given type and code, then the DB Precision invalid date (1/1/1800) will be returned.
GETHISTDATE(DateType, DateCode, LookupType, IgnoreEst, StartDate (optional), EndDate (optional))
DateType: refer to the following list for a description of DateType definitions.
- 0: Employment
- 1: Employment Classification
- 2: Earnings Type
- 3: Union Classification
- 4: Employee DB Contributions
- 5: Officer Status
- 6: Ownership Status
- 7: Pre-retirement Death Coverage
- 8: Employee Key Status
- 9: Employee HCE Status
- 10: Employee DC Contributions
- 11: Credited Service Override
- 12: Vesting Service Override
- 13: Participation Service Override
- 14: Plan Entry Date
DateCode: refer to the following list for a description of DateCode definitions.
- 0: Employment
- 0: Employed
- 1: Terminated - No Reason Given
- 2: Terminated - Voluntary
- 3: Terminated - Involuntary
- 4: Terminated - Layoff
- 5: Terminated - Plant Closing
- 6: Terminated - Severance Begins
- 7: Terminated - Severance Ends
- 8: Disabled - While Off the Job
- 9: Disabled - While On the Job
- 10: Leave of Absence - Authorized
- 11: Leave of Absence - Military
- 12: Leave of Absence - Maternity/Paternity
- 13: Leave of Absence - Medical
- 14: Leave of Absence - Unpaid
- 15: Leave of Absence - Paid
- 16: Disabled - Short-term
- 17: Disabled - Long-term
- 18: Disabled - Workers' Compensation
- 1: Employment Classification
- 0: Full-time
- 1: Part-time
- 2: Temporary
- 3: Commissioned
- 4: Leased
- 5: Per-Diem
- 6: Seasonal
- 2: Earnings Type
- 3: Union Classification
- 4: Employee DB Contributions
- 0: Start of Contributions
- 1: Revoked Payroll Deduction
- 5: Officer Status
- 0: Officer
- 1: Non-officer
- 6: Ownership Status
- 0: Non-owner
- 1: 1% Owner
- 2: 5% Owner
- 7: Pre-retirement Death Coverage
- 0: Elected Death Coverage
- 1: Revoked Death Coverage
- 8: Employee Key Status
- 9: Employee HCE Status
- 10: Employee DC Contributions
- 0: Start of Contributions
- 1: Stop of Contributions
- 11: Credited Service Override
- 0: Start of Accruals
- 1: Stop of Accruals
- 12: Vesting Service Override
- 0: Start of Accruals
- 1: Stop of Accruals
- 13: Participation Service Override
- 0: Start of Accruals
- 1: Stop of Accruals
- 14: Plan Entry Date
- 0: Entered Plan 1
- 1: Entered Plan 2
- 2: Entered Plan 3
- 3: Entered Plan 4
- 4: Entered Plan 5
LookupType: this value may be positive or negative. Positive values (1, 2, 3, etc.) will give the first, second, third, etc. date for a person. Negative values (-1, -2, -3, etc.) will give the last, second-to-last, third-to-last, etc. date.
IgnoreEst: TRUE if ignoring dates that are estimated.
StartDate: Earliest date to use when looking for this date. Leave blank or use DATE(1, 1, 1800) if no earliest date.
EndDate: Latest date to use when looking for this date. Leave blank or use DATE(1, 1, 1800) if no latest date.
GETHISTDATE(0, 0, 1, TRUE): will lookup the earliest employment date while ignoring estimated dates
GETHISTDATE(0, 1, -1, TRUE): will lookup the latest termination date while ignoring estimated dates
