SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vw_polris_variables_ann]
AS
	/*
	sélectionner les totaux des variables contrat et risque, par situation contrat
	*/

	select	policy_situation_id, variable_id, tf = sum(tf), taxes = sum(taxes), ti = sum(ti)
	from	(
			select	policy_situation_id = polv_policy_situation_id, variable_id = polv_variable_id, tf = polv_tf, taxes = polv_taxes, ti = polv_ti
			from	pol_variables

			union 

			select	policy_situation_id = rs.riss_policy_situation_id, variable_id = risv_variable_id, rv = risv_tf, taxes = risv_taxes, ti = risv_ti
			from	ris_variables rv 
					inner join ris_risks_situations rs on rs.riss_risk_situation_id = rv.risv_risk_situation_id
			) as a
	group by policy_situation_id, variable_id
GO
