with LATEST_CHECKUP_DATE as (SELECT
MEMBER_CODE 
,MAX(CHECKUP_DATE)
FROM HEALTH_CHECKUP
GROUP BY MEMBER_CODE
)

SELECT
LATEST_CHECK.CHECKUP_DATE AS CK_DATE
,LATEST_CHECK.MEMBER_CODE AS CODE
,LATEST_CHECK.LAST_NAME || LATEST_CHECK.FIRST_NAME AS NAME
,(LATEST_CHECK.WEIGHT - OLD_CHECK.WEIGHT) AS CHG_WT  --小数点以下がゼロの場合は整数のみの表示

FROM (
-- 前回の体重
	SELECT
	 HC.MEMBER_CODE 
	,HC.CHECKUP_DATE
	,HC.LAST_NAME
	,HC.FIRST_NAME
	,HC.WEIGHT
	FROM HEALTH_CHECKUP AS HC
	INNER JOIN LATEST_CHECKUP_DATE AS LHC
	ON  HC.MEMBER_CODE = LHC.MEMBER_CODE
	AND HC.CHECKUP_DATE < LHC.CHECKUP_DATE
) AS OLD_CHECK
 
LEFT JOIN  (
--最新の体重
	SELECT
	HC.MEMBER_CODE 
	,HC.CHECKUP_DATE
	,HC.LAST_NAME
	,HC.FIRST_NAME
	,HC.WEIGHT
	FROM HEALTH_CHECKUP AS HC
	INNER JOIN LATEST_CHECKUP_DATE AS LHC
	ON HC.MEMBER_CODE = LHC.MEMBER_CODE
	AND HC.CHECKUP_DATE = LHC.CHECKUP_DATE
) AS LATEST_CHECK

 ON OLD_CHECK.MEMBER_CODE = LATEST_CHECK.MEMBER_CODE
AND OLD_CHECK.CHECKUP_DATE = LATEST_CHECK.CHECKUP_DATE

WHERE (LATEST_CHECK.WEIGHT - OLD_CHECK.WEIGHT) >= 5.0

ORDER BY 
(LATEST_CHECK.WEIGHT - OLD_CHECK.WEIGHT) DESC
,LATEST_CHECK.MEMBER_CODE DESC