WITH SUB AS (SELECT CHECKUP_DATE, MEMBER_CODE AS CODE, WEIGHT,
ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE) AS NUMBER
FROM HEALTH_CHECKUP
ORDER BY MEMBER_CODE),
SUB1 AS (SELECT CHECKUP_DATE, CODE,
WEIGHT AS AFTER
FROM SUB
WHERE NUMBER = 2),
SUB2 AS (SELECT CHECKUP_DATE, CODE,
WEIGHT AS BEFORE
FROM SUB
WHERE NUMBER = 1),
SUB3 AS (SELECT SUB1.CHECKUP_DATE, SUB1.CODE, BEFORE, AFTER
FROM SUB1 INNER JOIN SUB2
ON SUB1.CODE = SUB2.CODE),
SUB4 AS (SELECT CHECKUP_DATE AS CK_DATE,
CODE,
CASE WHEN ABS(AFTER - BEFORE) >= 5.0 THEN ROUND(AFTER - BEFORE, 1) END AS CHG_WT
FROM SUB3)
SELECT CK_DATE, CODE, MM.LAST_NAME || MM.FIRST_NAME AS NAME, CHG_WT FROM SUB4
INNER JOIN MEMBER_MST MM
ON SUB4.CODE = MM.MEMBER_CODE
WHERE CHG_WT IS NOT NULL
ORDER BY CHG_WT DESC, CODE DESC