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, click the v button to view the 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
3. On 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-DATEPART(WEEKDAY,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
25. Applications Pending for Approval (email to applicant)
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.EE_NO
WHERE dbo.IsPending(A.[STATUS])=1
ORDER BY E.EMAIL, A.CREATE_DATE
26. Applications Pending for Approval (email to approvers, for Approval Workflow = Any)
SELECT
EMAIL,
EE_NAME,
[APPLICATION_TYPE],
[LEAVE_TYPE],
[APPLICATION_DESCRIPTION],
[DAYS/HOURS/AMOUNT],
[APPLY_DATE],
[STATUS]
FROM
(
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO1]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=0 -- Any Approver
UNION
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO2]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=0 -- Any Approver
UNION
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO3]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=0 -- Any Approver
UNION
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO4]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=0 -- Any Approver
UNION
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO5]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=0 -- Any Approver
) S
ORDER BY EMAIL, [APPLY_DATE]
27. Applications Pending for Approval (email to approvers, for Approval Workflow = All)
SELECT
EMAIL,
EE_NAME,
[APPLICATION_TYPE],
[LEAVE_TYPE],
[APPLICATION_DESCRIPTION],
[DAYS/HOURS/AMOUNT],
[APPLY_DATE],
[STATUS]
FROM
(
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO1]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=1 -- All Approver
AND A.APPROVER_NO1 <> A.PARTIALLY_APPROVED_BY
AND A.APPROVER_NO1 <> A.MAJORITY_APPROVED_BY
AND A.APPROVER_NO1 <> A.LARGE_MAJORITY_APPROVED_BY
AND A.APPROVER_NO1 <> A.VAST_MAJORITY_APPROVED_BY
UNION ALL
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO2]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=1 -- All Approver
AND A.APPROVER_NO2 <> A.PARTIALLY_APPROVED_BY
AND A.APPROVER_NO2 <> A.MAJORITY_APPROVED_BY
AND A.APPROVER_NO2 <> A.LARGE_MAJORITY_APPROVED_BY
AND A.APPROVER_NO2 <> A.VAST_MAJORITY_APPROVED_BY
UNION ALL
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO3]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=1 -- All Approver
AND A.APPROVER_NO3 <> A.PARTIALLY_APPROVED_BY
AND A.APPROVER_NO3 <> A.MAJORITY_APPROVED_BY
AND A.APPROVER_NO3 <> A.LARGE_MAJORITY_APPROVED_BY
AND A.APPROVER_NO3 <> A.VAST_MAJORITY_APPROVED_BY
UNION ALL
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO4]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=1 -- All Approver
AND A.APPROVER_NO4 <> A.PARTIALLY_APPROVED_BY
AND A.APPROVER_NO4 <> A.MAJORITY_APPROVED_BY
AND A.APPROVER_NO4 <> A.LARGE_MAJORITY_APPROVED_BY
AND A.APPROVER_NO4 <> A.VAST_MAJORITY_APPROVED_BY
UNION ALL
SELECT
EMAIL=E.EMAIL,
EE_NAME=A.[NAME],
[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',
[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),
[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],
[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],
[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),
A.[STATUS]
FROM v_WebLeaveApply A
INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO5]
WHERE dbo.IsPending(A.[STATUS])=1
AND ApprovalWorkFlow=1 -- All Approver
AND A.APPROVER_NO5 <> A.PARTIALLY_APPROVED_BY
AND A.APPROVER_NO5 <> A.MAJORITY_APPROVED_BY
AND A.APPROVER_NO5 <> A.LARGE_MAJORITY_APPROVED_BY
AND A.APPROVER_NO5 <> A.VAST_MAJORITY_APPROVED_BY
) S
ORDER BY EMAIL, [APPLY_DATE]
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%