DECLARE 
@TglAwal Datetime='2010-01-01', 
@TglAkhir Datetime='2010-02-01'


IF OBJECT_ID(N'TmpInsurance..Prib_Polis_Current', N'U') IS NOT NULL 
DROP TABLE [TmpInsurance].[dbo].[Prib_Polis_Current]


  CREATE TABLE [TmpInsurance].[dbo].[Prib_Polis_Current] (
	[No_Proposal] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Sub_Kelompok] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Nama_Perusahaan] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[No_Prs] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Tgl_Masuk] [datetime] NULL ,
	[Tgl_Mulai] [datetime] NULL ,
	[Tgl_Selesai] [datetime] NULL ,
	
	[Polis_Awal] [int] NULL CONSTRAINT [DF_Prib_Polis_Current_Polis_Awal] DEFAULT (0),
	[Polis_Baru] [int] NULL CONSTRAINT [DF_Prib_Polis_Current_Polis_Baru] DEFAULT (0),
	[Polis_Lapse] [int] NULL CONSTRAINT [DF_Prib_Polis_Current_Polis_Lapse] DEFAULT (0),
	[Polis_Akhir] [int] NULL CONSTRAINT [DF_Prib_Polis_Current_Polis_Akhir] DEFAULT (0),
	CONSTRAINT [PK_Prib_Polis_Current] PRIMARY KEY  CLUSTERED 
	(
		[No_Proposal],[Sub_Kelompok]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--  exec proc_eod
  SET NOCOUNT ON
  ---------------------------------------------
  -- INSERT POLIS INFORCE DALAM PERIODE LAPORAN
  --DELETE FROM TmpInsurance.dbo.Prib_Polis_Current
  INSERT INTO TmpInsurance.dbo.Prib_Polis_Current (No_Proposal, Sub_Kelompok, Nama_Perusahaan, No_Prs, Tgl_Masuk, Tgl_Mulai, Tgl_Selesai)
  SELECT  dbo.Polis_Asuransi.No_Proposal, Produk_Polis.Id_Produk, dbo.Perusahaan.Nama_Perusahaan, dbo.Polis_Asuransi.No_Prs, 
          dbo.Perusahaan.Tgl_Masuk, dbo.Polis_Asuransi.Tgl_Mulai, dbo.Polis_Asuransi.Tgl_Selesai
  FROM    dbo.Polis_Asuransi INNER JOIN
          dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs INNER JOIN Produk_Polis ON Polis_Asuransi.No_Proposal = Produk_Polis.No_Proposal
        
  WHERE   (dbo.Polis_Asuransi.Status <> 'C') AND (dbo.Polis_Asuransi.Status <> 'G') AND (ISNULL(dbo.Polis_Asuransi.No_Polis, '') <> '')
          AND (Tgl_Selesai>=@TglAwal-1) AND (Tgl_Mulai<=@TglAkhir)
          
          
          
  ------------------------
  UPDATE TmpInsurance.dbo.Prib_Polis_Current SET Polis_Awal=1   WHERE Tgl_Mulai<=@TglAwal-1 AND Tgl_Selesai>=@TglAwal-1
  UPDATE TmpInsurance.dbo.Prib_Polis_Current SET Polis_Baru=1   WHERE Tgl_Mulai>=@TglAwal AND Tgl_Mulai<=@TglAkhir
  UPDATE TmpInsurance.dbo.Prib_Polis_Current SET Polis_Lapse=1  WHERE Tgl_Selesai<=@TglAkhir
  UPDATE TmpInsurance.dbo.Prib_Polis_Current SET Polis_Akhir=1  WHERE Tgl_Selesai>=@TglAkhir+1 AND Polis_Lapse<>1
  
SELECT     TmpInsurance.dbo.Prib_Polis_Current.No_Proposal, TmpInsurance.dbo.Prib_Polis_Current.Sub_Kelompok, Produk_Polis.Sub_Kelompok AS Expr1, 
                      Sub_Kelompok.Nama_Sub, TmpInsurance.dbo.Prib_Polis_Current.Nama_Perusahaan, TmpInsurance.dbo.Prib_Polis_Current.No_Prs, 
                      TmpInsurance.dbo.Prib_Polis_Current.Tgl_Masuk, TmpInsurance.dbo.Prib_Polis_Current.Tgl_Mulai, TmpInsurance.dbo.Prib_Polis_Current.Tgl_Selesai, 
                      TmpInsurance.dbo.Prib_Polis_Current.Polis_Awal, TmpInsurance.dbo.Prib_Polis_Current.Polis_Baru, TmpInsurance.dbo.Prib_Polis_Current.Polis_Lapse, 
                      TmpInsurance.dbo.Prib_Polis_Current.Polis_Akhir
FROM         TmpInsurance.dbo.Prib_Polis_Current INNER JOIN
                      Produk_Polis ON TmpInsurance.dbo.Prib_Polis_Current.No_Proposal = Produk_Polis.No_Proposal AND 
                      TmpInsurance.dbo.Prib_Polis_Current.Sub_Kelompok = Produk_Polis.Id_Produk INNER JOIN
                      Sub_Kelompok ON Produk_Polis.Sub_Kelompok = Sub_Kelompok.Sub_Kelompok