,convert(nvarchar,prod.[Campaign ID])
+'_'+FORMAT(cp.[Start_date],'yyyymmdd')
+'_'+FORMAT(cp.[End_date],'yyyymmdd') as 'Nom Segment'
FROM [RelevanC].[dbo].[Target] tar with(nolock)
inner join (select Coupon_ID,[Campaign ID],Tag_description from [RelevanC].[dbo].[Product_offers] with(nolock))
prod on tar.Coupon_ID=prod.Coupon_ID
inner join (select [Brand],url,[Coupon ID] FROM [RelevanC].[dbo].[Product_List] with(nolock)) l on l.[Coupon ID]=tar.Coupon_ID
inner join ( SELECT [FID_Numero_Carte_Fid],[GuidCRM] FROM [BD_GEMO_CRM].[dbo].[View_HLI_Contact] with(nolock)) c
on c.FID_Numero_Carte_Fid=tar.Customer
inner join ( select [Campaign ID],[Title],[Start_date],[End_date] from Campaign with(nolock) ) cp on cp.[Campaign ID] = prod.[Campaign ID]
where (tar.[fg_contact_traite] is null or tar.[fg_contact_traite] !=1) and cp.[Campaign ID] <>34567752 --and c.FID_Numero_Carte_Fid = '2110003642065'
group by prod.[Campaign ID],c.GuidCRM,cp.[Title],cp.[Start_date],cp.[End_date],c.FID_Numero_Carte_Fid
