Please excuse our appearance as we undertake necessary system maintenance.
SELECT
I.SECPath, /* 1 */
I.xFileName, /* 2 */
I.filer_name, /* 3 */
I.II_title, /* 4 */
IF ((NOT I.OptionsFlag = '' AND I.OptionsFlag IS NOT NULL), I.OptionsFlag, I.JMTrans_Type) as JMTrans_Type, /* 5 */
I.dollar_value, /* 6 */
I.trans_amount, /* 7 */
I.trans_date_from, /* 8 */
I.trans_date_to, /* 9 */
I.trans_price_from, /* 10 */
I.trans_price_to, /* 11 */
I.amount_owned, /* 12 */
#insider.udf_GetDeltaOwn(IF ((NOT OptionsFlag = '' AND OptionsFlag IS NOT NULL), OptionsFlag, JMTrans_Type), CAST(amount_owned as DECIMAL), CAST(trans_amount as DECIMAL)) as delta_owned,
I.delta as delta_owned, /* 13 */
I.owned_type, /* 14 */
I.formType, /* 15 */
I.entered_date, /* 16 */
insider.udf_OtherInfo(CAST(I.entered_date AS CHAR), CAST(I.formType AS CHAR)) as other_info, /* 17 */
('1/1/1900') as upd_date, /* 18 */
I.UID, /* 19 */
I.ticker, /* 20 */
I.company_name, /* 21 */
I.SIC_Code, /* 22 */
I.OwnerCIK,
I.IssuerCIK,
S.SIC_Sector, /* 23 */
S.SIC_Industry, /* 24 */
S.SIC_SubIndustry, /* 25 */
J.Order as JMOrder, /* 26 */
isRestricted,
is10b51,
CONCAT(SECPath, xFileName) as FullSECPath
,
(
CASE WHEN
(
(I.JMTrans_Type = 'B' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'AB' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'JB*' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'OE*' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'OB' AND I.OptionsFlag = '')
OR I.OptionsFlag = 'B'
OR I.OptionsFlag = 'AB'
OR I.OptionsFlag = 'JB*'
OR I.OptionsFlag = 'OE*'
OR I.OptionsFlag = 'OB'
)
THEN
CASE WHEN sa.ps_ed_1y_n IS NULL OR sa.ps_ed_1y_n = 0 OR 365 > 366 THEN
CASE WHEN sa.ps_ed_9m_n IS NULL OR sa.ps_ed_9m_n = 0 OR 270 > 366 THEN
CASE WHEN sa.ps_ed_6m_n IS NULL OR sa.ps_ed_6m_n = 0 OR 180 > 366 THEN
CASE WHEN sa.ps_ed_3m_n IS NULL OR sa.ps_ed_3m_n = 0 OR 90 > 366 THEN
CASE WHEN sa.ps_ed_1m_n IS NULL OR sa.ps_ed_1m_n = 0 OR 30 > 366 THEN
CASE WHEN sa.ps_ed_1w_n IS NULL OR sa.ps_ed_1w_n = 0 OR 7 > 366 THEN
CONCAT('--|--|--|--|', sa.company_count)
ELSE
-- CASE WHEN 7 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1w', '|', sa.ps_ed_1w_n, '|', sa.ps_ed_1w_hit, '|', sa.ps_ed_1w_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 30 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1m', '|', sa.ps_ed_1m_n, '|', sa.ps_ed_1m_hit, '|', sa.ps_ed_1m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 90 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('3m', '|', sa.ps_ed_3m_n, '|', sa.ps_ed_3m_hit, '|', sa.ps_ed_3m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 180 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('6m', '|', sa.ps_ed_6m_n, '|', sa.ps_ed_6m_hit, '|', sa.ps_ed_6m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 270 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('9m', '|', sa.ps_ed_9m_n, '|', sa.ps_ed_9m_hit, '|', sa.ps_ed_9m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 365 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1y', '|', sa.ps_ed_1y_n, '|', sa.ps_ed_1y_hit, '|', sa.ps_ed_1y_mean, '|', sa.company_count)
-- END
END
ELSE
CASE WHEN
(
(I.JMTrans_Type = 'S' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'AS' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'JS*' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'OS' AND I.OptionsFlag = '')
OR I.OptionsFlag = 'S'
OR I.OptionsFlag = 'AS'
OR I.OptionsFlag = 'JS*'
OR I.OptionsFlag = 'OS'
)
THEN
CASE WHEN sa.ss_ed_1y_n IS NULL OR sa.ss_ed_1y_n = 0 OR 365 > 366 THEN
CASE WHEN sa.ss_ed_9m_n IS NULL OR sa.ss_ed_9m_n = 0 OR 270 > 366 THEN
CASE WHEN sa.ss_ed_6m_n IS NULL OR sa.ss_ed_6m_n = 0 OR 180 > 366 THEN
CASE WHEN sa.ss_ed_3m_n IS NULL OR sa.ss_ed_3m_n = 0 OR 90 > 366 THEN
CASE WHEN sa.ss_ed_1m_n IS NULL OR sa.ss_ed_1m_n = 0 OR 30 > 366 THEN
CASE WHEN sa.ss_ed_1w_n IS NULL OR sa.ss_ed_1w_n = 0 OR 7 > 366 THEN
CONCAT('--|--|--|--|', sa.company_count)
ELSE
-- CASE WHEN 7 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1w', '|', sa.ss_ed_1w_n, '|', sa.ss_ed_1w_hit, '|', sa.ss_ed_1w_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 30 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1m', '|', sa.ss_ed_1m_n, '|', sa.ss_ed_1m_hit, '|', sa.ss_ed_1m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 90 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('3m', '|', sa.ss_ed_3m_n, '|', sa.ss_ed_3m_hit, '|', sa.ss_ed_3m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 180 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('6m', '|', sa.ss_ed_6m_n, '|', sa.ss_ed_6m_hit, '|', sa.ss_ed_6m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 270 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('9m', '|', sa.ss_ed_9m_n, '|', sa.ss_ed_9m_hit, '|', sa.ss_ed_9m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 365 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1y', '|', sa.ss_ed_1y_n, '|', sa.ss_ed_1y_hit, '|', sa.ss_ed_1y_mean, '|', sa.company_count)
-- END
END
ELSE
'er'
END
END) as savvy_analysis,
insider.udf_SEOFriendlyTitle(I.filer_name) as seo_filer_name
FROM
insider.details_import I
LEFT JOIN (insider.SIC_Matrix S) ON (S.SIC = I.SIC_Code)
LEFT JOIN (insider.JMTrans_Types J) ON (J.Type = IF ((NOT OptionsFlag = '' AND OptionsFlag IS NOT NULL), I.OptionsFlag, I.JMTrans_Type))
LEFT JOIN ratings.savvy_analysis_final sa
ON sa.owner_cik = I.OwnerCIK
AND sa.issuer_cik = I.IssuerCIK
WHERE OwnerCIK = '1984667'
AND DATE(DATE_SUB(CAST(CURDATE() AS DATE),INTERVAL 2 YEAR)) < CAST(trans_date_to AS DATE)
#AND DATE(DATE_SUB(CAST(CURDATE() AS DATE),INTERVAL 1 DAY)) >= CAST(entered_date AS DATE)
AND ((JMTrans_Type = 'B' AND OptionsFlag = '') OR OptionsFlag = 'B' OR (JMTrans_Type = 'AB' AND OptionsFlag = '') OR OptionsFlag = 'AB' OR (JMTrans_Type = 'JB' AND OptionsFlag = '') OR OptionsFlag = 'JB' OR (JMTrans_Type = 'JB*' AND OptionsFlag = '') OR OptionsFlag = 'JB*' OR (JMTrans_Type = 'OB' AND OptionsFlag = '') OR OptionsFlag = 'OB' OR (JMTrans_Type = 'OE*' AND OptionsFlag = '') OR OptionsFlag = 'OE*' OR (JMTrans_Type = 'OE' AND OptionsFlag = '') OR OptionsFlag = 'OE' OR (JMTrans_Type = 'S' AND OptionsFlag = '') OR OptionsFlag = 'S' OR (JMTrans_Type = 'AS' AND OptionsFlag = '') OR OptionsFlag = 'AS' OR (JMTrans_Type = 'JS' AND OptionsFlag = '') OR OptionsFlag = 'JS' OR (JMTrans_Type = 'JS*' AND OptionsFlag = '') OR OptionsFlag = 'JS*' OR (JMTrans_Type = 'OS' AND OptionsFlag = '') OR OptionsFlag = 'OS' OR (JMTrans_Type = 'OS*' AND OptionsFlag = '') OR OptionsFlag = 'OS*' OR (JMTrans_Type = '3' AND OptionsFlag = '') OR OptionsFlag = '3')
ORDER BY 9 DESC, 16 DESC , JMOrder;