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 PLEMPLOYEE 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 plEmployee 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 plEMPLOYEE
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 plEMPLOYEE
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 plEMPLOYEE
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 E.[EE_NO],
DBO.EENAME(E.EE_NO),
DEPT_CODE,E.EE_CLASS,
dbo.fnFormatDate(E.CONTRACT_END_DATE)
FROM plEMPLOYEE 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]=dbo.LeaveApplyDescBySerial(A.ROWSERIAL),
[LEAVE_TYPE]=A.LEAVE_CODE_DESCRIPTION,
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
[STATUS]=A.APPROVED_STS
from [dbo].[v_plLeaveApply] A
INNER JOIN plEmployee 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 PLEMPLOYEE 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 PLEMPLOYEE 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_plLeaveApplyCancel] V ON V.RowSerial=C.RowSerial
  INNER JOIN [v_plLeaveApply] A ON A.RowSerial=C.plLeaveApply_RowSerial
  INNER JOIN PLEMPLOYEE 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]


See also