Position_ServiceHistory

Purpose

The Position.ServiceHistory table tracks comprehensive employment and service milestones for employees. It records significant events such as hires, rehires, terminations, position changes, promotions, and service recognition. This table is essential for service anniversary tracking, employment history analysis, and compliance reporting.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
ServiceHistoryID	INT	NO	Primary key, unique identifier	SERVICE_HISTORY	HISTORY_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	SERVICE_HISTORY	EMPL_ID	Direct mapping
EventType	VARCHAR(50)	NO	Type of service event	SERVICE_HISTORY	EVENT_TYPE	Mapped from codes
EventDate	DATE	NO	Date of the event	SERVICE_HISTORY	EVENT_DATE	Direct mapping
EventDescription	VARCHAR(200)	YES	Detailed event description	SERVICE_HISTORY	EVENT_DESC	Direct mapping
PositionID	INT	YES	Foreign key to Position.Position	SERVICE_HISTORY	POSITION_ID	Direct mapping
OldPositionID	INT	YES	Previous position ID	SERVICE_HISTORY	OLD_POS_ID	Direct mapping
OldJobTitle	VARCHAR(100)	YES	Previous job title	SERVICE_HISTORY	OLD_JOB_TITLE	Direct mapping
NewJobTitle	VARCHAR(100)	YES	New job title	SERVICE_HISTORY	NEW_JOB_TITLE	Direct mapping
OldDepartment	VARCHAR(100)	YES	Previous department	SERVICE_HISTORY	OLD_DEPT	Direct mapping
NewDepartment	VARCHAR(100)	YES	New department	SERVICE_HISTORY	NEW_DEPT	Direct mapping
ServiceYears	INT	YES	Years of service at event	SERVICE_HISTORY	SERVICE_YEARS	Calculated
AffiliatedYears	INT	YES	Years of affiliation at event	SERVICE_HISTORY	AFFILIATED_YEARS	Calculated
RecognitionType	VARCHAR(50)	YES	Type of service recognition	SERVICE_HISTORY	RECOGNITION_TYPE	Mapped from codes
RecognitionDate	DATE	YES	Date of recognition	SERVICE_HISTORY	RECOGNITION_DATE	Direct mapping
CreatedDate	DATETIME	NO	Record creation date	N/A	N/A	System generated
ModifiedDate	DATETIME	NO	Last modification date	N/A	N/A	System generated

Source System Mapping

Primary Source: SERVICE_HISTORY Table

Contains service history information
Updated when service events occur
Key fields mapped directly to Position.ServiceHistory

Secondary Sources:

POSITIONS Table
- Position information
- Used for position validation
- Updated when positions change
EMPLOYEES Table
- Employee information
- Used for service calculations
- Updated when employee status changes

Data Transformation Rules

Event Type Mapping

Source Code -> Target Type
'HIRE' -> 'Hire'
'REHIRE' -> 'Rehire'
'TERM' -> 'Termination'
'PROMO' -> 'Promotion'
'TRANS' -> 'Transfer'
'DEMO' -> 'Demotion'
'RECOG' -> 'Service Recognition'

Recognition Type Mapping

Source Code -> Target Type
'5YR' -> '5 Year Service'
'10YR' -> '10 Year Service'
'15YR' -> '15 Year Service'
'20YR' -> '20 Year Service'
'25YR' -> '25 Year Service'
'30YR' -> '30 Year Service'

Service Years Calculation

ServiceYears = DATEDIFF(YEAR, HireDate, EventDate)

Affiliated Years Calculation

AffiliatedYears = DATEDIFF(YEAR, AffiliationDate, EventDate)

Data Quality Rules

Required Fields
- ServiceHistoryID
- EmployeeID
- EventType
- EventDate
- CreatedDate
- ModifiedDate
Validation Rules
- EventDate must be valid
- RecognitionDate must be after EventDate if present
- ServiceYears must be non-negative if present
- AffiliatedYears must be non-negative if present
- PositionID must be valid if present
Business Rules
- Events must be in chronological order
- Service recognition must align with service years
- Position changes must have valid position IDs
- Hire must be first event for new employees
- Termination must be last event for terminated employees

Usage Notes

Reporting Considerations
- Use EventType for event analysis
- Consider ServiceYears for anniversary tracking
- Track position changes over time
- Monitor service recognition milestones
Performance Considerations
- Indexed on ServiceHistoryID, EmployeeID, and EventDate
- Consider filtering on EventType for specific analyses
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Core.Employee via EmployeeID
- Links to Position.Position via PositionID
- Used in service anniversary views
- Source for employment history

Maintenance

Refresh Schedule
- Daily updates for new events
- Full refresh monthly
- Emergency updates for corrections
Archive Strategy
- Historical events maintained in table
- Use EventDate for historical filtering
- Service history tracked for compliance
Monitoring
- Track service events by employee
- Monitor service recognition milestones
- Alert on validation rule violations
- Track position change patterns

Position.ServiceHistory Table Documentation