[SQL] 長期投資研究

2004年以前會計科目是「長期投資合計」，2005年之後用「基金及投資」


長期投資比率：

        select activity_date, long_term_investments_ratio from
        (
            select activity_date, long_term_investments_ratio, max(report_date) from
            (
                select
                L.activity_date,
                L.number / A.number as long_term_investments_ratio,
                L.report_date
                from BalanceSheet as L
                inner join
                BalanceSheet as A
                on L.stock_code = A.stock_code
                and L.activity_date = A.activity_date
                and L.item in ('長期投資合計', '基金及投資')
                and A.item = '資產總計'
                and L.report_type = 'C'
                and A.report_type = 'C'
                and L.stock_code = 1101
            )
            where long_term_investments_ratio is not null
            group by activity_date
            order by activity_date
        )