223 lines
8.0 KiB
SQL
223 lines
8.0 KiB
SQL
CREATE procedure [wh].[MERGED_PROCEDURE_S169]
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
-- QUERY PATRIMONIO CASA E PATRIMONIO TERZI
|
|
select
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
PATRBF.INSTITUTENAME,
|
|
CTV,
|
|
M.PROFILO_ASS AS CODICEPROFILO,
|
|
CR.MAX_VAR AS VARMAX,
|
|
RISK_AGG.VAR_PERC_PTF AS VAR ,
|
|
CASE
|
|
WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS VARSTRING,
|
|
RISK_AGG.CREDITRISK RISKCLASS,
|
|
CASE
|
|
WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
WHEN RISK_AGG.creditrisk is null THEN 'n.a.'
|
|
WHEN RISK_AGG.creditrisk = 0.00 THEN 'n.a.'
|
|
ELSE NULL
|
|
END AS RISKCLASSSTRING,
|
|
ISNULL(RISK_AGG.COPERTURA,0.00) AS COVERAGE,
|
|
NULL AS COVERAGESTRING,
|
|
M.RISKCLASS AS RISKCLASSMAX,
|
|
round((SUM(CTV) OVER (PARTITION BY PATRBF.COD_FISCALE,PATRBF.RETE, PATRBF.INSTITUTENAME) /
|
|
-- 16.07.2011 COLAIANNI CONSOLI GESTIONE DIVISIONE PER ZERO
|
|
CASE
|
|
WHEN ( SUM(CTV) OVER (PARTITION BY PATRBF.RETE, PATRBF.COD_FISCALE) ) = 0 THEN 1
|
|
ELSE ( SUM(CTV) OVER (PARTITION BY PATRBF.RETE, PATRBF.COD_FISCALE) )
|
|
END) * 100, 2) AS PERCENTAGE,
|
|
ORDINE,
|
|
RISK_AGG.Ully_perc
|
|
into #appo
|
|
from
|
|
(
|
|
select
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
case
|
|
when PATRBF.RETE = 'F' then 'Patrimonio Fideuram'
|
|
else 'Patrimonio Sanpaolo Invest'
|
|
end AS INSTITUTENAME,
|
|
sum(PATRBF.CTV) as CTV,
|
|
1 as ORDINE,
|
|
RISK_AGG.Ully_perc
|
|
from
|
|
c6martperiodico.patrimonio_bf PATRBF,
|
|
C6MartPeriodico.RISCHIO_AGGREGATO RISK_AGG
|
|
where
|
|
1=1
|
|
and PATRBF.COD_FISCALE = RISK_AGG.COD_FISCALE
|
|
and PATRBF.rete= RISK_AGG.rete
|
|
and PATRBF.CTV > 0
|
|
and RISK_AGG.COD_AGGREG = 'COMPLESSIVO|BF'
|
|
group by
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
RISK_AGG.Ully_perc,
|
|
case
|
|
when PATRBF.RETE = 'F' then 'Patrimonio Fideuram'
|
|
else 'Patrimonio Sanpaolo Invest'
|
|
end
|
|
union
|
|
SELECT
|
|
TERZI.RETE,
|
|
TERZI.COD_FISCALE,
|
|
'Patrimonio altri Istituti' AS INSTITUTENAME,
|
|
SUM(ROUND(TERZI.CTV,2)) AS CTV,
|
|
--V
|
|
2 as ORDINE,
|
|
RISK_AGG.Ully_perc
|
|
from
|
|
c6martperiodico.PATRIMONIO_TERZI TERZI,
|
|
C6MartPeriodico.RISCHIO_AGGREGATO RISK_AGG
|
|
where
|
|
1=1
|
|
and TERZI.COD_FISCALE = RISK_AGG.COD_FISCALE
|
|
and TERZI.rete= RISK_AGG.rete
|
|
and TERZI.CTV > 0
|
|
and RISK_AGG.COD_AGGREG= 'COMPLESSIVO|TERZI'
|
|
group by
|
|
TERZI.RETE,
|
|
TERZI.COD_FISCALE,
|
|
RISK_AGG.Ully_perc
|
|
) PATRBF
|
|
LEFT OUTER JOIN c6martperiodico.MIFID AS M
|
|
ON PATRBF.COD_FISCALE = M.COD_FISCALE
|
|
AND PATRBF.RETE = M.RETE
|
|
LEFT OUTER JOIN c6martperiodico.CODIFICA_RISCHIO AS CR
|
|
ON M.PROFILO_ASS = CR.PROFILO
|
|
LEFT OUTER JOIN c6martperiodico.CODIFICA_CREDITRISK CK
|
|
ON M.riskclass = CK.PROFILO
|
|
LEFT OUTER JOIN c6martperiodico.RISCHIO_AGGREGATO RISK_AGG
|
|
ON PATRBF.COD_FISCALE = RISK_AGG.COD_FISCALE
|
|
AND PATRBF.RETE = RISK_AGG.RETE
|
|
AND (case
|
|
when RISK_AGG.COD_AGGREG = 'COMPLESSIVO|BF' and PATRBF.RETE = 'F' then 'Patrimonio Fideuram'
|
|
when RISK_AGG.COD_AGGREG = 'COMPLESSIVO|BF' and PATRBF.RETE = 'S' then 'Patrimonio Sanpaolo Invest'
|
|
else 'Patrimonio altri Istituti'
|
|
end) = PATRBF.INSTITUTENAME
|
|
where
|
|
(
|
|
RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF' or
|
|
RISK_AGG.COD_AGGREG= 'COMPLESSIVO|TERZI'
|
|
)
|
|
-- FINE QUERY PER PATRIMONIO CASA E PATRIMONIO TERZI
|
|
-- SEPARAZIONE DEL PATRIMONIO CASA E DEL PATRIMONIO TERZI
|
|
select * into #ordine1 from #appo where ordine = 1
|
|
select * into #ordine2 from #appo where ordine = 2
|
|
-- QUERY PATRIMONIO COMPLESSIVO
|
|
SELECT
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
'Patrimonio Complessivo' AS INSTITUTENAME,
|
|
SUM(CTV) AS CTV,
|
|
MAX(M.PROFILO_ASS) AS CODICEPROFILO,
|
|
MAX(CR.MAX_VAR) AS VARMAX,
|
|
MAX(RISK_AGG.VAR_PERC_PTF) AS VAR ,
|
|
MAX(CASE
|
|
WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
ELSE NULL
|
|
END) AS VARSTRING,
|
|
MAX(RISK_AGG.CREDITRISK) RISKCLASS,
|
|
MAX(case
|
|
WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
WHEN RISK_AGG.creditrisk is null THEN 'n.a.'
|
|
WHEN RISK_AGG.creditrisk = 0.00 THEN 'n.a.'
|
|
ELSE NULL
|
|
END) AS RISKCLASSSTRING,
|
|
MAX(ISNULL(RISK_AGG.COPERTURA,0.00)) AS COVERAGE,
|
|
MAX(CASE
|
|
WHEN ISNULL(RISK_AGG.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
ELSE NULL
|
|
END) AS COVERAGESTRING,
|
|
MAX(M.RISKCLASS) AS RISKCLASSMAX,
|
|
100 AS PERCENTAGE,
|
|
3 AS ORDINE,
|
|
RISK_AGG.Ully_perc
|
|
into #Ordine3
|
|
from
|
|
(
|
|
select
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
SUM(PATRBF.CTV) AS CTV
|
|
from c6martperiodico.patrimonio_bf PATRBF
|
|
where
|
|
1=1
|
|
and
|
|
PATRBF.CTV > 0
|
|
group by
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE
|
|
union
|
|
SELECT
|
|
TERZI.RETE,
|
|
TERZI.COD_FISCALE,
|
|
SUM(ROUND(TERZI.CTV,2)) AS CTV
|
|
FROM
|
|
c6martperiodico.PATRIMONIO_TERZI TERZI
|
|
where
|
|
1=1
|
|
and
|
|
TERZI.CTV > 0
|
|
group by
|
|
TERZI.RETE,
|
|
TERZI.COD_FISCALE
|
|
) PATRBF
|
|
left outer join c6martperiodico.MIFID AS M
|
|
on PATRBF.COD_FISCALE =M.COD_FISCALE
|
|
and PATRBF.RETE = M.RETE
|
|
left outer join c6martperiodico.CODIFICA_RISCHIO AS CR
|
|
on M.PROFILO_ASS = CR.PROFILO
|
|
left outer join c6martperiodico.CODIFICA_CREDITRISK CK
|
|
on M.riskclass = CK.PROFILO
|
|
left outer join c6martperiodico.RISCHIO_AGGREGATO RISK_AGG
|
|
on PATRBF.COD_FISCALE = RISK_AGG.COD_FISCALE
|
|
and PATRBF.RETE = RISK_AGG.RETE
|
|
where
|
|
RISK_AGG.COD_AGGREG = 'COMPLESSIVO'
|
|
group by
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
RISK_AGG.Ully_perc
|
|
-- FINE QUERY PATRIMONIO COMPLESSIVO
|
|
-- INSERIMENTO NELLA TABELLA MERGED DELLA UNION TRA PATRIMONIO CASA (#Ordine1), PATRIMONIO TERZI (#Ordine2), PATRIMONIO COMPLESSIVO (#Ordine3)
|
|
INSERT INTO WH.PL_D2_S169RischiomercatoRischiocredito
|
|
select 'C6MartPeriodico.PL_D2_S169RischiomercatoRischiocredito' as ProcedureName, #Ordine1.RETE as i_rete, #Ordine1.COD_FISCALE as i_codiceFiscale, #Ordine1.RETE, #Ordine1.COD_FISCALE, #Ordine1.INSTITUTENAME, #Ordine1.CTV, #Ordine1.CODICEPROFILO, #Ordine1.VARMAX, #Ordine1.VAR, #Ordine1.VARSTRING, #Ordine1.RISKCLASS, #Ordine1.RISKCLASSSTRING, #Ordine1.COVERAGE, convert(varchar(100), #Ordine1.COVERAGESTRING) as COVERAGESTRING, #Ordine1.RISKCLASSMAX, #Ordine1.PERCENTAGE, #Ordine1.ORDINE, #Ordine1.Ully_perc from #Ordine1 --64740
|
|
union
|
|
select 'C6MartPeriodico.PL_D2_S169RischiomercatoRischiocredito' as ProcedureName, #Ordine2.RETE as i_rete, #Ordine2.COD_FISCALE as i_codiceFiscale, #Ordine2.RETE, #Ordine2.COD_FISCALE, #Ordine2.INSTITUTENAME, #Ordine2.CTV, #Ordine2.CODICEPROFILO, #Ordine2.VARMAX, #Ordine2.VAR, #Ordine2.VARSTRING, #Ordine2.RISKCLASS, #Ordine2.RISKCLASSSTRING, #Ordine2.COVERAGE, convert(varchar(100), #Ordine2.COVERAGESTRING) as COVERAGESTRING, #Ordine2.RISKCLASSMAX, #Ordine2.PERCENTAGE, #Ordine2.ORDINE, #Ordine2.Ully_perc from #Ordine2 --7274
|
|
union
|
|
select 'C6MartPeriodico.PL_D2_S169RischiomercatoRischiocredito' as ProcedureName, #Ordine3.RETE as i_rete, #Ordine3.COD_FISCALE as i_codiceFiscale, #Ordine3.RETE, #Ordine3.COD_FISCALE, #Ordine3.INSTITUTENAME, #Ordine3.CTV, #Ordine3.CODICEPROFILO, #Ordine3.VARMAX, #Ordine3.VAR, #Ordine3.VARSTRING, #Ordine3.RISKCLASS, #Ordine3.RISKCLASSSTRING, #Ordine3.COVERAGE, convert(varchar(100), #Ordine3.COVERAGESTRING) as COVERAGESTRING, #Ordine3.RISKCLASSMAX, #Ordine3.PERCENTAGE, #Ordine3.ORDINE, #Ordine3.Ully_perc from #Ordine3 --64832
|
|
-- CON UNION CON LA LEFT JOIN TRA #Ordine3 e #Ordine1 PER PRENDERE TUTTI I CASI IN CUI NON C'E' LA RIGA DEL PATRIMONIO CASA. VIENE CREATA UNA RIGA FITTIZIA
|
|
union
|
|
select
|
|
'C6MartPeriodico.PL_D2_S169RischiomercatoRischiocredito' as ProcedureName, #Ordine3.RETE as i_rete, #Ordine3.COD_FISCALE as i_codiceFiscale,
|
|
#Ordine3.Rete,
|
|
#Ordine3.Cod_Fiscale,
|
|
case when #Ordine3.RETE= 'F' then 'Patrimonio Fideuram'
|
|
WHEN #Ordine3.RETE= 'S' then 'Patrimonio Sanpaolo Invest'
|
|
end as INSTITUTENAME,
|
|
0 as CTV,
|
|
#Ordine3.CODICEPROFILO,
|
|
#Ordine3.VARMAX,
|
|
0 AS VAR,
|
|
'n.c.' AS VARSTRING,
|
|
0 as RISKCLASS,
|
|
'n.c.' as RISKCLASSSTRING,
|
|
#Ordine3.COVERAGE,
|
|
NULL AS COVERAGESTRING,
|
|
#Ordine3.RISKCLASSMAX,
|
|
0.0 as PERCENTAGE,
|
|
1 as ordine,
|
|
0 as Ully_perc
|
|
from #Ordine3
|
|
left join #Ordine1
|
|
on #Ordine3.Rete = #Ordine1.Rete
|
|
and #Ordine3.Cod_Fiscale = #Ordine1.Cod_Fiscale
|
|
where #Ordine1.Cod_Fiscale is null
|
|
END |