SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_risks_situations_before_incomplete]
    @policy_ids_table type_table_id readonly,
    @risks_ids_table type_table_id readonly

as
    set nocount on;

    --declare @policies_table table (pols_policy_id int, pols_policy_situation_id int)

    --insert into @policies_table(	 pols_policy_id,	 pols_policy_situation_id)
    select					 pps.pols_policy_id, 
							 pols_policy_situation_id = max(pps.pols_policy_situation_id),
							 pol_last_policy_situation_id = max(pp.pol_last_policy_situation_id) 
    from pol_policies_situations pps
	   inner join @policy_ids_table pit on pit.id = pps.pols_policy_id
	   inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
    where pps.pols_incomplete = 0
    group by pps.pols_policy_id 

    
    select  rrs.riss_risk_id, 
		  riss_risk_situation_id = max(rrs.riss_risk_situation_id),
		  ris_last_risk_situation_id = max(rr.ris_last_risk_situation_id)
    from ris_risks_situations rrs
	   inner join @risks_ids_table rit on rit.id = rrs.riss_risk_id
	   inner join pol_policies_situations pps on pps.pols_policy_situation_id = rrs.riss_policy_situation_id 
	   inner join ris_risks rr on rr.ris_risk_id = rrs.riss_risk_id
    where pps.pols_incomplete = 0
    group by rrs.riss_risk_id
GO
