WITH HC AS
(
SELECT
 CHECKUP_DATE,
 MEMBER_CODE,
 WEIGHT,
 ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS RANK
FROM
 HEALTH_CHECKUP
)
SELECT
 AFTER.CHECKUP_DATE AS CK_DATE,
 AFTER.MEMBER_CODE AS CODE,
 LAST_NAME || FIRST_NAME AS NAME,
 ROUND(AFTER.WEIGHT - BEFORE.WEIGHT, 1) AS CHG_WT
FROM
(
SELECT
 CHECKUP_DATE,
 MEMBER_CODE,
 WEIGHT
FROM
 HC
WHERE RANK = 1
) AFTER
LEFT OUTER JOIN
(
SELECT
 CHECKUP_DATE,
 MEMBER_CODE,
 WEIGHT
FROM
 HC
WHERE RANK = 2
) BEFORE
ON
 BEFORE.MEMBER_CODE = AFTER.MEMBER_CODE
LEFT OUTER JOIN
 MEMBER_MST
ON
 MEMBER_MST.MEMBER_CODE = AFTER.MEMBER_CODE
WHERE
 ROUND(AFTER.WEIGHT - BEFORE.WEIGHT, 1) >= 5
OR
 ROUND(AFTER.WEIGHT - BEFORE.WEIGHT, 1) <= -5
ORDER BY
 ROUND(AFTER.WEIGHT - BEFORE.WEIGHT, 1) DESC,
 AFTER.MEMBER_CODE DESC