Untitled
unknown
pgsql
3 years ago
19 kB
8
Indexable
SELECT
id,
name,
SORT_NAME,
sort_parent_name,
inst_id,
parent_name,
LEVEL,
instinsid,
ultparentid,
ultparentname,
htype,
investor,
MFADVISOR,
style,
mstyle,
capgrp,
turn,
metro,
region,
subregion,
cntry,
state,
city,
domfor,
UKSECT,
UKMKT,
title,
cfax,
beta,
relstr,
AUM,
EQASSET,
filername,
mfcontact,
cphone,
cadd,
cemail,
ctitle,
actdesc,
actscore,
sw,
pb,
divyld,
pe,
WATCHLIST,
ACTFLAG,
SUM(parent_pos) as parent_pos,
SUM(POSCHG0) AS POSCHG0,
SUM(POSCHG1) AS POSCHG1,
SUM(pos1) AS pos1,
SUM(include) as include
from
(
SELECT
CONCAT('F', pu.company_number) AS id,
(
CASE
WHEN 'EN'
/*language*/
= 'EN' THEN pu.company_name
ELSE COALESCE(nn.native_name, pu.company_name)
END
) AS NAME,
pu.company_name AS SORT_NAME,
pu.company_name AS sort_parent_name,
pu.inst_id,
pu.company_name AS parent_name,
0 AS LEVEL,
'Institution' AS instinsid,
pu.ultparentid,
pu.ultparentname,
pu.htype,
pu.investor,
pu.Institution_Name AS MFADVISOR,
pu.style,
pu.mstyle,
pu.capgrp,
pu.turn,
pu.metro,
pu.region,
pu.subregion,
pu.cntry,
pu.state,
pu.city,
(
CASE
WHEN 'US'
/*country*/
= NULL
OR pu.iso_country IS NULL THEN NULL
ELSE (
CASE
WHEN pu.iso_country = 'US'
/*country*/
THEN 'Domestic'
ELSE 'Foreign'
END
)
END
) AS domfor,
opsa.pct_sector AS UKSECT,
opca.pct_market AS UKMKT,
NULL AS title,
(
CASE
WHEN pu.cfax = '' THEN NULL
ELSE pu.cfax
END
) AS cfax,
pu.beta,
pu.relstr,
pu.aum * '1'
/*curr_xrate*/
AS AUM,
pu.eqasset * '1'
/*curr_xrate*/
AS EQASSET,
pu.filername,
pu.mfcontact,
pu.cphone,
pu.cadd,
pu.cemail,
pu.ctitle,
pu.actdesc,
pu.actscore,
pu.sw,
pu.pb,
pu.divyld,
pu.pe,
CASE
WHEN hh.report_date = '20210630' THEN hh.position
ELSE 0
END AS parent_pos,
CASE
WHEN hh.report_date = '20210630' THEN hh.position
ELSE 0
END AS pos1,
case
when hh.report_date = '20210331' then hh.position
else 0
end - case
when hh.report_date = '20200930' then hh.position
else 0
end as POSCHG0,
case
when hh.report_date = '20210630' then hh.position
else 0
end - case
when hh.report_date = '20201231' then hh.position
else 0
end as POSCHG1,
NULL
/*wl_str*/
AS WATCHLIST,
hh.company_number = ANY(
STRING_TO_ARRAY(
'284159,9342070,10992703,12044185,12445676,13371444'
/*activists*/
,
','
) :: int []
) AS ACTFLAG,
(
CASE
WHEN hh.report_date = ANY(
STRING_TO_ARRAY(
'20200930,20201231'
/*include_string*/
,
','
) :: date []
) THEN 0
ELSE 1
END
) AS include
FROM
own.own_snapshot hh
JOIN own.own_portmas_ultimate pu ON hh.company_number = pu.company_number
AND pu.portfolio_type = 'F'
INNER JOIN own.own_secmas s ON hh.cusip = s.cusip
LEFT JOIN own.own_portmas_native nn ON pu.company_number = nn.company_number
AND nn.language_code = 'EN'
/*language*/
LEFT JOIN own.own_holdings_hist_prices hhp ON hh.cusip = hhp.CUSIP
AND hh.report_date = hhp.Price_Date
LEFT JOIN own.own_portmas_optmv_snapshot optmv ON hh.company_number = optmv.company_number
AND hh.report_date = optmv.date
AND optmv.type = 'F'
LEFT JOIN own.own_portmas_sector_analytics_fds opsa ON opsa.company_number = hh.company_number
AND opsa.code = '3300'
/*sector_code*/
AND opsa.iso_country = 'GB'
AND opsa.type = 'F'
LEFT JOIN own.own_portmas_country_analytics_uk_fds opca ON opca.company_number = hh.company_number
AND opca.type = 'F'
LEFT JOIN own.own_stakes_snapshot sts ON hh.cusip = sts.cusip
AND hh.company_number = sts.company_number
AND hh.report_date = sts.report_date
WHERE
hh.cusip = '594918104'
/*cusip*/
AND hh.report_date IN (
'20200930',
'20201231',
'20210331',
'20210630'
)
UNION
ALL
SELECT
CONCAT('S', pu.company_number) AS id,
(
CASE
WHEN 'EN'
/*language*/
= 'EN' THEN pu.company_name
ELSE COALESCE(nn.native_name, pu.company_name)
END
) AS NAME,
pu.company_name AS SORT_NAME,
pu.company_name AS sort_parent_name,
pu.inst_id,
pu.company_name AS parent_name,
0 AS LEVEL,
'Insider' AS instinsid,
pu.ultparentid,
pu.ultparentname,
pu.htype,
pu.investor,
pu.Institution_Name AS MFADVISOR,
pu.style,
pu.mstyle,
pu.capgrp,
pu.turn,
pu.metro,
pu.region,
pu.subregion,
pu.cntry,
pu.state,
pu.city,
(
CASE
WHEN 'US' = NULL
OR pu.iso_country IS NULL THEN NULL
ELSE (
CASE
WHEN pu.iso_country = 'US' THEN 'Domestic'
ELSE 'Foreign'
END
)
END
) AS domfor,
opsa.pct_sector AS UKSECT,
opca.pct_market AS UKMKT,
NULL AS title,
(
CASE
WHEN pu.cfax = '' THEN NULL
ELSE pu.cfax
END
) AS cfax,
pu.beta,
pu.relstr,
pu.aum * '1'
/*curr_xrate*/
AS AUM,
pu.eqasset * '1'
/*curr_xrate*/
AS EQASSET,
pu.filername,
pu.mfcontact,
pu.cphone,
pu.cadd,
pu.cemail,
pu.ctitle,
pu.actdesc,
pu.actscore,
pu.sw,
pu.pb,
pu.divyld,
pu.pe,
CASE
WHEN hh.report_date = '20210630' THEN hh.position
ELSE 0
END AS parent_pos,
CASE
WHEN hh.report_date = '20210630' THEN hh.position
ELSE 0
END AS pos1,
case
when hh.report_date = '20210331' then hh.position
else 0
end - case
when hh.report_date = '20200930' then hh.position
else 0
end as POSCHG0,
case
when hh.report_date = '20210630' then hh.position
else 0
end - case
when hh.report_date = '20201231' then hh.position
else 0
end as POSCHG1,
NULL
/*wl_str*/
AS WATCHLIST,
NULL AS ACTFLAG,
(
CASE
WHEN hh.report_date = ANY(
STRING_TO_ARRAY(
'20200930,20201231'
/*include_string*/
,
','
) :: date []
) THEN 0
ELSE 1
END
) AS include
FROM
own.own_stakes_snapshot hh
JOIN own.own_portmas_ultimate pu ON hh.company_number = pu.company_number
AND pu.portfolio_type = 'S'
INNER JOIN own.own_secmas s ON hh.cusip = s.cusip
LEFT JOIN own.own_portmas_native nn ON pu.company_number = nn.company_number
AND nn.language_code = 'EN'
/*language*/
LEFT JOIN own.own_holdings_hist_prices hhp ON hh.cusip = hhp.CUSIP
AND hh.report_date = hhp.Price_Date
LEFT JOIN own.own_portmas_optmv_snapshot optmv ON hh.company_number = optmv.company_number
AND hh.report_date = optmv.date
AND optmv.type = 'S'
LEFT JOIN own.own_portmas_sector_analytics_fds opsa ON opsa.company_number = hh.company_number
AND opsa.code = '3300'
/*sector_code*/
AND opsa.iso_country = 'GB'
AND opsa.type = 'S'
LEFT JOIN own.own_portmas_country_analytics_uk_fds opca ON opca.company_number = hh.company_number
AND opca.type = 'S'
LEFT JOIN own.own_stakes_snapshot sts ON hh.cusip = sts.cusip
AND hh.company_number = sts.company_number
AND hh.report_date = sts.report_date
WHERE
hh.cusip = '594918104'
/*cusip*/
AND hh.report_date IN (
'20200930',
'20201231',
'20210331',
'20210630'
)
UNION
ALL
SELECT
CONCAT(' M', pu.company_number) AS id,
(
CASE
WHEN 'EN'
/*language*/
= 'EN' THEN pu.company_name
ELSE COALESCE(nn.native_name, pu.company_name)
END
) AS NAME,
inst.inner_sort_name AS SORT_NAME,
pu.company_name AS sort_parent_name,
pu.inst_id,
inst.name AS parent_name,
1 AS LEVEL,
'Mutual Fund' AS instinsid,
pu.ultparentid,
pu.ultparentname,
pu.htype,
pu.investor,
pu.Institution_Name AS MFADVISOR,
pu.style,
pu.mstyle,
pu.capgrp,
pu.turn,
pu.metro,
pu.region,
pu.subregion,
pu.cntry,
pu.state,
pu.city,
(
CASE
WHEN 'US' = NULL
OR pu.iso_country IS NULL THEN NULL
ELSE (
CASE
WHEN pu.iso_country = 'US' THEN 'Domestic'
ELSE 'Foreign'
END
)
END
) AS domfor,
opsa.pct_sector AS UKSECT,
opca.pct_market AS UKMKT,
NULL AS title,
(
CASE
WHEN pu.cfax = '' THEN NULL
ELSE pu.cfax
END
) AS cfax,
pu.beta,
pu.relstr,
pu.aum * '1'
/*curr_xrate*/
AS AUM,
pu.eqasset * '1'
/*curr_xrate*/
AS EQASSET,
pu.filername,
pu.mfcontact,
pu.cphone,
pu.cadd,
pu.cemail,
pu.ctitle,
pu.actdesc,
pu.actscore,
pu.sw,
pu.pb,
pu.divyld,
pu.pe,
inst.parent_pos,
CASE
WHEN hh.report_date = '20210630' THEN hh.position
ELSE 0
END AS pos1,
case
when hh.report_date = '20210331' then hh.position
else 0
end - case
when hh.report_date = '20200930' then hh.position
else 0
end as POSCHG0,
case
when hh.report_date = '20210630' then hh.position
else 0
end - case
when hh.report_date = '20201231' then hh.position
else 0
end as POSCHG1,
NULL
/*wl_str*/
AS WATCHLIST,
NULL AS ACTFLAG,
(
CASE
WHEN hh.report_date = ANY(
STRING_TO_ARRAY(
'20200930,20201231'
/*include_string*/
,
','
) :: date []
) THEN 0
ELSE 1
END
) AS include
FROM
own.own_fund_hist_unadjusted_snapshot hh
JOIN own.own_portmas_ultimate pu ON hh.company_number = pu.company_number
AND pu.portfolio_type = 'M'
INNER JOIN own.own_secmas s ON hh.cusip = s.cusip
LEFT JOIN own.own_portmas_sector_analytics_fds opsa ON opsa.company_number = hh.company_number
AND opsa.code = '3300'
/*sector_code*/
AND opsa.iso_country = 'GB'
AND opsa.type = 'M'
LEFT JOIN own.own_portmas_country_analytics_uk_fds opca ON opca.company_number = hh.company_number
AND opca.type = 'M'
LEFT JOIN own.own_portmas_native nn ON pu.company_number = nn.company_number
AND nn.language_code = 'EN'
/*language*/
JOIN (
SELECT
CONCAT('F', pu.company_number) AS id,
CASE
WHEN hh.report_date = '20210630' THEN hh.position
ELSE 0
END AS parent_pos,
pu.company_name AS NAME,
pu.company_name AS inner_sort_name,
pu.inst_id,
0 AS LEVEL,
pu.portfolio_type
FROM
own.own_snapshot hh
JOIN own.own_portmas_ultimate pu ON hh.company_number = pu.company_number
AND pu.portfolio_type = 'F'
INNER JOIN own.own_secmas s ON hh.cusip = s.cusip
LEFT JOIN own.own_holdings_hist_prices hhp ON hh.cusip = hhp.CUSIP
AND hh.report_date = hhp.Price_Date
WHERE
hh.cusip = '594918104'
/*cusip*/
AND hh.report_date IN (
'20200930',
'20201231',
'20210331',
'20210630'
)
UNION
ALL
SELECT
CONCAT('S', pu.company_number) AS id,
CASE
WHEN hh.report_date = '20210630' THEN hh.position
ELSE 0
END AS parent_pos,
pu.company_name AS NAME,
pu.company_name AS inner_sort_name,
pu.inst_id,
0 AS LEVEL,
pu.portfolio_type
FROM
own.own_stakes_snapshot hh
JOIN own.own_portmas_ultimate pu ON hh.company_number = pu.company_number
AND pu.portfolio_type = 'S'
INNER JOIN own.own_secmas s ON hh.cusip = s.cusip
LEFT JOIN own.own_holdings_hist_prices hhp ON hh.cusip = hhp.CUSIP
AND hh.report_date = hhp.Price_Date
WHERE
hh.cusip = '594918104'
/*cusip*/
AND hh.report_date IN (
'20200930',
'20201231',
'20210331',
'20210630'
)
) inst ON pu.inst_id = inst.inst_id
LEFT JOIN own.own_holdings_hist_prices hhp ON hh.cusip = hhp.CUSIP
AND hh.report_date = hhp.Price_Date
LEFT JOIN own.own_portmas_optmv_snapshot optmv ON hh.company_number = optmv.company_number
AND hh.report_date = optmv.date
AND optmv.type = 'M'
WHERE
hh.cusip = '594918104'
/*cusip*/
AND hh.report_date IN (
'20200930',
'20201231',
'20210331',
'20210630'
)
) as tmp
where
include != 0
GROUP BY
id,
name,
SORT_NAME,
sort_parent_name,
inst_id,
parent_name,
LEVEL,
instinsid,
ultparentid,
ultparentname,
htype,
investor,
MFADVISOR,
style,
mstyle,
capgrp,
turn,
metro,
region,
subregion,
cntry,
state,
city,
domfor,
UKSECT,
UKMKT,
title,
cfax,
beta,
relstr,
AUM,
EQASSET,
filername,
mfcontact,
cphone,
cadd,
cemail,
ctitle,
actdesc,
actscore,
sw,
pb,
divyld,
pe,
WATCHLIST,
ACTFLAG,
include
ORDER BY
parent_pos DESC,
sort_parent_name,
inst_id,
LEVEL,
pos1 DESC,
SORT_NAME
Editor is loading...