CREATE VIEW [C6MartPeriodico].[vPatrimonioBFAggregatoPerArea]
AS
SELECT     TOP (100) PERCENT RETE, COD_FISCALE, LEFT(ID_AREA, 3) AS ID_AREA,chiave_progetto as ORDINAMENTO_PROGETTO, SUM(CTV) AS CTV
FROM         C6MartPeriodico.PATRIMONIO_BF
WHERE LEFT(ID_AREA, 3) <> 'PRE'
GROUP BY RETE, COD_FISCALE, LEFT(ID_AREA, 3),chiave_progetto
union all
SELECT     TOP (100) PERCENT RETE, COD_FISCALE, LEFT(ID_AREA, 3) AS ID_AREA,max(chiave_progetto) as ORDINAMENTO_PROGETTO, SUM(CTV) AS CTV
FROM         C6MartPeriodico.PATRIMONIO_BF
where  LEFT(ID_AREA, 3) = 'PRE'
GROUP BY RETE, COD_FISCALE, LEFT(ID_AREA, 3)