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