View: dbo.XactPay_Payroll_Transmission_View
View definition
create view dbo.XactPay_Payroll_Transmission_View
as
select
xactpay_payroll_transmission_KEY = XPT.xactpay_payroll_transmission_KEY,
client_KEY = XPT.client_KEY,
number_of_employees = XPT.number_of_employees,
check_date = XPT.check_date,
gross_pay = XPT.gross_pay,
payroll_data_xml = XPT.payroll_data_xml,
period_begin_date = XPT.period_begin_date,
period_end_date = XPT.period_end_date,
-- This will give the latest status for the xactpay_payroll_transmission_KEY. This is used for filtering
thirdparty_worker_compensation_transmission_status_type_KEY = LatestTransaction.thirdparty_worker_compensation_transmission_status_type_KEY
from
dbo.XactPay_Payroll_Transmission XPT
left join
(
select LatestDate.xactpay_payroll_transmission_KEY, XPTR.thirdparty_worker_compensation_transmission_status_type_KEY
from
(
select xactpay_payroll_transmission_KEY, MAX(status_change_date) date from dbo.XactPay_Payroll_Transmission_Result group by xactpay_payroll_transmission_KEY
) LatestDate inner join dbo.XactPay_Payroll_Transmission_Result XPTR on XPTR.xactpay_payroll_transmission_KEY = LatestDate.xactpay_payroll_transmission_KEY and XPTR.status_change_date = LatestDate.date
) LatestTransaction
on LatestTransaction.xactpay_payroll_transmission_KEY = XPT.xactpay_payroll_transmission_KEY