SQLite - CASE WHEN

Example:

計算 2330(台積電) 年化ROE:

select activity_date,
case
    when strftime('%m', activity_date) = '03' then roe * 4/1
    when strftime('%m', activity_date) = '06' then roe * 4/2
    when strftime('%m', activity_date) = '09' then roe * 4/3
    else roe
end as annual_adjusted_roe
from
(
    select activity_date, roe, max(report_date) from
    (
        select
        E.activity_date,
        I.number / E.number as roe,
        E.report_date
        from BalanceSheet as E
        inner join
        IncomeStmt as I
        on E.stock_code = I.stock_code
        and E.activity_date = I.activity_date
        and E.item = '股東權益總計'
        and I.item = '合併總損益'
        and E.report_type = 'C'
        and I.report_type = 'C'
        and E.stock_code = '2330'
    )
    where roe is not null
    group by activity_date
    order by activity_date
)



Result:

"2004-12-01","0.23137723860560547"
"2005-06-01","0.18294564717444345"
"2005-12-01","0.20982641425180892"
"2006-06-01","0.29793076021065906"
"2006-12-01","0.2498246389394268"
"2007-03-01","0.14263155367277086"
"2007-06-01","0.18819824997708876"
"2007-09-01","0.19872114434046592"
"2007-12-01","0.22403837915176847"
"2008-03-01","0.22017673663001705"
"2008-06-01","0.25570481677155477"
"2008-09-01","0.2515449671927223"
"2008-12-01","0.20926102952524128"
"2009-03-01","0.012261980810645017"
"2009-06-01","0.11859167096946371"
"2009-09-01","0.1617447509053049"
"2009-12-01","0.1792735864247019"
"2010-03-01","0.2541800955198025"
"2010-06-01","0.29903653176286066"
"2010-09-01","0.29928906353328644"
"2010-12-01","0.28042283521239136"
"2011-03-01","0.23793318292084237"
"2011-06-01","0.2549615622488371"
"2011-09-01","0.22821454301634214"
"2011-12-01","0.21272784096487046"
"2012-03-01","0.2011224622441732"
"2012-06-01","0.23849902699697093"