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 | |||