Ruleset examples - event external identifier

This article contains typical examples on rules related to event external identifiers.

Database descriptions can be found here

Select regular working hours and event external identifier

External identifier is selected as CustomData1

SELECT
  usd.*,
  SUM(usd.Amount) AS OverrideAmount,
  uexid.ExternalIdentifier AS CustomData1
FROM
  UserSalaryData usd
LEFT JOIN UserEventExternalIdentifierData uexid ON
  uexid.ActivityId = usd.ActivityId
  AND uexid.IdentifierType = 'External'
WHERE
  usd.CompensationType = 'BasicTime'
  AND usd.BasePartTarget IN ('salary','bank','balance','flexiblehours')
  AND (usd.OvertimeTarget IS NULL OR usd.OvertimeTarget = 'uncompensated')
  AND usd.ActivityTypeCode = 1 
GROUP BY
  usd.SalaryRenderingDate,
  usd.ActivityId

Select regular working hours only from events that have an external identifier

External identifier is selected as CustomData1

SELECT
  usd.*,
  SUM(usd.Amount) AS OverrideAmount,
  uexid.ExternalIdentifier AS CustomData1
FROM
  UserSalaryData usd
JOIN UserEventExternalIdentifierData uexid ON
  uexid.ActivityId = usd.ActivityId
WHERE
  usd.CompensationType = 'BasicTime'
  AND usd.BasePartTarget IN ('salary','bank','balance','flexiblehours')
  AND (usd.OvertimeTarget IS NULL OR usd.OvertimeTarget = 'uncompensated')
  AND usd.ActivityTypeCode = 1
  AND e.IdentifierType = 'External'
GROUP BY
  usd.SalaryRenderingDate,
  usd.ActivityId

Select regular working hours only from events that have no external identifier

SELECT
  usd.*,
  SUM(usd.Amount) AS OverrideAmount
FROM
  UserSalaryData usd
LEFT JOIN UserEventExternalIdentifierData uexid ON
  uexid.ActivityId = usd.ActivityId
  AND uexid.IdentifierType = 'External'
WHERE
  usd.CompensationType = 'BasicTime'
  AND usd.BasePartTarget IN ('salary','bank','balance','flexiblehours')
  AND (usd.OvertimeTarget IS NULL OR usd.OvertimeTarget = 'uncompensated')
  AND usd.ActivityTypeCode = '1'
  AND uexid.ActivityId IS NULL
GROUP BY
  usd.SalaryRenderingDate