SELECT
	duka.Up_Minimum,
	duka.Up_Maksimum,
	SUM(COALESCE(duka.Duka_Santunan,0)) AS Duka_Santunan,
	SUM(COALESCE(duka.Duka_Peserta,0)) AS Duka_Peserta,
	SUM(COALESCE(acc.ACC_Santunan,0)) AS ACC_Santunan,
	SUM(COALESCE(acc.ACC_Peserta,0)) AS ACC_Peserta,
	SUM(COALESCE(ajb.AJB_Santunan,0)) AS AJB_Santunan,
	SUM(COALESCE(ajb.AJB_Peserta,0)) AS AJB_Peserta
FROM
	(
		SELECT
			T1.Up_Minimum,
			T1.Up_Maksimum,
			SUM(COALESCE(T2.Duka_Santunan, 0)) AS Duka_Santunan,
			SUM(COALESCE(T2.Duka_Peserta, 0)) AS Duka_Peserta
		FROM
			(
				SELECT
					Polis_Asuransi.No_Proposal,
					UP_Kelompok.Up_Minimum,
					UP_Kelompok.UP_Maksimum
				FROM
					Polis_Asuransi
					INNER JOIN Perusahaan ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs
					CROSS JOIN UP_Kelompok
				WHERE
					(Polis_Asuransi.Status <> 'C')
					AND (Polis_Asuransi.Status <> 'G')
					AND (Polis_Asuransi.Tgl_Mulai <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
					AND (Polis_Asuransi.Tgl_Selesai >= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
					AND (Polis_Asuransi.Mata_Uang = '01') ---tgl diganti 01 u/ rp n 02 u/ USD
			) AS T1
			LEFT OUTER JOIN 
			(
				SELECT
					dbo.Peserta_Produk.No_proposal,
					Duka_Peserta =	CASE 
														WHEN ISNULL(dbo.Peserta_Produk.Santunan_Duka, 0) <> 0 AND dbo.Peserta_Produk.Sub_Kelompok = '0302' THEN 1
														ELSE 0
													END,
					Duka_Santunan = CASE 
														WHEN ISNULL(dbo.Peserta_Produk.Santunan_Duka, 0) <> 0 AND dbo.Peserta_Produk.Sub_Kelompok = '0302' THEN ISNULL(dbo.Peserta_Produk.Santunan_Duka, 0) * 1--@Kurs
														ELSE 0
													END
				FROM
					dbo.Peserta_Produk
				WHERE
					(dbo.Peserta_Produk.Status <> 'C')
			) AS T2 ON T1.No_Proposal = T2.No_Proposal 
								 AND (T2.Duka_Santunan >= T1.Up_Minimum 
								 AND T2.Duka_Santunan <= T1.UP_Maksimum)
		GROUP BY
			T1.Up_Minimum,
			T1.Up_Maksimum
	) AS duka LEFT JOIN
	(
		SELECT
			T1.Up_Minimum,
			T1.Up_Maksimum,
			SUM(COALESCE(T2.ACC_Santunan, 0)) AS ACC_Santunan,
			SUM(COALESCE(T2.ACC_Peserta, 0)) AS ACC_Peserta
		FROM
			(
				SELECT
					Polis_Asuransi.No_Proposal,
					UP_Kelompok.Up_Minimum,
					UP_Kelompok.UP_Maksimum
				FROM
					Polis_Asuransi
					INNER JOIN Perusahaan ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs
					CROSS JOIN UP_Kelompok
				WHERE
					(Polis_Asuransi.Status <> 'C')
					AND (Polis_Asuransi.Status <> 'G')
					AND (Polis_Asuransi.Tgl_Mulai <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
					AND (Polis_Asuransi.Tgl_Selesai >= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
					AND (Polis_Asuransi.Mata_Uang = '01') ---tgl diganti 01 u/ rp n 02 u/ USD
			) AS T1
			LEFT OUTER JOIN 
			(
				SELECT
					dbo.Peserta_Produk.No_proposal,
					ACC_Peserta =		CASE 
														WHEN ISNULL(dbo.Peserta_Produk.Curr_Santunan, 0) <> 0 AND dbo.Peserta_Produk.Sub_Kelompok = '0200' THEN 1
														ELSE 0
													END,
					ACC_Santunan =	CASE 
														WHEN ISNULL(dbo.Peserta_Produk.Curr_Santunan, 0) <> 0 AND dbo.Peserta_Produk.Sub_Kelompok = '0200' THEN dbo.Peserta_Produk.Curr_Santunan * 1--@Kurs
														ELSE 0
													END
				FROM
					dbo.Peserta_Produk
				WHERE
					(dbo.Peserta_Produk.Status <> 'C')
			) AS T2 ON T1.No_Proposal = T2.No_Proposal 
								 AND (T2.ACC_Santunan >= T1.Up_Minimum 
								 AND T2.ACC_Santunan <= T1.UP_Maksimum)
		GROUP BY
			T1.Up_Minimum,
			T1.Up_Maksimum
	) AS acc ON duka.Up_Minimum = acc.Up_Minimum
							AND duka.Up_Maksimum = acc.Up_Maksimum LEFT JOIN
	(
		SELECT
			T1.Up_Minimum,
			T1.Up_Maksimum,
			SUM(COALESCE(T2.AJB_Santunan, 0)) AS AJB_Santunan,
			SUM(COALESCE(T2.AJB_Peserta, 0)) AS AJB_Peserta
		FROM
			(
				SELECT
					Polis_Asuransi.No_Proposal,
					UP_Kelompok.Up_Minimum,
					UP_Kelompok.UP_Maksimum
				FROM
					Polis_Asuransi
					INNER JOIN Perusahaan ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs
					CROSS JOIN UP_Kelompok
				WHERE
					(Polis_Asuransi.Status <> 'C')
					AND (Polis_Asuransi.Status <> 'G')
					AND (Polis_Asuransi.Tgl_Mulai <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
					AND (Polis_Asuransi.Tgl_Selesai >= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))
					AND (Polis_Asuransi.Mata_Uang = '01') ---tgl diganti 01 u/ rp n 02 u/ USD
			) AS T1
			LEFT OUTER JOIN 
			(
				SELECT
					dbo.Peserta_Produk.No_proposal,
					AJB_Peserta =		CASE 
														WHEN ISNULL(dbo.Peserta_Produk.Curr_Santunan, 0) <> 0 AND dbo.Peserta_Produk.Sub_Kelompok = '0100' THEN 1
														ELSE 0
													END,
					AJB_Santunan =	CASE 
														WHEN ISNULL(dbo.Peserta_Produk.Curr_Santunan, 0) <> 0 AND dbo.Peserta_Produk.Sub_Kelompok = '0100' THEN dbo.Peserta_Produk.Curr_Santunan * 1--@Kurs
														ELSE 0
													END
				FROM
					dbo.Peserta_Produk
				WHERE
					(dbo.Peserta_Produk.Status <> 'C')
			) AS T2 ON T1.No_Proposal = T2.No_Proposal 
								 AND (T2.AJB_Santunan >= T1.Up_Minimum
								 AND T2.AJB_Santunan <= T1.UP_Maksimum)
		GROUP BY
			T1.Up_Minimum,
			T1.Up_Maksimum
	) AS ajb ON acc.Up_Minimum = ajb.Up_Minimum
							AND acc.Up_Maksimum = ajb.Up_Maksimum
GROUP BY
	duka.Up_Minimum,
	duka.Up_Maksimum,
	acc.Up_Minimum,
	acc.Up_Maksimum,
	ajb.Up_Minimum,
	ajb.Up_Maksimum
