USE [IKU]
GO

/****** Object:  Table [dbo].[TRN_MONA]    Script Date: 6/19/2018 5:42:04 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TRN_MONA](
	[YEAR] [int] NULL,
	[ID_BANK] [varchar](10) NULL,
	[TIPE_DOKUMEN] [varchar](20) NULL,
	[KETERANGAN] [varchar](200) NULL,
	[START_DATE] [datetime] NULL,
	[TARGET_DATE] [datetime] NULL,
	[REALIZATION_DATE] [datetime] NULL,
	[USER_CREATED] [varchar](20) NULL,
	[DATE_CREATED] [datetime] NULL,
	[USER_UPDATED] [varchar](20) NULL,
	[DATE_UPDATED] [datetime] NULL
) ON [PRIMARY]
GO


CREATE VIEW IKUREPORT AS

SELECT        KODE_IKU, TAHUN_REALISASI, PERIODE, NILAI_REALISASI, INDICATOR, NILAI_REALISASI / INDICATOR * 100 AS REALISASI, TARGET,
                         (NILAI_REALISASI / INDICATOR * 100) / TARGET * 100 AS PENCAPAIAN
FROM            (SELECT        KODE_IKU, TAHUN_REALISASI, PERIODE, SUM(NILAI_REALISASI_1) AS NILAI_REALISASI, CAST
                                                        ((SELECT        SUM(NILAI_INDICATOR_1)
                                                            FROM            TRN_INDICATOR_QN_DTL B
                                                            WHERE        A.KODE_IKU = B.KODE_IKU AND A.PERIODE = B.PERIODE AND A.TAHUN_REALISASI = B.TAHUN_INDICATOR) AS FLOAT) AS INDICATOR,
                                                    CAST
                                                        ((SELECT        THRESHOLD
                                                            FROM            TRN_INDICATOR_QN B
                                                            WHERE        A.KODE_IKU = B.KODE_IKU AND A.PERIODE = B.PERIODE AND A.TAHUN_REALISASI = B.TAHUN_INDICATOR) AS FLOAT)
                                                    AS TARGET
                          FROM            TRN_REALIZATION_QN_DTL A
                          GROUP BY KODE_IKU, TAHUN_REALISASI, PERIODE) B
UNION
SELECT        KODE_IKU, TAHUN_REALISASI, PERIODE, NILAI_REALISASI, INDICATOR, NILAI_REALISASI / INDICATOR * 100 AS REALISASI, TARGET,
                         (NILAI_REALISASI / INDICATOR * 100) / TARGET * 100 AS PENCAPAIAN
FROM            (SELECT DISTINCT KODE_IKU, TAHUN_REALISASI, PERIODE, CAST
                                                        ((SELECT        COUNT(0)
                                                            FROM            TRN_REALIZATION_QL B
                                                            WHERE        STATUS = 'Selesai' AND a.KODE_IKU = b.KODE_IKU AND a.TAHUN_REALISASI = b.TAHUN_REALISASI AND a.PERIODE = b.PERIODE)
                                                    AS FLOAT) AS NILAI_REALISASI, CAST
                                                        ((SELECT        COUNT(0)
                                                            FROM            TRN_REALIZATION_QL B
                                                            WHERE        a.KODE_IKU = b.KODE_IKU AND a.TAHUN_REALISASI = b.TAHUN_REALISASI AND a.PERIODE = b.PERIODE) AS FLOAT) AS INDICATOR,
                                                    100 AS TARGET
                          FROM            TRN_REALIZATION_QL A) C