View: dbo.Insurepay_Payroll_Transmission_View
View definition
create view dbo.Insurepay_Payroll_Transmission_View
as
select
insurepay_payroll_transmission_KEY = IPT.insurepay_payroll_transmission_KEY,
client_KEY = IPT.client_KEY,
number_of_employee = IPT.number_of_employee,
check_date = IPT.check_date,
gross_pay = IPT.gross_pay,
payroll_data_xml = IPT.payroll_data_xml,
period_begin_date = IPT.period_begin_date,
period_end_date = IPT.period_end_date,
-- This will give the latest status for the insurepay_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.Insurepay_Payroll_Transmission IPT
left join
(
select LatestDate.insurepay_payroll_transmission_KEY, IPTR.thirdparty_worker_compensation_transmission_status_type_KEY
from
(
select insurepay_payroll_transmission_KEY, MAX(status_change_date) date from dbo.Insurepay_Payroll_Transmission_Result group by insurepay_payroll_transmission_KEY
) LatestDate inner join dbo.Insurepay_Payroll_Transmission_Result IPTR on IPTR.insurepay_payroll_transmission_KEY = LatestDate.insurepay_payroll_transmission_KEY and IPTR.status_change_date = LatestDate.date
) LatestTransaction
on LatestTransaction.insurepay_payroll_transmission_KEY = IPT.insurepay_payroll_transmission_KEY