View: dbo.Insurepay_Setup_Transmission_View | |||
View definition | |||
create view dbo.Insurepay_Setup_Transmission_View as select ist.insurepay_setup_transmission_KEY , ist.client_KEY , ist.number_of_employee , ist.setup_data_xml -- This will give the latest status for the insurepay_payroll_transmission_KEY. This is used for filtering , thirdparty_worker_compensation_transmission_status_type_KEY = ( select result.thirdparty_worker_compensation_transmission_status_type_KEY from dbo.Insurepay_Setup_Transmission_Result as result inner join ( select md.insurepay_setup_transmission_KEY , md.target__date , MAX(istr.connect_event_id) as target__connect_event_id from ( select insurepay_setup_transmission_KEY, MAX(status_change_date) as target__date from dbo.Insurepay_Setup_Transmission_Result group by insurepay_setup_transmission_KEY ) as md inner join dbo.Insurepay_Setup_Transmission_Result as istr on istr.insurepay_setup_transmission_KEY = md.insurepay_setup_transmission_KEY and istr.status_change_date = md.target__date group by md.insurepay_setup_transmission_KEY , md.target__date ) as me on result.insurepay_setup_transmission_KEY = me.insurepay_setup_transmission_KEY and result.status_change_date = me.target__date and result.connect_event_id = me.target__connect_event_id where me.insurepay_setup_transmission_KEY = ist.insurepay_setup_transmission_KEY ) from dbo.Insurepay_Setup_Transmission as ist | |||