User Guide‎ > ‎

Business Alert/User Query Samples

Business Alert provides scheduling ability based on monitoring the HRPro database for critical, time-sensitive information, send out alert emails with the most timely data. These queries can also be used in user query. See also How to execute user query?  

The followings are some sample queries for Business Alert/User Query:

1. Holidays in this month

SELECT
[HOLIDAY]=HOLIDAY_DATE,
[DESCRIPTION]=HOLIDAY_DESC
FROM PLHOLIDAY WHERE YEAR(GETDATE())=YEAR(HOLIDAY_DATE) AND MONTH(GETDATE())=MONTH(HOLIDAY_DATE)

2. On Leave Employee(s) Today

SELECT
[EE NAME]=E.SURNAME+' '+E.OTHERNAME,

L.LEAVE_FR,
L.LEAVE_TO,
L.DAYS,
L.LEAVE_CODE
FROM PLLEAVE L

INNER JOIN v_Employee E ON E.EE_NO=L.EE_NO
WHERE DATEDIFF(D,LEAVE_FR,GETDATE())=0 OR (DATEDIFF(D, LEAVE_FR,GETDATE())>=0 AND DATEDIFF(D, LEAVE_TO,GETDATE())<=0)
ORDER BY L.EE_NO

3On Leave Employee(s) Next Week

SELECT
L.EE_NO,
E.DEPT_CODE,
EE_NAME=DBO.EENAME(L.EE_NO),
L.LEAVE_DATE,
L.LEAVE_CODE 
FROM plLeaveDayRecord L
INNER JOIN v_Employee E ON E.EE_NO=L.EE_NO
WHERE
L.LEAVE_DATE > GETDATE()+(7-dbo.DOW(GETDATE()))
ORDER BY LEAVE_DATE, L.EE_NO

4. Last Backup Time

SELECT 
Last_Backup_DateTime=MAX(backup_finish_date),
Days_before=DATEDIFF(D,MAX(backup_finish_date), GETDATE())

FROM msdb.dbo.backupmediafamily m
INNER JOIN msdb.dbo.backupset b ON m.media_set_id=b.media_set_id
WHERE b.database_name = N'dbHRPro'

5. Coming Public Holiday (3 days before)

SELECT
[HOLIDAY]=HOLIDAY_DATE,
[DESCRIPTION]=HOLIDAY_DESC
FROM PLHOLIDAY
WHERE 
REGION='HKG' AND DATEDIFF(d,GETDATE(),HOLIDAY_DATE)=3

6. Employee(s) to be terminated within 3 days 

SELECT
EE_NO,
FULLNAME=DBO.EENAME(EE_NO),
DEPT_CODE,
TERMN_DATE,
REASON_CODE,
TERMN_REASON
FROM 
v_Employee 
WHERE DATEDIFF(d,GETDATE(),TERMN_DATE)>=0 AND DATEDIFF(d,GETDATE(),TERMN_DATE)<=3
ORDER BY TERMN_DATE

7. Employee(s) require appraisal within these 2 weeks 

SELECT
EE_NO,
FULLNAME=DBO.EENAME(EE_NO),
DEPT_CODE,
NEXT_APPRAISAL_DATE
FROM 
v_Employee 
WHERE dbo.IsActiveEmployeeOn(EE_NO,GETDATE())=1 AND
DATEDIFF(d,GETDATE(),NEXT_APPRAISAL_DATE)>=0 AND DATEDIFF(d,GETDATE(),NEXT_APPRAISAL_DATE)<=14
ORDER BY NEXT_APPRAISAL_DATE

8. On Birthday Employee(s) within 2 days 

SELECT
EE_NO,
FULLNAME=DBO.EENAME(EE_NO),
DEPT_CODE,
BIRTHDATE 
FROM v_Employee 
WHERE dbo.IsActiveEmployeeOn(EE_NO,GETDATE())=1 AND
DATEDIFF(d,GETDATE(),BIRTHDATE)>=0 AND DATEDIFF(d,GETDATE(),BIRTHDATE)<=2
ORDER BY BIRTHDATE

9. Leave Notification for the coming 6 days

SELECT
[DATE]= (CASE WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=0 THEN 'Today'
WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=1 THEN 'Tomorrow'
WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=2 THEN '2 days later'
WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=3 THEN '3 days later'
WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=4 THEN '4 days later'
WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=5 THEN '5 days later'
WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=6 THEN '6 days later' END), [LEAVE_DATE]=D.[HR_Date],
[Day of Week]=ISNULL((SELECT Holiday_Desc FROM plHoliday WHERE [PUBLIC]=1 AND REGION='HKG' AND HOLIDAY_DATE=D.[HR_Date]),datename(weekday,D.[HR_Date])),
[Annual Leave]=dbo.ALNamesAt(D.[HR_Date]),
[Sick Leave]=dbo.SLNamesAt(D.[HR_Date]),
[Other Leave]=dbo.OLNamesAt(D.[HR_Date])
FROM [plDate] D
WHERE DATEDIFF(d,GETDATE(), D.[HR_Date])>=0
AND DATEDIFF(d,GETDATE(),D.[HR_Date])<=6

10. Payroll Change Log (Yesterday)
SELECT L.[UserID]
      ,[EventDateTime]=convert(nvarchar(12),L.EventDateTime,111) + ' ' + convert(nvarchar(12),L.EventDateTime, 114)
      ,L.[BATCH_NO]
      ,L.[EE_NO]
      ,[EE_NAME]=dbo.EENAME(L.[EE_NO])
      ,L.[Field]
      ,L.[OriginalValues]
      ,L.[NewValues]
  FROM [plPayrollChangeLog] L
  WHERE DATEDIFF(d,GETDATE(),EventDateTime)=-1

11. Employee Change Log (Yesterday)
SELECT L.[UserID]
      ,L.[EE_NO]
      ,[EventDateTime]=convert(nvarchar(12),L.EventDateTime,111) + ' ' + convert(nvarchar(12),L.EventDateTime, 114)
      ,[EE_NAME]=dbo.EENAME(L.[EE_NO])
      ,L.[Field]
      ,L.[OriginalValues]
      ,L.[NewValues]
  FROM [plEEChangeLog] L
  WHERE DATEDIFF(d,GETDATE(),EventDateTime)=-1

12. Contract End Employee(s)  within these 2 months

SELECT
[EE_NO],

[EE Name]=DBO.EENAME(E.EE_NO),
DEPT_CODE,
EE_CLASS,

[CONTRACT_END_DATE]=dbo.fnFormatDate(E.CONTRACT_END_DATE)
FROM 
v_Employee E
WHERE
DATEDIFF(m, GETDATE(), E.CONTRACT_END_DATE) >= 0 AND DATEDIFF(m, GETDATE(), E.CONTRACT_END_DATE) <= 1
AND dbo.IsActiveEmployeeOn(E.EE_NO,dbo.Today())=1 ORDER BY E.CONTRACT_END_DATE

13. Leave Applications Pending for Approval (email to approvers, for Approval Workflow = By level)

SELECT 
EMAIL=E.EMAIL,
EE_NAME=A.EE_NAME,
[LEAVE_DATE]=[LEAVE_FR_TO],
[DAYS_HOURS_AMOUNT]=[DAYS_HOURS],
[LEAVE_TYPE]=A.LEAVE_CODE_DESCRIPTION,
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
[STATUS]=A.APPROVED_STS
from [dbo].[v_plLeaveApply] A
INNER JOIN 
v_Employee E ON E.EE_NO=A.NEXT_APPROVER
WHERE dbo.IsPending(A.APPROVED_STS)=1 ORDER BY E.EMAIL, A.LEAVE_FR

14. Pending Applications for Approval of Today (email to approvers, for Approval Workflow = By level)

SELECT EMAIL, [Application Date], [Applicant], [Leave from], [To], [Leave Days], [Leave Type],[Apply for]
FROM 
(
select E.EMAIL
        ,[Application Date]=A.CREATE_DATE
        ,[Applicant]=A.EE_NAME
        ,[Leave from]=A.LEAVE_FR
        ,[To]=A.LEAVE_TO
        ,[Leave Days]=A.[DAYS]
        ,[Leave Type]=A.LEAVE_CODE
        ,[Apply for]='Apply'
  FROM [v_plLeaveApply] A
  INNER JOIN plLeaveApply L On A.RowSerial=L.RowSerial
  INNER JOIN v_Employee E ON E.EE_NO=A.NEXT_APPROVER
  WHERE dbo.IsPending(A.APPROVED_STS)=1    
  AND DATEDIFF(d,A.CREATE_DATE,dbo.Today())=0
  AND L.CREATE_DATE=L.LAST_UPDATE_DATE
UNION ALL
select E.EMAIL
        ,[Application Date]=L.LAST_UPDATE_DATE
        ,[Applicant]=A.EE_NAME
        ,[Leave from]=A.LEAVE_FR
        ,[To]=A.LEAVE_TO
        ,[Leave Days]=A.[DAYS]
        ,[Leave Type]=A.LEAVE_CODE
        ,[Apply for]='Edit'
  FROM [v_plLeaveApply] A
  INNER JOIN plLeaveApply L On A.RowSerial=L.RowSerial
  INNER JOIN v_Employee E ON E.EE_NO=A.NEXT_APPROVER
  WHERE dbo.IsPending(A.APPROVED_STS)=1    
  AND DATEDIFF(d,L.LAST_UPDATE_DATE,dbo.Today())=0
  AND L.CREATE_DATE<>L.LAST_UPDATE_DATE
UNION ALL
SELECT E.EMAIL
      ,[Application Date]=C.CREATE_DATE
      ,[Applicant]=dbo.EENAME(C.[EE_NO])
      ,[Leave from]=A.LEAVE_FR
      ,[To]=A.LEAVE_TO
      ,[Leave Days]=A.[DAYS]
      ,[Leave Type]=A.LEAVE_CODE
      ,[Apply for]='Cancel'
  FROM [dbo].[plLeaveApplyCancel] C
  INNER JOIN [v_plCancelRequestApply] V ON V.RowSerial=C.RowSerial
  INNER JOIN [v_plLeaveApply] A ON A.RowSerial=C.plLeaveApply_RowSerial
  INNER JOIN v_Employee E ON E.EE_NO=V.NEXT_APPROVER
  WHERE dbo.IsPending(A.CANCEL_STS)=1
  AND DATEDIFF(d,C.CREATE_DATE,dbo.Today())=0
) S
ORDER BY S.EMAIL, [Application Date]

15. Employee Cost Centres for all Employees 

SELECT [EE_NO]
      ,[START_DATE]
      ,[COST_CENTRE]
      ,[PERCENTAGE]
  FROM [dbo].[plEECostCentre]
  ORDER BY [EE_NO],[START_DATE]


16. Employee Missed Swipe Report  (email to employee if there is missed swipe attendance record yesterday)

SELECT E.EMAIL
      ,[Employee]=dbo.EEName(A.[EE_NO])
      ,[Date]=A.WORK_DATE
      ,[Work In]=(CASE WHEN A.[IN_TIME1]='' THEN 'Missing' ELSE A.[IN_TIME1] END)
      ,[Work Out]=(CASE WHEN A.[OUT_TIME2]='' THEN 'Missing' ELSE A.[OUT_TIME2] END)
  FROM plATTD A
  INNER JOIN v_Employee E ON E.EE_NO=A.EE_NO
  WHERE DATEDIFF(d,A.WORK_DATE,GETDATE())=1 AND dbo.IsMissedSwipe(A.[WORK_DATE],A.EE_NO,A.IN_TIME1,    A.OUT_TIME1, A.IN_TIME2, A.OUT_TIME2, A.IN_TIME3, A.OUT_TIME3)=1
  ORDER BY E.EMAIL

17. Continuous Contract of Employment (418) Checking

-- %NoFooter%
SELECT [EE No.vr_PLRCT.EE_NO]
      ,[Badge No.vr_PLRCT.BADGE_NO]
      ,[Employee Name.vr_PLRCT.EE_NAME]
      ,[Department.vr_PLRCT.DEPT_NAME]
      ,[Division.vr_PLRCT.DIVISION_NAME]
      ,[Employer Code.vr_PLRCT.EMPLOYER_CODE]
      ,[Work Hrs Type.vr_PLRCT.WORK_HRS_TYPE]
      ,[Roster Code.vr_PLRCT.ROSTER_CODE]
      ,[Join Date.vr_PLRCT.JOIN_DATE]
      ,[Weeks of Service.vr_PLRCT.WEEK_OF_SERVICE.N0]
      ,[Week 1.vr_PLRCT.WEEK1.NS]
      ,[Week 1 Working Hours.vr_PLRCT.WEEK1.NS]
      ,[Week 2.vr_PLRCT.WEEK2.NS]
      ,[Week 2 Working Hours.vr_PLRCT.WEEK2.NS]
      ,[Week 3.vr_PLRCT.WEEK3.NS]
      ,[Week 3 Working Hours.vr_PLRCT.WEEK3.NS]
      ,[Week 4.vr_PLRCT.WEEK4.NS]
      ,[Week 4 Working Hours.vr_PLRCT.WEEK4.NS]
      ,[Fulfils 418.vr_PLRCT.Is418]
  FROM [dbo].[vr_rptPLRCT]

18. Appraisals waiting for review (email to reviewers)

SELECT  [REVIEWER_EMAIL]=dbo.EEEmail([REVIEWER_NO]),
[Appraisal No.]=[APPRAISAL_NO],
[Employee]=dbo.EENameNo(EE_NO),
[Release Date]=[RELEASED_DATE],
[Deadline]=[APPRAISAL_DEADLINE],
[Status]=dbo.AppraisalStatus([APPRAISAL_NO])
FROM paAppraisalMaster
WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='IN_REVIEW' 
ORDER BY dbo.EEEmail([REVIEWER_NO]),[APPRAISAL_NO]

19. Appraisals awaiting Employee Feedback (email to employees)

SELECT  [EMAIL]=dbo.EEEmail([EE_NO]),
[Appraisal No.]=[APPRAISAL_NO],
[Employee]=dbo.EENameNo([EE_NO]),
[Release Date]=[RELEASED_DATE],
[Deadline]=[APPRAISAL_DEADLINE],
[Status]=dbo.AppraisalStatus([APPRAISAL_NO])
FROM paAppraisalMaster
WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='AWAITING_EMPLOYEE_FEEDBACK' 
ORDER BY dbo.EEEmail([EE_NO]),[APPRAISAL_NO]

20. Appraisals awaiting Feedbacker Feedback (email to Feedbackers)

SELECT  [EMAIL]=dbo.EEEmail([FEEDBACKER_NO]),
[Appraisal No.]=[APPRAISAL_NO],
[Employee]=dbo.EENameNo([EE_NO]),
[Release Date]=[RELEASED_DATE],
[Deadline]=[APPRAISAL_DEADLINE],
[Status]=dbo.AppraisalStatus([APPRAISAL_NO])
FROM paAppraisalMaster
WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='AWAITING_FEEDBACKER_FEEDBACK' 
ORDER BY dbo.EEEmail([FEEDBACKER_NO]),[APPRAISAL_NO]

21. Appraisals waiting for final review (email to reviewers)

SELECT  [EMAIL]=dbo.EEEmail([REVIEWER_NO]),
[Appraisal No.]=[APPRAISAL_NO],
[Employee]=dbo.EENameNo([EE_NO]),
[Release Date]=[RELEASED_DATE],
[Deadline]=[APPRAISAL_DEADLINE],
[Status]=dbo.AppraisalStatus([APPRAISAL_NO])
FROM paAppraisalMaster
WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='IN_FINAL_REVIEW' 
ORDER BY dbo.EEEmail([REVIEWER_NO]),[APPRAISAL_NO]

22. Appraisals awaiting HR Comment (email to HR)

SELECT  [EMAIL]=dbo.HREmail(),
[Appraisal No.]=[APPRAISAL_NO],
[Employee]=dbo.EENameNo([EE_NO]),
[Release Date]=[RELEASED_DATE],
[Deadline]=[APPRAISAL_DEADLINE],
[Status]=dbo.AppraisalStatus([APPRAISAL_NO])
FROM paAppraisalMaster M
WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='IN_HR_COMMENT' 
ORDER BY dbo.HREmail(),[APPRAISAL_NO]

23. Compensation Leave Balance Expiry Reminder (email to employee for the com leave entitlements will expire within 7 days)

SELECT 
   E.EMAIL,
   [Employee]=dbo.EENameNo(E.EE_NO),
   [Entitled Date]=C.ADJ_DATE,
   [Entitled Hours]=C.[HOURS],
   [Com Leave Balance]=C.HOURS_BALANCE,
   [Expiry Date]=C.[EXPIRY_DATE]
FROM v_Employee E
CROSS APPLY dbo.ComLeaveBalanceAllocation(E.EE_NO,dbo.Today()) C
WHERE HOURS_BALANCE<>0
AND DATEDIFF(DAY, dbo.Today(), C.[EXPIRY_DATE]) >= 0
AND DATEDIFF(DAY, dbo.Today(), C.[EXPIRY_DATE]) <= 7
    

24. Leave Type Balance Report (example for Leave Type BL - Birthday Leave)

SELECT
[EE No.v_Employee.EE_NO]=EE_NO,
[Employee Name.v_Employee.EE_NAME]=EE_NAME,
[Department.v_Employee.DEPT_NAME]=DEPT_NAME,
[Division.v_Employee.DIVISION_NAME]=DIVISION_NAME,
[Leave Type]='Birthday Leave',
[Entitled]=dbo.LeaveTypeEntitlement('BL',EE_NO,dbo.Today()),
[Adjustment]=dbo.LeaveAdjustmentByLeaveType('BL',EE_NO,dbo.LeaveTypeStartDate('BL',EE_NO,dbo.Today()),dbo.LeaveTypeEndDate('BL',EE_NO,dbo.Today())),
[Taken]=dbo.LeaveTakenByLeaveType('BL',EE_NO,dbo.LeaveTypeStartDate('BL',EE_NO,dbo.Today()),dbo.Today()),
[Approved]=dbo.LeaveTakenByLeaveType('BL',EE_NO,DATEADD(DAY,1,dbo.Today()),dbo.LeaveTypeEndDate('BL',EE_NO,dbo.Today())),
[Pending]=dbo.LeaveTypePendingApproval(EE_NO, 'BL', 0, dbo.Today()),
[Balance]=dbo.LeaveTypeBalance('BL',EE_NO,dbo.Today())+dbo.LeaveTakenByLeaveType('BL',EE_NO,DATEADD(DAY,1,dbo.Today()),dbo.LeaveTypeEndDate('BL',EE_NO,dbo.Today())),
[Future Adjustment]=dbo.LeaveAdjustmentByLeaveType('BL',EE_NO,DATEADD(DAY,1,dbo.LeaveTypeEndDate('BL',EE_NO,dbo.Today())),dbo.FutureEndDate(EE_NO,'BL',dbo.Today())),
[Future Balance]=dbo.LeaveTypeBalance('BL',EE_NO,dbo.FutureEndDate(EE_NO,'BL',dbo.Today())),
[Future As Of]=dbo.FutureEndDate(EE_NO,'BL',dbo.Today())
FROM v_Employee E
WHERE E.AUTHORIZED=1
    


Business Alert Query Hints

You can put some Business Alert Query Hints into the query to slightly format the result (see example 17 as example)

-- %NoTotal%

-- %NoFooter%

-- %NoTable%

-- %NoEncode%

-- %FullHeader%


See also