with t1 as (
select max(CHECKUP_DATE) as d,
MEMBER_CODE as c
from HEALTH_CHECKUP
group by member_code
)
, t2 as (
select max(CHECKUP_DATE) as d,
MEMBER_CODE as c
from HEALTH_CHECKUP
where (CHECKUP_DATE, MEMBER_CODE) not in t1
group by member_code
)
, t as (
select 
t1.d as 'CK_DATE',
t1.c as 'CODE',
-- t1.d as d1,
-- t2.d as d2,
last_name||FIRST_NAME as 'NAME',
round(h1.weight - h2.weight,2) as 'CHG_WT'
from t1
inner join t2 on t1.c=t2.c
inner join MEMBER_MST as m
on m.MEMBER_CODE = t1.c
inner join HEALTH_CHECKUP as h1
on h1.MEMBER_CODE=t1.c 
and h1.CHECKUP_DATE=t1.d
inner join HEALTH_CHECKUP as h2
on h2.MEMBER_CODE=t1.c 
and h2.CHECKUP_DATE=t2.d
)
select *
from t
where abs(CHG_WT) >= 5
order by CHG_WT desc, 2 desc