--SELECT
--    x.Status, 
--    x.Nama_Perusahaan,
--    x.No_Ref, 
--    x.No_Note, 
--    x.Description, 
--    x.Tgl_Buat, 
--    x.Total, 
--    x.Jml_Lunas,
--    x.Saldo, 
--    x.No_Polis, 
--    x.Tgl_Mulai, 
--    x.Tgl_Selesai, 
--    x.No_Proposal, 
--    x.Tgl_Lunas, 
--    x.Change_By, 
--    x.Change_Date, 
--    x.Jenis, 
--    x.Tgl_Terima, 
--    x.Nama_Payor, 
--    x.Id_Payor, 
--    x.GT_OG,
--    x.Jml_Peserta,
--    x.Marketing,
--    SUM(x.AJB) AS AJB,
--    SUM(x.ACC) AS ACC,
--    SUM(x.RI) AS RI,
--    SUM(x.RJ) AS RJ,
--    SUM(x.Gigi) AS Gigi,
--    SUM(x.Lahir) AS Lahir,
--    SUM(x.Kacamata) AS Kacamata
--FROM (
--      SELECT TOP 50
--        dbo.Note_Terbit_Master.Status, 
--        dbo.Perusahaan.Nama_Perusahaan,
--        dbo.Note_Terbit_Master.No_Ref, 
--        dbo.Note_Terbit_Master.No_Note, 
--        dbo.Note_Jenis.Description, 
--        dbo.Note_Terbit_Master.Tgl_Buat, 
--        dbo.Note_Terbit_Master.Total, 
--        dbo.Note_Terbit_Master.Jml_Lunas,
--        dbo.Note_Terbit_Master.Saldo, 
--        dbo.Note_Terbit_Master.No_Polis, 
--        dbo.Polis_Asuransi.Tgl_Mulai, 
--        dbo.Polis_Asuransi.Tgl_Selesai, 
--        dbo.Note_Terbit_Master.No_Proposal, 
--        dbo.Note_Terbit_Master.Tgl_Lunas, 
--        dbo.Note_Terbit_Master.Change_By, 
--        dbo.Note_Terbit_Master.Change_Date, 
--        dbo.Note_Terbit_Master.Jenis, 
--        dbo.Note_Shipping.Tgl_Terima, 
--        dbo.Payor_Header.Nama_Payor, 
--        dbo.Polis_Asuransi.Id_Payor, 
--        dbo.Note_Terbit_Master.GT_OG,
--        dbo.Note_Terbit_Master.Jml_Peserta,
--        dbo.Polis_Asuransi.Marketing,
--        --dbo.Note_Terbit_Detail.Sub_Kelompok,
--        --dbo.Note_Terbit_Detail.Total,
--        --ajb, acc, ri, rj, g, lahir, kacamata
--        --0100, 0200, 0302, 0301, 0303, 0304, 0305
--        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
--          WHEN '0100' THEN dbo.Note_Terbit_Detail.Total
--          ELSE 0
--        END AS 'AJB',
--        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
--          WHEN '0200' THEN dbo.Note_Terbit_Detail.Total
--          ELSE 0
--        END AS 'ACC',
--        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
--          WHEN '0302' THEN dbo.Note_Terbit_Detail.Total
--          ELSE 0
--        END AS 'RI',
--        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
--          WHEN '0301' THEN dbo.Note_Terbit_Detail.Total
--          ELSE 0
--        END AS 'RJ',
--        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
--          WHEN '0303' THEN dbo.Note_Terbit_Detail.Total
--          ELSE 0
--        END AS 'Gigi',
--        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
--          WHEN '0304' THEN dbo.Note_Terbit_Detail.Total
--          ELSE 0
--        END AS 'Lahir',
--        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
--          WHEN '0305' THEN dbo.Note_Terbit_Detail.Total
--          ELSE 0
--        END AS 'Kacamata'
--      FROM 
--        dbo.Payor_Header INNER JOIN
--        dbo.Perusahaan INNER JOIN
--        dbo.Polis_Asuransi ON dbo.Perusahaan.No_Prs = dbo.Polis_Asuransi.No_Prs ON 
--        dbo.Payor_Header.Id_Payor = dbo.Polis_Asuransi.Id_Payor RIGHT OUTER JOIN
--        dbo.Note_Jenis INNER JOIN
--        dbo.Note_Terbit_Master ON dbo.Note_Jenis.Kode = dbo.Note_Terbit_Master.Jenis LEFT OUTER JOIN
--        dbo.Note_Shipping ON dbo.Note_Terbit_Master.No_Ref = dbo.Note_Shipping.No_Ref ON 
--        dbo.Polis_Asuransi.No_Proposal = dbo.Note_Terbit_Master.No_Proposal INNER JOIN
--        dbo.Note_Terbit_Detail ON dbo.Note_Terbit_Master.No_Ref=dbo.Note_Terbit_Detail.No_Ref
--      WHERE 
--        (dbo.Note_Terbit_Master.Status = 'I') 
--        AND (dbo.Note_Terbit_Master.Jenis = 11) 
--        AND (dbo.Note_Terbit_Master.GT_OG = 'GT') 
--        AND (dbo.Note_Terbit_Master.Tgl_Buat >= CONVERT(DATETIME, '2009-04-01 00:00:00', 102))
--      ORDER BY 
--        dbo.Perusahaan.Nama_Perusahaan, 
--        dbo.Note_Terbit_Master.Tgl_Buat DESC
--     ) x
--GROUP BY
--    x.Status, 
--    x.Nama_Perusahaan,
--    x.No_Ref, 
--    x.No_Note, 
--    x.Description, 
--    x.Tgl_Buat, 
--    x.Total, 
--    x.Jml_Lunas,
--    x.Saldo, 
--    x.No_Polis, 
--    x.Tgl_Mulai, 
--    x.Tgl_Selesai, 
--    x.No_Proposal, 
--    x.Tgl_Lunas, 
--    x.Change_By, 
--    x.Change_Date, 
--    x.Jenis, 
--    x.Tgl_Terima, 
--    x.Nama_Payor, 
--    x.Id_Payor, 
--    x.GT_OG,
--    x.Jml_Peserta,
--    x.Marketing



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

SELECT
    x.Description AS Type_Of_Transaction,
    x.Marketing,
    x.No_Polis, 
    x.No_Proposal, 
    x.Jml_Peserta,
    --x.Status, 
    --x.Nama_Perusahaan,
    --x.No_Ref, 
    --x.No_Note, 
    --x.Tgl_Buat, 
    --x.Total, 
    --x.Jml_Lunas,
    --x.Saldo, 
    --x.Tgl_Mulai, 
    --x.Tgl_Selesai, 
    --x.Tgl_Lunas, 
    --x.Change_By, 
    --x.Change_Date, 
    --x.Jenis, 
    --x.Tgl_Terima, 
    --x.Nama_Payor, 
    --x.Id_Payor, 
    --x.GT_OG,
    SUM(x.AJB) AS AJB,
    SUM(x.ACC) AS ACC,
    SUM(x.RI) AS RI,
    SUM(x.RJ) AS RJ,
    SUM(x.Gigi) AS Gigi,
    SUM(x.Lahir) AS Lahir,
    SUM(x.Kacamata) AS Kacamata,
    SUM(x.Total) AS Total
FROM (
      SELECT TOP 100 PERCENT
        dbo.Note_Jenis.Description, 
        dbo.Polis_Asuransi.Marketing,
        dbo.Note_Terbit_Master.No_Polis, 
        dbo.Note_Terbit_Master.No_Proposal, 
        dbo.Note_Terbit_Master.Jml_Peserta,
        --dbo.Note_Terbit_Master.Status, 
        --dbo.Perusahaan.Nama_Perusahaan,
        --dbo.Note_Terbit_Master.No_Ref, 
        --dbo.Note_Terbit_Master.No_Note, 
        --dbo.Note_Terbit_Master.Tgl_Buat, 
        --dbo.Note_Terbit_Master.Total, 
        --dbo.Note_Terbit_Master.Jml_Lunas,
        --dbo.Note_Terbit_Master.Saldo, 
        --dbo.Polis_Asuransi.Tgl_Mulai, 
        --dbo.Polis_Asuransi.Tgl_Selesai, 
        --dbo.Note_Terbit_Master.Tgl_Lunas, 
        --dbo.Note_Terbit_Master.Change_By, 
        --dbo.Note_Terbit_Master.Change_Date, 
        --dbo.Note_Terbit_Master.Jenis, 
        --dbo.Note_Shipping.Tgl_Terima, 
        --dbo.Payor_Header.Nama_Payor, 
        --dbo.Polis_Asuransi.Id_Payor, 
        --dbo.Note_Terbit_Master.GT_OG,
        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
          WHEN '0100' THEN dbo.Note_Terbit_Detail.Total
          ELSE 0
        END AS 'AJB',
        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
          WHEN '0200' THEN dbo.Note_Terbit_Detail.Total
          ELSE 0
        END AS 'ACC',
        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
          WHEN '0302' THEN dbo.Note_Terbit_Detail.Total
          ELSE 0
        END AS 'RI',
        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
          WHEN '0301' THEN dbo.Note_Terbit_Detail.Total
          ELSE 0
        END AS 'RJ',
        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
          WHEN '0303' THEN dbo.Note_Terbit_Detail.Total
          ELSE 0
        END AS 'Gigi',
        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
          WHEN '0304' THEN dbo.Note_Terbit_Detail.Total
          ELSE 0
        END AS 'Lahir',
        CASE dbo.Note_Terbit_Detail.Sub_Kelompok
          WHEN '0305' THEN dbo.Note_Terbit_Detail.Total
          ELSE 0
        END AS 'Kacamata',
        dbo.Note_Terbit_Detail.Total
      FROM 
        dbo.Payor_Header INNER JOIN
        dbo.Perusahaan INNER JOIN
        dbo.Polis_Asuransi ON dbo.Perusahaan.No_Prs = dbo.Polis_Asuransi.No_Prs ON 
        dbo.Payor_Header.Id_Payor = dbo.Polis_Asuransi.Id_Payor RIGHT OUTER JOIN
        dbo.Note_Jenis INNER JOIN
        dbo.Note_Terbit_Master ON dbo.Note_Jenis.Kode = dbo.Note_Terbit_Master.Jenis LEFT OUTER JOIN
        dbo.Note_Shipping ON dbo.Note_Terbit_Master.No_Ref = dbo.Note_Shipping.No_Ref ON 
        dbo.Polis_Asuransi.No_Proposal = dbo.Note_Terbit_Master.No_Proposal INNER JOIN
        dbo.Note_Terbit_Detail ON dbo.Note_Terbit_Master.No_Ref=dbo.Note_Terbit_Detail.No_Ref
      WHERE 
        (dbo.Note_Terbit_Master.Status = 'I') 
        --AND (dbo.Note_Terbit_Master.Jenis = 11) 
        AND (dbo.Note_Terbit_Master.GT_OG = 'OG') 
        AND (dbo.Note_Terbit_Master.Tgl_Buat >= '2014-04-01')
        AND (dbo.Note_Terbit_Master.Tgl_Buat <= '2014-04-30')
      ORDER BY 
        dbo.Perusahaan.Nama_Perusahaan, 
        dbo.Note_Terbit_Master.Tgl_Buat DESC
     ) x
GROUP BY
    x.Description,
    x.Marketing, 
    x.No_Polis, 
    x.No_Proposal,
    x.Jml_Peserta--, 
    --x.Status, 
    --x.Nama_Perusahaan,
    --x.No_Ref, 
    --x.No_Note, 
    --x.Tgl_Buat, 
    --x.Total, 
    --x.Jml_Lunas,
    --x.Saldo, 
    --x.Tgl_Mulai, 
    --x.Tgl_Selesai, 
    --x.Tgl_Lunas, 
    --x.Change_By, 
    --x.Change_Date, 
    --x.Jenis, 
    --x.Tgl_Terima, 
    --x.Nama_Payor, 
    --x.Id_Payor, 
    --x.GT_OG