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