@CutOffDate - Date value that is used to determine the attendance plans to include. Only attendance plans with an effective date before the @CutOffDate are included in the calculation
@StartDate - Date value that is used to determine when to start calculating the total. This will often be the effective date of a particular plan.
@EndDate - Date value that determines the date for when to calculate the total. Attendance accrued or used after this date will not be included.
@ConvertToDays - Integer that determines whether to return the total in Days or Hours. If this is set to 0, it will return Hours. Any other value will return Days.
@PassInEmployeeID - Specific Employee ID to base calculated attendance on. Default is 0, which is all employees.
Here is a sample SQL that returns Vacation Time Accrued and Vacation Time Used as of 03/24/2014 based on a start date of 01/01/2014.
select E.USERDEFINEDID, E.EMPLOYEENAME, VacationAccrued.VacationAccruedTotal, VacationUsed.VacationUsedTotal
from PY7EMPLOYEES E
--Calculate Vacation Accrued
PY7EMPLOYEESID, SUM(Accrued) VacationAccruedTotal
FROM DBO.tf_PY7CapGetHours('03/24/2014','01/01/2014','03/24/2014',0,0) as SA
WHERE SA.AttendanceTypeID = (select AttendanceType from PY7ATTENDANCECODES where DESCRIPTION = 'Vacation')
group by SA.PY7EmployeesID
) as VacationAccrued on E.PY7EMPLOYEESID = VacationAccrued.PY7EmployeesID
-- Calculate Vacation Used
SELECT PY7EMPLOYEESID, SUM(Used) VacationUsedTotal
FROM DBO.tf_PY7CapGetHours('03/24/2014','01/01/2014','03/24/2014',0,0) as SU
WHERE SU.AttendanceTypeID = (select AttendanceType from PY7ATTENDANCECODES where DESCRIPTION = 'Vacation')
group by SU.PY7EmployeesID
) as VacationUsed on E.PY7EMPLOYEESID = VacationUsed.PY7EmployeesID
Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.