with C as ( -- 直近
select MEMBER_CODE, max(CHECKUP_DATE) as CK_DATE , WEIGHT 
from HEALTH_CHECKUP
group by MEMBER_CODE 
) , L as ( -- 前回
select H.MEMBER_CODE, max(H.CHECKUP_DATE) as CK_DATE , H.WEIGHT 
from HEALTH_CHECKUP as H inner join C on H.MEMBER_CODE = C.MEMBER_CODE
where H.CHECKUP_DATE < C.CK_DATE
group by H.MEMBER_CODE
)
select C.CK_DATE as CK_DATE , C.MEMBER_CODE as CODE
    , M.LAST_NAME || M.FIRST_NAME as NAME
    ,round(C.WEIGHT*1.0 - L.WEIGHT*1.0,1) as CHG_WT
    from C inner join L on C.MEMBER_CODE = L.MEMBER_CODE 
    inner join MEMBER_MST as M on C.MEMBER_CODE = M.MEMBER_CODE 
    where ABS(round(C.WEIGHT*1.0 - L.WEIGHT*1.0,1) ) >= 5.0 
order by 4 desc , 2 desc