SELECT
    CH0111.NYUKIN_NO
   ,CH0191_CL_CNT.KNK_CL_CNT
   ,CH0191_CL_CNT.MAX_KNK_IDO_YMD
FROM
    CH_T_NYUKIN CH0111
    INNER JOIN
        (SELECT
            TO_CHAR(COUNT(*)) AS KNK_CL_CNT
           ,CH0191.NYUKIN_NO
           ,MAX(CH0191.KNK_IDO_YMD) AS MAX_KNK_IDO_YMD
        FROM
            (SELECT
                CH0191_RANK.KNK_NO
               ,CH0191_RANK.NYUKIN_NO
               ,CH0191_RANK.GENE_ADD_DTM
               ,CH0191_RANK.KNK_IDO_YMD
            FROM
                (SELECT
                    CH0191_GENE.KNK_NO
                   ,CH0191_GENE.NYUKIN_NO
                   ,CH0191_GENE.GENE_ADD_DTM
                   ,CH0191_GENE.KNK_IDO_YMD
                   ,CH0191_GENE.RIREKI_DATA_FLG
                   ,CH0191_GENE.KNK_MOVE_CD
                   ,RANK() OVER(PARTITION BY CH0191_GENE.KNK_NO ORDER BY CH0191_GENE.GENE_ADD_DTM DESC) AS GENE_ADD_DTM_RANK
                FROM
                    CH_T_KNK CH0191_GENE
                WHERE
                    CH0191_GENE.MK_FLG = '0'
                ) CH0191_RANK
            WHERE
                CH0191_RANK.RIREKI_DATA_FLG   = '1'
            AND CH0191_RANK.KNK_MOVE_CD       = '09'
            AND CH0191_RANK.GENE_ADD_DTM_RANK = 1
            ) CH0191
        GROUP BY
            CH0191.NYUKIN_NO
        ) CH0191_CL_CNT
       ON  CH0111.NYUKIN_NO = CH0191_CL_CNT.NYUKIN_NO
WHERE
    (
        (
            CH0111.OUT_ORG_SHUNO_YMD BETWEEN :OUT_ORG_SHUNO_YMD1 AND :OUT_ORG_SHUNO_YMD2
        )
        OR
        (
            CH0111.OUT_ORG_SHUNO_YMD  < :OUT_ORG_SHUNO_YMD3 AND CH0111.NYUKIN_TRN_YMD > :NYUKIN_TRN_YMD
        )
    )
AND CH0111.NYUKIN_SYRZM_FLG = :NYUKIN_SYRZM_FLG
AND CH0111.NYUKIN_STAT     IN (:NYUKIN_STAT1,:NYUKIN_STAT2)
AND CH0111.MK_FLG           = '0'