View: dbo.XactPay_Setup_Transmission_View | |||
View definition | |||
create view dbo.XactPay_Setup_Transmission_View as select xactpay_setup_transmission_KEY = XST.xactpay_setup_transmission_KEY, client_KEY = XST.client_KEY, number_of_employees = XST.number_of_employees, is_seasonal_business = XST.is_seasonal_business, seasonal_explanation = XST.seasonal_explanation, number_of_payrolls = XST.number_of_payrolls, setup_data_xml = XST.setup_data_xml, -- This will give the latest status for the xactpay_setup_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_Setup_Transmission XST left join ( select LatestDate.xactpay_setup_transmission_KEY, XSTR.thirdparty_worker_compensation_transmission_status_type_KEY from ( select xactpay_setup_transmission_KEY, MAX(status_change_date) date from dbo.XactPay_Setup_Transmission_Result group by xactpay_setup_transmission_KEY ) LatestDate inner join dbo.XactPay_Setup_Transmission_Result XSTR on XSTR.xactpay_setup_transmission_KEY = LatestDate.xactpay_setup_transmission_KEY and XSTR.status_change_date = LatestDate.date ) LatestTransaction on LatestTransaction.xactpay_setup_transmission_KEY = XST.xactpay_setup_transmission_KEY | |||