WITH
--直近の日にち
wk_daylist1(MEMBER_CODE, day1)
AS
(
 SELECT
  MEMBER_CODE
  ,MAX(CHECKUP_DATE) as day1
 FROM
  HEALTH_CHECKUP
 GROUP BY
  MEMBER_CODE
)
,
--直近の日にちの体重
wk_daylist1_wg(MEMBER_CODE, day1, wg1)
AS
(
 SELECT
  a.MEMBER_CODE
  ,a.CHECKUP_DATE as day1
  ,a.WEIGHT
 FROM
  HEALTH_CHECKUP a
  inner join
  wk_daylist1 b
  on a.MEMBER_CODE=b.MEMBER_CODE and a.CHECKUP_DATE=b.day1
)
,
--直近の一つ前の日にち
wk_daylist2(MEMBER_CODE, day2)
AS
(
SELECT
 MEMBER_CODE
  ,MAX(CHECKUP_DATE) as day2
FROM
 HEALTH_CHECKUP
WHERE
 NOT EXISTS (select 1 from wk_daylist1 WHERE HEALTH_CHECKUP.MEMBER_CODE=wk_daylist1.MEMBER_CODE and HEALTH_CHECKUP.CHECKUP_DATE=wk_daylist1.day1)
GROUP BY
 MEMBER_CODE
)
,
--直近の一つ前の日にちの体重
wk_daylist2_wg(MEMBER_CODE, day2, wg2)
AS
(
 SELECT
  a.MEMBER_CODE
  ,a.CHECKUP_DATE as day2
  ,a.WEIGHT
 FROM
  HEALTH_CHECKUP a
  inner join
  wk_daylist2 b
  on a.MEMBER_CODE=b.MEMBER_CODE and a.CHECKUP_DATE=b.day2
)

SELECT
 y.day1 as CK_DATE
 ,x.MEMBER_CODE as CODE
 ,(x.LAST_NAME || x.FIRST_NAME) as NAME
 ,(z.wg2 - y.wg1) as CHG_WT
FROM
 MEMBER_MST x
 inner join
 wk_daylist1_wg y
 on x.MEMBER_CODE = y.MEMBER_CODE
 inner join
 wk_daylist2_wg z
 on x.MEMBER_CODE = z.MEMBER_CODE
WHERE
 CHG_WT>=5
ORDER BY
 CHG_WT desc
 ,x.MEMBER_CODE desc