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 | |||