User Guide‎ > ‎

Business Alert/User Query Samples

Business Alert is an add-on module to help you manage your business more easily and effectively. You can identify any business-critical issues early and resolve them and exercise better control over your business. 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 A.APPROVED_STS IN ('Pending','Partially Approved','Majority Approved') 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 A.APPROVED_STS IN ('Pending','Partially Approved','Majority Approved')    
  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 A.APPROVED_STS IN ('Pending','Partially Approved','Majority Approved')    
  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 C.[CANCEL_STS] IN ('Pending','Partially Approved','Majority Approved') 
  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]

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