Untitled

avatar
unknown
mysql
4 years ago
9.4 kB
7
Indexable
SELECT *
FROM
(
SELECT
	CONCAT('F', pu.company_number) AS id,
	IF('EN' = 'EN',
	pu.company_name,
	IFNULL(nn.native_name, pu.company_name)) 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,
	IF('US' = '@NA'
	OR pu.iso_country IS NULL,
	NULL,
	IF(pu.iso_country = 'US',
	'Domestic',
	'Foreign')) AS domfor,
	opsa.pct_sector AS UKSECT,
	opca.pct_market AS UKMKT,
	NULL AS title,
	IF(pu.cfax = '',
	NULL,
	pu.cfax) AS cfax,
	pu.beta,
	pu.relstr,
	pu.aum * 1 AS AUM,
	pu.eqasset * 1 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,
	SUM(IF(hh.report_date = '20210630', hh.position, 0)) AS parent_pos,
	SUM(IF(hh.report_date = '20210630', hh.position, 0)) AS pos1,
	SUM(IF(hh.report_date = '20210331', hh.position, 0)) - SUM(IF(hh.report_date = '20200930', hh.position, 0)) AS poschg0,
	SUM(IF(hh.report_date = '20210630', hh.position, 0)) - SUM(IF(hh.report_date = '20201231', hh.position, 0)) AS poschg1,
	NULL AS WATCHLIST,
	hh.company_number IN('284159','9342070','10992703','12044185','12445676','13371444') AS ACTFLAG,
	SUM(IF(hh.report_date IN (20200930, 20201231), 0, 1)) AS include
FROM
	own_snapshot hh
JOIN own_portmas_ultimate pu ON
	hh.company_number = pu.company_number
	AND pu.portfolio_type = 'F'
INNER JOIN own_secmas s ON
	hh.cusip = s.cusip
LEFT JOIN own_portmas_native nn ON
	pu.company_number = nn.company_number
	AND nn.language_code = 'EN'
LEFT JOIN own_holdings_hist_prices hhp ON
	hh.cusip = hhp.CUSIP
	AND hh.report_date = hhp.Price_Date
LEFT JOIN 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_portmas_sector_analytics_fds opsa ON
	opsa.company_number = hh.company_number
	AND opsa.code = '3300'
	AND opsa.iso_country = 'GB'
	AND opsa.type = 'F'
LEFT JOIN own_portmas_country_analytics_uk_fds opca ON
	opca.company_number = hh.company_number
	AND opca.type = 'F'
LEFT JOIN 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'
	AND hh.report_date IN ('20200930', '20201231', '20210331', '20210630')
GROUP BY
	hh.company_number
HAVING
	include != 0
UNION ALL
SELECT
	CONCAT('S', pu.company_number) AS id,
	IF('EN' = 'EN',
	pu.company_name,
	IFNULL(nn.native_name, pu.company_name)) 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,
	IF('US' = '@NA'
		OR pu.iso_country IS NULL,
		NULL,
		IF(pu.iso_country = 'US',
		'Domestic',
		'Foreign')) AS domfor,
	opsa.pct_sector AS UKSECT,
	opca.pct_market AS UKMKT,
	IF(pu.title = '',
	NULL,
	pu.title) AS title,
	IF(pu.cfax = '',
	NULL,
	pu.cfax) AS cfax,
	pu.beta,
	pu.relstr,
	pu.aum * 1 AS AUM,
	pu.eqasset * 1 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,
	SUM(IF(hh.report_date = '20210630', hh.position, 0)) AS parent_pos,
	SUM(IF(hh.report_date = '20210630', hh.position, 0)) AS pos1,
	SUM(IF(hh.report_date = '20210331', hh.position, 0)) - SUM(IF(hh.report_date = '20200930', hh.position, 0)) AS poschg0,
	SUM(IF(hh.report_date = '20210630', hh.position, 0)) - SUM(IF(hh.report_date = '20201231', hh.position, 0)) AS poschg1,
	NULL AS WATCHLIST,
	NULL AS ACTFLAG,
	SUM(IF(hh.report_date IN (20200930, 20201231), 0, 1)) AS include
FROM
	own_stakes_snapshot hh
JOIN own_portmas_ultimate pu ON
	hh.company_number = pu.company_number
	AND pu.portfolio_type = 'S'
INNER JOIN own_secmas s ON
	hh.cusip = s.cusip
LEFT JOIN own_portmas_native nn ON
	pu.company_number = nn.company_number
	AND nn.language_code = 'EN'
LEFT JOIN own_holdings_hist_prices hhp ON
	hh.cusip = hhp.CUSIP
	AND hh.report_date = hhp.Price_Date
LEFT JOIN 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_portmas_sector_analytics_fds opsa ON
	opsa.company_number = hh.company_number
	AND opsa.code = '3300'
	AND opsa.iso_country = 'GB'
	AND opsa.type = 'S'
LEFT JOIN own_portmas_country_analytics_uk_fds opca ON
	opca.company_number = hh.company_number
	AND opca.type = 'S'
LEFT JOIN 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'
	AND hh.report_date IN ('20200930', '20201231', '20210331', '20210630')
GROUP BY
	hh.company_number
HAVING
	include != 0
ORDER BY
	parent_pos DESC,
	NAME ) fs
UNION ALL
SELECT
	CONCAT(' M', pu.company_number) AS id,
	CONCAT(' ', IF('EN' = 'EN', pu.company_name, IFNULL(nn.native_name, pu.company_name))) AS NAME,
	pu.company_name AS SORT_NAME,
	inst.inner_sort_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,
	IF('US' = '@NA'
		OR pu.iso_country IS NULL,
		NULL,
		IF(pu.iso_country = 'US',
		'Domestic',
		'Foreign')) AS domfor,
	opsa.pct_sector AS UKSECT,
	opca.pct_market AS UKMKT,
	NULL AS title,
	IF(pu.cfax = '',
	NULL,
	pu.cfax) AS cfax,
	pu.beta,
	pu.relstr,
	pu.aum * 1 AS AUM,
	pu.eqasset * 1 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,
	SUM(IF(hh.report_date = '20210630', hh.position, 0)) AS pos1,
	SUM(IF(hh.report_date = '20210331', hh.position, 0)) - SUM(IF(hh.report_date = '20200930', hh.position, 0)) AS poschg0,
	SUM(IF(hh.report_date = '20210630', hh.position, 0)) - SUM(IF(hh.report_date = '20201231', hh.position, 0)) AS poschg1,
	NULL AS WATCHLIST,
	NULL AS ACTFLAG,
	SUM(IF(hh.report_date IN (20200930, 20201231), 0, 1)) AS include
FROM
	own_fund_hist_unadjusted_snapshot hh
JOIN own_portmas_ultimate pu ON
	hh.company_number = pu.company_number
	AND pu.portfolio_type = 'M'
INNER JOIN own_secmas s ON
	hh.cusip = s.cusip
LEFT JOIN own_portmas_sector_analytics_fds opsa ON
	opsa.company_number = hh.company_number
	AND opsa.code = '3300'
	AND opsa.iso_country = 'GB'
	AND opsa.type = 'M'
LEFT JOIN own_portmas_country_analytics_uk_fds opca ON
	opca.company_number = hh.company_number
	AND opca.type = 'M'
LEFT JOIN own_portmas_native nn ON
	pu.company_number = nn.company_number
	AND nn.language_code = 'EN'
JOIN (
	SELECT
		CONCAT('F', pu.company_number) AS id,
		SUM(IF(hh.report_date = '20210630', hh.position, 0)) 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_snapshot hh
	JOIN own_portmas_ultimate pu ON
		hh.company_number = pu.company_number
		AND pu.portfolio_type = 'F'
	INNER JOIN own_secmas s ON
		hh.cusip = s.cusip
	LEFT JOIN own_holdings_hist_prices hhp ON
		hh.cusip = hhp.CUSIP
		AND hh.report_date = hhp.Price_Date
	WHERE
		hh.cusip = '594918104'
		AND hh.report_date IN ('20200930', '20201231', '20210331', '20210630')
	GROUP BY
		hh.company_number
UNION ALL
	SELECT
		CONCAT('S', pu.company_number) AS id,
		SUM(IF(hh.report_date = '20210630', hh.position, 0)) 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_stakes_snapshot hh
	JOIN own_portmas_ultimate pu ON
		hh.company_number = pu.company_number
		AND pu.portfolio_type = 'S'
	INNER JOIN own_secmas s ON
		hh.cusip = s.cusip
	LEFT JOIN own_holdings_hist_prices hhp ON
		hh.cusip = hhp.CUSIP
		AND hh.report_date = hhp.Price_Date
	WHERE
		hh.cusip = '594918104'
		AND hh.report_date IN ('20200930', '20201231', '20210331', '20210630')
	GROUP BY
		hh.company_number
	ORDER BY
		parent_pos DESC,
		NAME ) inst ON
	pu.inst_id = inst.inst_id
LEFT JOIN own_holdings_hist_prices hhp ON
	hh.cusip = hhp.CUSIP
	AND hh.report_date = hhp.Price_Date
LEFT JOIN 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'
	AND hh.report_date IN ('20200930', '20201231', '20210331', '20210630')
GROUP BY
	hh.company_number
HAVING
	include != 0
ORDER BY
	parent_pos DESC,
	sort_parent_name,
	inst_id,
	LEVEL,
	pos1 DESC,
	SORT_NAME
Editor is loading...