To create a data export for employees' emergency contacts, or employees who don't have emergency contacts, follow the steps below.
In the Admin Tool, click Import/Export data.
Click Create an export definition.
Click Select the data to export.
From the Database drop-down menu, select Select HR.
Click the SQL tab then delete Select*.
Copy the relevant SQL from the sections below into the blank space.
Select the relevant filter criteria, then click the Data tab.
Click Key Field at the top-left, then select Person Number.
Click OK, then review the data.
Once you're happy with the data, in the Definition field, enter a name for the export.
Next to Export to file location, click Browse then choose where to save the export.
From the File Format drop-down menu, select the relevant option.
If required, select the Export column headers check box.
Click Next then select the relevant options.
Click Next then click Finish.
Current employees with emergency contacts
SELECT TOP 100 PERCENT pSS.[Person Number], pSS.[Effective Status], pSS.Surname, pSS.[First Name], pSS.[E-Mail], pSS.[Location Name], pSS.[Post Name], pEC.Relationship, pEC.[Emergency Contact], pEC.Salutation, pEC.[Formal Name], pEC.[E-Mail] AS [Contact Email], pEC.[Private E-Mail], pEC.[Mobile Phone Number], pEC.[External Job Title], pEC.[External Company Name], pEC.[Address Block], pEC.[Address Line 1], pEC.[Address Line 2], pEC.[Address Line 3], pEC.[Address Line 4], pEC.[Address Line 5], pEC.[Post Code], pEC.[Phone Number] FROM Person.Snapshot pSS INNER JOIN Person.[Emergency Contacts] pEC ON pSS.[Person Number] = pEC.[Person Number] WHERE pSS.[Effective Status] = 'Current' ORDER BY pSS.Surname
Current employees with no emergency contacts
SELECT TOP 100 PERCENT PSS.[Person Number], PSS.[Effective Status], PSS.Surname, PSS.[First Name], PSS.[E-Mail], PSS.[Location Name], PSS.[Post Name], PEC.Relationship, PEC.[Emergency Contact], PEC.Salutation, PEC.[Formal Name], PEC.[E-Mail] AS [Contact Email], PEC.[Private E-Mail], PEC.[Mobile Phone Number], PEC.[External Job Title], PEC.[External Company Name], PEC.[Address Block], PEC.[Address Line 1], PEC.[Address Line 2], PEC.[Address Line 3], PEC.[Address Line 4], PEC.[Address Line 5], PEC.[Post Code], PEC.[Phone Number] FROM Person.Snapshot PSS LEFT JOIN Person.[Emergency Contacts] PEC ON PSS.[Person Number] = PEC.[Person Number] WHERE PSS.[Effective Status] = 'current' AND PEC.[Person Number] IS NULL ORDER BY PSS.Surname