SELECT --TOP 1
  Polis_Asuransi.No_Proposal,
  member_id = CASE WHEN RIGHT(polis_asuransi.no_polis, 1) = 0
                        AND produk_polis.sub_kelompok = '0304' THEN LEFT(CASE WHEN CHARINDEX('-', cfah.MEMBER_ID) = 0 THEN SUBSTRING(cfah.MEMBER_ID, 1, 10) + '-' + RIGHT(cfah.MEMBER_ID, 1)
                                                                              ELSE cfah.MEMBER_ID
                                                                         END, 11) + '0'
                   ELSE CASE WHEN CHARINDEX('-', cfah.MEMBER_ID) = 0 THEN SUBSTRING(cfah.MEMBER_ID, 1, 10) + '-' + RIGHT(cfah.MEMBER_ID, 1)
                             ELSE cfah.MEMBER_ID
                        END
              END,
  NO_SANTUNAN = CASE WHEN ISNULL(Produk_Polis.keluarga, 0) = 1 THEN LEFT(CASE WHEN CHARINDEX('-', cfah.MEMBER_ID) = 0 THEN SUBSTRING(cfah.MEMBER_ID, 1, 10) + '-' + RIGHT(cfah.MEMBER_ID, 1)
                                                                              ELSE cfah.MEMBER_ID
                                                                         END, 11) + '0'
                     ELSE CASE WHEN CHARINDEX('-', cfah.MEMBER_ID) = 0 THEN SUBSTRING(cfah.MEMBER_ID, 1, 10) + '-' + RIGHT(cfah.MEMBER_ID, 1)
                               ELSE cfah.MEMBER_ID
                          END
                END,
  id_plan = SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5),
  produk_polis.Sub_Kelompok,
  cfah.DIAGNOSIS_CODE, 
  provider.IDI,
  ADMISSION_DATE = CASE WHEN CHARINDEX('-', cfah.ADMISSION_DATE) <> 0 THEN SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
                        ELSE SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
                   END,
  CLAIM_TYPE = CASE cfah.CLAIM_TYPE
                 WHEN 'R' THEN 'R'
                 ELSE 'P'
               END,
  cfah.AMT_INCURRED,
  cfah.AMT_APPROVED,
  cfah.AMT_NOT_APPROVED,
  cfah.CLAIM_ID,
  CASE WHEN CHARINDEX('-', cfah.ADMISSION_DATE) <> 0 THEN SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
       ELSE SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
  END,
  CASE cfah.CLAIM_TYPE
    WHEN 'R' THEN 21
    ELSE 20
  END,
  1,
  
  CASE 
    WHEN CHARINDEX('-', cfah.ADMISSION_DATE) <> 0 THEN SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
    ELSE SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
  END AS cek_1, -- >= 
  CASE 
    WHEN Polis_Asuransi.Open_polis = 1 THEN CAST(DATEPART(year, GETDATE()) AS VARCHAR(4)) + '-01-01'
    ELSE Polis_Asuransi.tgl_mulai
  END AS cek_2  --  2016-12-22  >=  2017-01-01 00:00:00.000
FROM
  claim_from_admedika_header cfah
  INNER JOIN Polis_Asuransi ON cfah.POLICY_NUMBER = REPLACE(Polis_Asuransi.No_Polis, '.', '')
  INNER JOIN Produk_Polis ON SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5) = Produk_Polis.Id_Produk
  LEFT JOIN (
             SELECT
              MAX(IDI) AS IDI,
              Kode_AdProvider
             FROM
              Provider
             GROUP BY
              Kode_AdProvider
            ) AS Provider ON Provider.Kode_AdProvider = cfah.PROVIDER_CODE
WHERE
  cfah.CLAIM_ID = '57224410'
  AND id_claim_from_admedika_header = 3976663
  AND COALESCE(claim_proses_status, '0') = 0--@CLAIM_PROSES_STATUS
  
  
  AND CASE 
        WHEN CHARINDEX('-', cfah.ADMISSION_DATE) <> 0 THEN SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
        ELSE SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
      END 
        >= 
      CASE 
        WHEN Polis_Asuransi.Open_polis = 1 THEN CAST(DATEPART(year, GETDATE()) AS VARCHAR(4)) + '-01-01'
        ELSE Polis_Asuransi.tgl_mulai
      END
  
  
  AND CASE WHEN CHARINDEX('-', cfah.ADMISSION_DATE) <> 0 THEN SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
           ELSE SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
      END <= CASE WHEN Polis_Asuransi.Open_polis = 1 THEN CAST(DATEPART(year, GETDATE()) AS VARCHAR(4)) + '-12-31'
                  ELSE Polis_Asuransi.Tgl_Selesai
             END
  AND LEFT(member_id, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
  AND LEFT(cfah.DIAGNOSIS_CODE, 6) <> 'PRORAT'
  AND (LEFT(COVERAGE_ID, 1) NOT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') OR PROVIDER_CODE <> 'NPK')
  AND (Polis_Asuransi.status = 'I' OR polis_asuransi.open_polis = 0)