SELECT
	web.*,
	core.*
FROM
  (
    SELECT
	      id_peserta,
	      no_polis,
	      nama,
	      tgl_lahir,
	      nama_peserta_2,
	      tgl_lahir_peserta_2,
	      berat_badan,
	      uang_pertanggungan,
	      id_valuta,
	      tgl_mulai,
	      masa_kontrak,
	      kode_produk,
	      bunga,
	      premi,
	      prosen_extra_premi,
	      extra_premi,
	      tinggi_badan,
	      satuan_masa,
	      umur,
	      jenis_kelamin,
	      keterangan,
	      id_cabang,
	      no_peserta,
	      no_hp,
	      email,
	      kode_medical,
	      keterangan_medical,
	      jenis_kredit,
	      status,
	      create_date,
	      create_by,
	      change_date,
	      change_by,
	      submitted,
	      submitted_date,
	      submitted_by
    FROM
	    dbo.peserta_ajk AS pa
    WHERE
      pa.submitted = 1
  ) AS web LEFT JOIN
	(
	  SELECT
	  	no_peserta
	  FROM
	  	AJK.dbo.peserta AS p
	) AS core ON web.no_peserta = core.no_peserta
WHERE
  core.no_peserta IS NULL


SELECT
	*
FROM
  dbo.peserta_ajk AS pa
 WHERE
  pa.submitted = 1
  AND (pa.no_peserta IS NULL OR pa.no_peserta = '')


SELECT
	*
FROM
  dbo.peserta_ajk AS pa
WHERE
  pa.submitted = 0
  AND (pa.no_peserta IS NOT NULL AND pa.no_peserta <> '')

  
SELECT
	*
FROM
  dbo.peserta_ajk AS pa
WHERE
  pa.submitted = 0
ORDER BY
  create_date  