
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE view [dbo].[vw_tt_act_actors_addresse_corresp]
as

	/*
	DGE
		Cette vue renvoie pour un acteur:
		- l'id de l'adresse de correspondence, donc nm_types_addresses.nmtadr_corresp != 0;
		- si pas d'adresse de correspondence définie, on envoie l'adresse principale

	*/

	select	distinct adra_actor_id, 
			adra_address_id = isnull(t.min_address_id, act_address_id)
			--,t.min_address_id, act_address_id
	from	act_actors
			inner join act_actors_addresses on adra_actor_id = act_actor_id
			left join	(
						-- 1ere adresse de correspondance trouvée pour l'acteur
						select	actor_id = aa.adra_actor_id, 
								min_address_id = min(aa.adra_address_id)
						from	act_actors_addresses aa
								inner join adr_addresses a		on	a.adr_address_id = aa.adra_address_id
								inner join nm_types_addresses t on	t.nmtadr_type_code = a.adr_type_code 
																and t.nmtadr_corresp != 0
						group by aa.adra_actor_id 
						) as t on t.actor_id = adra_actor_id


GO
