テーブル内の他の条件に基づいてレコードをクエリします（レコードの選択）

これは少し注意が必要ですが、sprocからselectにいくつかの派生テーブルを書き込むと、2つの不一致基準を特定できるはずです。1）kcodeを取得しなかったcpの場合、表示回数が多い2）表示回数は等しくて最も早い日付は<割り当てられたcpの最も早い日付です。

select
    ptnt_id
    --Assigned CP values
    , assigned_cp
    , assigned_times_seen
    , assigned_got_kcode
    , assigned_earliest_date_seen
    --Unassigned CP values
    , unassigned_cp
    , unassigned_times_seen
    , unassigned_got_kcode
    , unassigned_earliest_date_seen
    , case 
        when (times_seen_mismatch = 1 or earliest_date_mismatch = 1)
        then 0
        else 1
    end as correct_kcode
from
    (
        select
            ptnt_id
            , kcode_assigned.cp_id as assigned_cp
            , kcode_zero.cp_id as unassigned_cp
            --case to determine where times_seen of assigned cp 
            --is less than that of an unassigned cp
            , case
                when kcode_zero.times_seen > kcode_assigned.times_seen
                then 1
                else 0
            end as times_seen_mismatch
            --case to determine where times_seen of assigned cp 
            --equals times_seen of unassigned cp and the unassigned cp
            --saw the patient on an earlier date than the assigne cp
            , case
                when kcode_zero.times_seen = kcode_assigned.times_seen
                    and kcode_zero.earliest_date_seen < kcode_assigned.earliest_date_seen
                then 1
                else 0
            end as earliest_date_mismatch
            --Assigned CP fields
            , kcode_assigned.times_seen as assigned_times_seen
            , kcode_assigned.got_kcode as assigned_got_kcode
            , kcode_assigned.earliest_date_seen as assigned_earliest_date_seen
            --Unassigned CP fields
            , kcode_zero.times_seen as unassigned_times_seen
            , kcode_zero.got_kcode as unassigned_got_kcode
            , kcode_zero.earliest_date_seen as unassigned_earliest_date_seen
        from
            --derived table to return only those 
            --records where kcode assigned
            (
                select
                    ptnt_id
                    , cp_id
                    , times_seen
                    , got_kcode
                    , earliest_date_seen
                from
                    [your table]
                where
                    got_kcode > 0
            ) kcode_assigned
            left outer join
                --derived table to return only those
                --records where kcode was not assigned
                (
                    select
                        ptnt_id
                        , cp_id
                        , times_seen
                        , got_kcode
                        , earliest_date_seen
                    from
                        [your table]
                    where
                        got_kcode = 0
                ) kcode_zero
                    on kcode_assigne.ptnt_id = kcode_zero.ptnt_id
    ) validate
where
    1=1
    and (times_seen_mismatch = 1 or earliest_date_mismatch = 1)