--会員別直近の診断日
WITH CHECK_LAST as (
select MAX(CHECKUP_DATE) as MAX_DATE, MEMBER_CODE, WEIGHT 
  from HEALTH_CHECKUP 
 group by MEMBER_CODE
)
--会員別直近１つ前の診断日
, CHECK_LAST1 as (
select MAX(HEALTH.CHECKUP_DATE) as MAX_DATE, HEALTH.MEMBER_CODE, HEALTH.WEIGHT 
  from HEALTH_CHECKUP as HEALTH
      ,CHECK_LAST
 where HEALTH.CHECKUP_DATE<CHECK_LAST.MAX_DATE
   and HEALTH.MEMBER_CODE=CHECK_LAST.MEMBER_CODE
 group by HEALTH.MEMBER_CODE
)
, DIFF_WEIGHT as (
select CHECK_LAST.MAX_DATE, CHECK_LAST.MEMBER_CODE, round(CHECK_LAST.WEIGHT-CHECK_LAST1.WEIGHT, 1) as CHG_WT
from CHECK_LAST, CHECK_LAST1
where CHECK_LAST.MEMBER_CODE=CHECK_LAST1.MEMBER_CODE
order by CHG_WT desc
)
, DIFF_WEIGHT_5 as (
select * from DIFF_WEIGHT 
 where DIFF_WEIGHT.CHG_WT<=-5.0 or 5.0<=DIFF_WEIGHT.CHG_WT
order by MEMBER_CODE
)

select DIFF_WEIGHT_5.MAX_DATE, DIFF_WEIGHT_5.MEMBER_CODE as CODE, MEMBER_MST.LAST_NAME || MEMBER_MST.FIRST_NAME as NAME, CHG_WT 
  from DIFF_WEIGHT_5 LEFT OUTER JOIN MEMBER_MST ON DIFF_WEIGHT_5.MEMBER_CODE=MEMBER_MST.MEMBER_CODE
order by CHG_WT desc, CODE desc