DECLARE
  @tahun INT = 2011

---------------------------------------

DECLARE
  @tgl_transaksi_mulai DATETIME,
  @tgl_transaki_sampai DATETIME
SET @tgl_transaksi_mulai = CAST(CAST(@tahun AS VARCHAR(4)) +  '01' + '01' AS DATETIME)
SET @tgl_transaki_sampai = DATEADD(yy, DATEDIFF(yy,0,CAST(CAST(@tahun AS VARCHAR(4)) +  '01' + '01' AS DATETIME)) + 1, -1)

SELECT
	tidak_ikut.id_link_transaksi
FROM
	(
    SELECT
      terakhir.no_polis,
      terakhir.tgl_transaksi,
      detail.id_link_transaksi,
      --detail.no_polis,
      detail.lt_no_polis,
      --detail.tgl_transaksi,
      detail.periode_bayar,
      detail.suspect_abaikan,
      CASE
        WHEN detail.periode_bayar = 'SINGLE' AND detail.suspect_abaikan = 1 THEN 1
        ELSE 0
      END AS abaikan
    FROM
	    (
        SELECT
          p.no_polis,
          MAX(lt.tgl_transaksi) AS tgl_transaksi
        FROM
          dbo.polis AS p
          INNER JOIN dbo.link_nasabah AS ln ON p.no_polis = ln.no_polis 
          INNER JOIN dbo.link_transaksi AS lt ON ln.id_link_nasabah = lt.id_link_nasabah
          INNER JOIN dbo.link_jenis_transaksi AS jt ON lt.id_jenis_transaksi = jt.id_jenis_transaksi
        WHERE
          dbo.func_get_kelompok_produk(p.id_plan_dasar) IN ('11', '10') -- unit link & hybrid
          AND p.id_polis = dbo.func_get_id_polis_per_tanggal(p.no_polis, ln.tgl_berlaku, 0)
          AND ln.id_jenis_investasi NOT IN ('00') -- semua investasi
          AND lt.id_jenis_transaksi IN ('1', '11', '1A', '24') -- Jenis Transaksi: Top Up Tunggal, Penarikan Dana, Top Up Tunggal HO, Penarikan Dana
          --AND dbo.func_fixed_date(lt.tgl_transaksi) >= @tgl_transaksi_mulai
          --AND dbo.func_fixed_date(lt.tgl_transaksi) <= @tgl_transaki_sampai
          AND (ln.id_link_nasabah = dbo.func_get_id_link_nasabah(p.no_polis, lt.tgl_transaksi))
        GROUP BY
          --lt.id_link_transaksi,
          p.no_polis
      ) AS terakhir INNER JOIN
      (
        SELECT
          lt.id_link_transaksi,
          p.no_polis,
          lt.no_polis AS lt_no_polis,
          lt.tgl_transaksi,
          CASE 
            WHEN p.id_periode_bayar IN ('1','2','3','4') THEN 'REGULAR'
            ELSE 'SINGLE'
          END AS periode_bayar,
              CASE
                WHEN lt.id_jenis_transaksi IN ('11', '24') THEN -- Jenis Transaksi: Penarikan Dana, Penarikan Dana
                  CASE 
                    WHEN ji.id_valuta = 'IDR' THEN  -- jika Rupia
                      CASE
                        WHEN (CAST(dbo.func_link_get_saldo_akhir_unit(p.no_polis, DATEADD(day,1,lt.tgl_transaksi)) AS NUMERIC(20,4)) <= 50000)  -- jika saldo saat itu kurang dari 50rb
                              AND  ( CAST(dbo.func_link_get_saldo_akhir_unit(p.no_polis, GETDATE()) AS NUMERIC(20,4)) <= CAST(dbo.func_link_get_saldo_akhir_unit(p.no_polis, DATEADD(day,1,lt.tgl_transaksi)) AS NUMERIC(20,4))) -- dan saldo hari ini lebih kecil sama dengan saldo pada saat itu
                              THEN 1 -- maka transaksi tsb dianggap transaksi surrender
                        ELSE 0
                      END
                    ELSE  -- jika selain rupiah (maksudnya USD)
                      CASE
                        WHEN (CAST(dbo.func_link_get_saldo_akhir_unit(p.no_polis, DATEADD(day,1,lt.tgl_transaksi)) AS NUMERIC(20,4)) <= 5)  -- jika saldo saat itu kurang dari 5 dollar
                              AND  ( CAST(dbo.func_link_get_saldo_akhir_unit(p.no_polis, GETDATE()) AS NUMERIC(20,4)) <= CAST(dbo.func_link_get_saldo_akhir_unit(p.no_polis, DATEADD(day,1,lt.tgl_transaksi)) AS NUMERIC(20,4))) -- dan saldo hari ini lebih kecil sama dengan saldo pada saat itu
                              THEN 1 -- maka transaksi tsb dianggap transaksi surrender
                        ELSE 0
                      END
                  END
                ELSE 0  -- Jika transaksi bukan penarikan dana
              END AS suspect_abaikan
        FROM
          dbo.polis AS p
          INNER JOIN dbo.link_nasabah AS ln ON p.no_polis = ln.no_polis 
          INNER JOIN dbo.link_transaksi AS lt ON ln.id_link_nasabah = lt.id_link_nasabah
          INNER JOIN dbo.jenis_investasi AS ji ON ln.id_jenis_investasi=ji.id_jenis_investasi
          INNER JOIN dbo.link_jenis_transaksi AS jt ON lt.id_jenis_transaksi = jt.id_jenis_transaksi
        WHERE
          dbo.func_get_kelompok_produk(p.id_plan_dasar) IN ('11', '10') -- unit link & hybrid
          AND p.id_polis = dbo.func_get_id_polis_per_tanggal(p.no_polis, ln.tgl_berlaku, 0)
          AND ln.id_jenis_investasi NOT IN ('00') -- semua investasi
          AND lt.id_jenis_transaksi IN ('1', '11', '1A', '24') -- Jenis Transaksi: Top Up Tunggal, Penarikan Dana, Top Up Tunggal HO, Penarikan Dana
          AND dbo.func_fixed_date(lt.tgl_transaksi) >= @tgl_transaksi_mulai
          AND dbo.func_fixed_date(lt.tgl_transaksi) <= @tgl_transaki_sampai
          AND (ln.id_link_nasabah = dbo.func_get_id_link_nasabah(p.no_polis, lt.tgl_transaksi))
      ) AS detail ON terakhir.no_polis = detail.no_polis
                     AND terakhir.tgl_transaksi = detail.tgl_transaksi
  ) AS tidak_ikut
WHERE
  tidak_ikut.abaikan = 1