Vista Customization- Set Payroll Employees Inactive

Scenario: A company has old employees marked as active and wishes to find employees who haven’t received a paycheck recently and mark those employees as inactive.

Warning: test these queries in a test database prior to using in your live database.

Possible Solution # 1

Run the query below in SQL Server to find a list of active employees and their last pay date. Then use the list to manually mark employees as inactive.

SELECT LASTCHECK.PaidDate, e.PRCo, e.Employee, e.FullName FROM PREHFullName e

OUTER APPLY

(

SELECT TOP 1 PaidDate

FROM PRSQ s

WHERE s.PRCo = e.PRCo and s.PRGroup = e.PRGroup and s.Employee = e.Employee

ORDER BY PaidDate DESC

) AS LASTCHECK

WHERE e.ActiveYN = 'Y'

Possible Solution # 2

Backup the PREH Table.

Modify the date in the last line and run the query below to automatically mark employees inactive if their last paid date was prior to the specified date.

UPDATE PREH

SET ActiveYN = 'N'

FROM PREH

LEFT OUTER JOIN

(

SELECT e.PRCo, e.PRGroup, e.Employee, LASTCHECK.PaidDate FROM PREHFullName e

OUTER APPLY

(

SELECT TOP 1 PaidDate

FROM PRSQ s

WHERE s.PRCo = e.PRCo and s.PRGroup = e.PRGroup and s.Employee = e.Employee

ORDER BY PaidDate DESC

) AS LASTCHECK

WHERE e.ActiveYN = 'Y'

) t1 on t1.PRCo = PREH.PRCo and t1.PRGroup = PREH.PRGroup and t1.Employee = PREH.Employee

WHERE t1.PaidDate < '2020-06-01'