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%