Skip to main content

Data Export: Overlapping absence data export

Data export to show overlapping absences.

H
Written by Harry Ledger
Updated over 2 months ago

You can export data showing overlapping absences by following the steps below.

This can be useful for identifying clashes, such as multiple absence records for the same employee.

Create the export

  1. From the Admin Tool, click Import/export data.

  2. Click Create an export definition.

  3. Click Select the data to export.

  4. From the Database drop-down menu, select Select HR.

  5. Click the SQL tab and delete the default SELECT *.

  6. Copy and paste the relevant SQL code from the sections below.

  7. Select any filter criteria you want to apply, then click the Data tab.

  8. Click Key Field in the top-left and select the appropriate option.

  9. Click OK and review the data returned.

  10. Once happy with the results:

    • Enter a name in the Definition field.

    • Next to Export to file location, click Browse and choose where to save the export.

    • Choose a File Format.

    • (Optional) Tick Export column headers if needed.

  11. Click Next and configure any remaining options.

  12. Click Next, then Finish to complete the export.

SelectHR version 3.3 and above

The below SQL code will work for version 3.3, 4.0 and SelectHR Evo.

All absence types (live and pending)

SELECT hd.[Person Number], Person.Details.[Formal Name], hd.[Absence Number], overlap.[Absence Number] AS [Overlapping Absence Number], hd.[Absence Start Date], hd.[Absence End Date], hd.[Absence Type], overlap.[Absence Start Date] AS [Overlap Start Date], overlap.[Absence End Date] AS [Overlap End Date], overlap.[Absence Type] AS [Overlap Absence Type] FROM Absence.[History Data] AS hd INNER JOIN Absence.[History Data] AS overlap ON hd.[Person Number] = overlap.[Person Number] INNER JOIN Person.Details ON hd.[Person Number] = Person.Details.[Person Number] WHERE hd.[Absence Number] != overlap.[Absence Number] AND (overlap.[Absence Start Date] + overlap.[Start Time] < ISNULL(hd.[Absence End Date], '2099-12-31') + ISNULL(hd.[End Time], '23:59') AND ISNULL(overlap.[Absence End Date], '2099-12-31') + ISNULL(overlap.[End Time], '23:59') > hd.[Absence Start Date] + hd.[Start Time]) AND hd.Workflow IN (0, 1) AND overlap.Workflow IN (0, 1)

Sickness absence only (live and pending)

SELECT hd.[Person Number], Person.Details.[Formal Name], hd.[Absence Number], hd.[Absence Start Date], hd.[Absence End Date], hd.[Absence Type], overlap.[Absence Number] AS [Overlap Absence Number], overlap.[Absence Start Date] AS [Overlap Start Date], overlap.[Absence End Date] AS [Overlap End Date], overlap.[Absence Type] AS [Overlap Absence Type] FROM Absence.[History Data] AS hd INNER JOIN Absence.[History Data] AS overlap ON hd.[Person Number] = overlap.[Person Number] INNER JOIN Person.Details ON hd.[Person Number] = Person.Details.[Person Number] WHERE hd.[Absence Number] != overlap.[Absence Number] AND hd.[Absence Type] = 'Sickness' AND overlap.[Absence Type] = 'Sickness' AND overlap.[Absence Start Date] + overlap.[Start Time] < ISNULL(hd.[Absence End Date], '2099-12-31') + ISNULL(hd.[End Time], '23:59') AND ISNULL(overlap.[Absence End Date], '2099-12-31') + ISNULL(overlap.[End Time], '23:59') > hd.[Absence Start Date] + hd.[Start Time] AND hd.Workflow IN (0, 1) AND overlap.Workflow IN (0, 1)

SelectHR version 3.2 and below

The SQL code below will 0nly work for v3.2 and below.

All absence types (live and pending)

SELECT DISTINCT ah.[Absence Number], ah.[Absence Type], ah2.[Absence Type] AS [Overlapping Absence Type], ah.[Person Number], CASE WHEN ah.Workflow = 1 THEN 'Live' WHEN ah.Workflow = 0 THEN 'Pending Authorisation' WHEN ah.Workflow = 2 THEN 'Pending Update' WHEN ah.Workflow = -1 THEN 'Declined' WHEN ah.Workflow = -2 THEN 'Cancelled' WHEN ah.Workflow = -3 THEN 'Soft-deleted' END AS 'Workflow Status', ah.[Absence Start Date], ah.[Absence End Date], CASE WHEN ah2.Workflow = 1 THEN 'Live' WHEN ah2.Workflow = 0 THEN 'Pending Authorisation' WHEN ah2.Workflow = 2 THEN 'Pending Update' WHEN ah2.Workflow = -1 THEN 'Declined' WHEN ah2.Workflow = -2 THEN 'Cancelled' WHEN ah2.Workflow = -3 THEN 'Soft-deleted' END AS 'Workflow Status 2', ah2.[Absence Number] AS [Overlapping Absence Number], ah2.[Absence Start Date] AS [Overlapping Start Date], ah2.[Absence End Date] AS [Overlapping End Date], Person.Details.[Formal Name] FROM absence.history ah INNER JOIN absence.days ad ON ah.[Absence Number] = ad.[absence number] INNER JOIN absence.days ad2 ON ad2.[absence Date] = ad.[Absence Date] INNER JOIN absence.history ah2 ON ah2.[absence number] != ah.[Absence Number] AND ah2.[absence Number] = ad2.[Absence Number] AND ah.[Person Number] = ah2.[Person Number] INNER JOIN Person.Details ON Person.Details.[Person Number] = ah2.[Person Number] WHERE CASE WHEN ah.Workflow = 1 THEN 'Live' WHEN ah.Workflow = 0 THEN 'Pending Authorisation' WHEN ah.Workflow = 2 THEN 'Pending Update' WHEN ah.Workflow = -1 THEN 'Declined' WHEN ah.Workflow = -2 THEN 'Cancelled' WHEN ah.Workflow = -3 THEN 'Soft-deleted' END IN ('LIVE', 'PENDING') AND CASE WHEN ah2.Workflow = 1 THEN 'Live' WHEN ah2.Workflow = 0 THEN 'Pending Authorisation' WHEN ah2.Workflow = 2 THEN 'Pending Update' WHEN ah2.Workflow = -1 THEN 'Declined' WHEN ah2.Workflow = -2 THEN 'Cancelled' WHEN ah2.Workflow = -3 THEN 'Soft-deleted' END IN ('LIVE', 'PENDING')

Sickness absence only (live and pending)

SELECT DISTINCT ah.[Absence Number], ah.[Absence Type], ah2.[Absence Type] AS [Overlapping Absence Type], ah.[Person Number], CASE WHEN ah.Workflow = 1 THEN 'Live' WHEN ah.Workflow = 0 THEN 'Pending Authorisation' WHEN ah.Workflow = 2 THEN 'Pending Update' WHEN ah.Workflow = -1 THEN 'Declined' WHEN ah.Workflow = -2 THEN 'Cancelled' WHEN ah.Workflow = -3 THEN 'Soft-deleted' END AS 'Workflow Status', ah.[Absence Start Date], ah.[Absence End Date], CASE WHEN ah2.Workflow = 1 THEN 'Live' WHEN ah2.Workflow = 0 THEN 'Pending Authorisation' WHEN ah2.Workflow = 2 THEN 'Pending Update' WHEN ah2.Workflow = -1 THEN 'Declined' WHEN ah2.Workflow = -2 THEN 'Cancelled' WHEN ah2.Workflow = -3 THEN 'Soft-deleted' END AS 'Workflow Status 2', ah2.[Absence Number] AS [Overlapping Absence Number], ah2.[Absence Start Date] AS [Overlapping Absence Start Date], ah2.[Absence End Date] AS [Overlapping Absence End Date], Person.Details.[Formal Name] FROM absence.history ah INNER JOIN absence.days ad ON ah.[Absence Number] = ad.[absence number] INNER JOIN absence.days ad2 ON ad2.[absence Date] = ad.[Absence Date] INNER JOIN absence.history ah2 ON ah2.[absence number] != ah.[Absence Number] AND ah2.[absence Number] = ad2.[Absence Number] AND ah.[Person Number] = ah2.[Person Number] INNER JOIN Person.Details ON Person.Details.[Person Number] = ah2.[Person Number] WHERE ah.[Absence Type] = 'SICKNESS' AND ah2.[Absence Type] = 'SICKNESS' AND CASE WHEN ah.Workflow = 1 THEN 'Live' WHEN ah.Workflow = 0 THEN 'Pending Authorisation' WHEN ah.Workflow = 2 THEN 'Pending Update' WHEN ah.Workflow = -1 THEN 'Declined' WHEN ah.Workflow = -2 THEN 'Cancelled' WHEN ah.Workflow = -3 THEN 'Soft-deleted' END IN ('LIVE', 'PENDING') AND CASE WHEN ah2.Workflow = 1 THEN 'Live' WHEN ah2.Wor
Related Articles
Data Export: List of leavers
Total days value is incorrect on the absence history listing
Blank overlapping absence screen displayed during absence entry or request
Data Export: Employee absence history records
Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.
Did this answer your question?