Untitled

mail@pastecode.io avatar
unknown
pgsql
2 years ago
19 kB
2
Indexable
Never
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