--SELECT Curr_Santunan FROM Peserta_Produk
--WHERE No_Proposal=''

-- jagaraga 2012.0311
-- semangat 2012.0309

-- Jumlah Peserta Semangat
SELECT COUNT(*) AS 'Jumlah Peserta Semangat' FROM Peserta WHERE No_Proposal='2012.0309'
-- Jumlah Peserta Jagaraga
SELECT COUNT(*) AS 'Jumlah Peserta Jagaraga' FROM Peserta WHERE No_Proposal='2012.0311'

-- Kasus Semangat
SELECT COUNT(*) AS 'Jumlah Kasus Semangat' FROM Medical_Record WHERE No_Proposal='2012.0309'
-- Kasus Jagaraga
SELECT COUNT(*) AS 'Jumlah Kasus Jagaraga' FROM Medical_Record WHERE No_Proposal='2012.0311'
SELECT * FROM Medical_Record WHERE No_Proposal='2012.0311'

-- Kasus Semangat Employee
SELECT COUNT(*) AS 'Jumlah Kasus Semangat Employee' FROM Medical_Record WHERE RIGHT(No_Peserta, 2)='-0' AND No_Proposal='2012.0309'
-- Kasus Semangat Dependent
SELECT COUNT(*) AS 'Jumlah Kasus Semangat Dependent' FROM Medical_Record WHERE RIGHT(No_Peserta, 2) IN ('-1','-2','-3','-4') AND No_Proposal='2012.0309'

-- Kasus Semangat OutPatient Employee
SELECT COUNT(*) AS 'Jumlah Kasus Semangat OutPatient Employee' FROM Medical_Record WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(No_Peserta, 2)='-0' AND No_Proposal='2012.0309'
-- Kasus Semangat OutPatient Dependent
SELECT COUNT(*) AS 'Jumlah Kasus Semangat OutPatient Dependent' FROM Medical_Record WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(No_Peserta, 2) IN ('-1','-2','-3','-4') AND No_Proposal='2012.0309'
-- Kasus Semangat InPatient Employee
SELECT COUNT(*) AS 'Jumlah Kasus Semangat InPatient Employee' FROM Medical_Record WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(No_Peserta, 2)='-0' AND No_Proposal='2012.0309'
-- Kasus Semangat InPatient Dependent
SELECT COUNT(*) AS 'Jumlah Kasus Semangat InPatient Dependent' FROM Medical_Record WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(No_Peserta, 2) IN ('-1','-2','-3','-4') AND No_Proposal='2012.0309'

-- Kasus Jagaraga OutPatient Employee
SELECT COUNT(*) AS 'Jumlah Kasus Jagaraga OutPatient Employee' FROM Medical_Record WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(No_Peserta, 2)='-0' AND No_Proposal='2012.0311'
-- Kasus Jagaraga OutPatient Dependent
SELECT COUNT(*) AS 'Jumlah Kasus Jagaraga OutPatient Dependent' FROM Medical_Record WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(No_Peserta, 2) IN ('-1','-2','-3','-4') AND No_Proposal='2012.0311'
-- Kasus Jagaraga InPatient Employee
SELECT COUNT(*) AS 'Jumlah Kasus Jagaraga InPatient Employee' FROM Medical_Record WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(No_Peserta, 2)='-0' AND No_Proposal='2012.0311'
-- Kasus Jagaraga InPatient Dependent
SELECT COUNT(*) AS 'Jumlah Kasus Jagaraga InPatient Dependent' FROM Medical_Record WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(No_Peserta, 2) IN ('-1','-2','-3','-4') AND No_Proposal='2012.0311'


-- Gender distribution among workers (khusus employee) - Semangat OutPatient
SELECT COUNT(*) AS 'Gender distribution among workers Semangat OP', Peserta.Kelamin 
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(Medical_Record.No_Peserta, 2)='-0' AND Medical_Record.No_Proposal='2012.0309' AND Peserta.No_Proposal='2012.0309'
GROUP BY Peserta.Kelamin 

-- Gender distribution among workers (khusus employee) - Semangat InPatient
SELECT COUNT(*) AS 'Gender distribution among workers Semangat IP', Peserta.Kelamin 
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(Medical_Record.No_Peserta, 2)='-0' AND Medical_Record.No_Proposal='2012.0309' AND Peserta.No_Proposal='2012.0309'
GROUP BY Peserta.Kelamin 

-- Gender distribution among workers (khusus employee) - Jagaraga OutPatient
SELECT COUNT(*) AS 'Gender distribution among workers Jagaraga OP', Peserta.Kelamin 
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(Medical_Record.No_Peserta, 2)='-0' AND Medical_Record.No_Proposal='2012.0311' AND Peserta.No_Proposal='2012.0311'
GROUP BY Peserta.Kelamin 

-- Gender distribution among workers (khusus employee) - Jagaraga InPatient
SELECT COUNT(*) AS 'Gender distribution among workers Jagaraga IP', Peserta.Kelamin 
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(Medical_Record.No_Peserta, 2)='-0' AND Medical_Record.No_Proposal='2012.0311' AND Peserta.No_Proposal='2012.0311'
GROUP BY Peserta.Kelamin 

-- ini buat cross-check data gender
-- Kasus Semangat Employee
SELECT COUNT(*) AS 'Jumlah Kasus Semangat Employee' FROM Medical_Record WHERE RIGHT(No_Peserta, 2)='-0' AND No_Proposal='2012.0309'
-- Kasus Jagaraga Employee
SELECT COUNT(*) AS 'Jumlah Kasus Jagaraga Employee' FROM Medical_Record WHERE RIGHT(No_Peserta, 2)='-0' AND No_Proposal='2012.0311'

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

-- Gender distribution (khusus dependent) - Semangat OutPatient - istri/suami
SELECT COUNT(*) AS 'Gender distribution Semangat OP dependent istri/suami', Peserta.Kelamin 
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(Medical_Record.No_Peserta, 2)='-1' AND Medical_Record.No_Proposal='2012.0309' AND Peserta.No_Proposal='2012.0309'
GROUP BY Peserta.Kelamin 

-- Gender distribution (khusus dependent) - Semangat OutPatient - anak
SELECT COUNT(*) AS 'Gender distribution Semangat OP dependent anak'
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(Medical_Record.No_Peserta, 2) IN ('-2','-3','-4') AND Medical_Record.No_Proposal='2012.0309' AND Peserta.No_Proposal='2012.0309'

-- Gender distribution (khusus dependent) - Semangat InPatient - istri/suami
SELECT COUNT(*) AS 'Gender distribution Semangat IP dependent istri/suami', Peserta.Kelamin 
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(Medical_Record.No_Peserta, 2)='-1' AND Medical_Record.No_Proposal='2012.0309' AND Peserta.No_Proposal='2012.0309'
GROUP BY Peserta.Kelamin 

-- Gender distribution (khusus dependent) - Semangat InPatient - anak
SELECT COUNT(*) AS 'Gender distribution Semangat IP dependent anak'
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(Medical_Record.No_Peserta, 2) IN ('-2','-3','-4') AND Medical_Record.No_Proposal='2012.0309' AND Peserta.No_Proposal='2012.0309'

-- Gender distribution (khusus dependent) - Jagaraga OutPatient - istri/suami
SELECT COUNT(*) AS 'Gender distribution Jagaraga OP dependent istri/suami', Peserta.Kelamin 
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(Medical_Record.No_Peserta, 2)='-1' AND Medical_Record.No_Proposal='2012.0311' AND Peserta.No_Proposal='2012.0311'
GROUP BY Peserta.Kelamin 

-- Gender distribution (khusus dependent) - Jagaraga OutPatient - anak
SELECT COUNT(*) AS 'Gender distribution Jagaraga OP dependent anak'
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0301','0303') AND RIGHT(Medical_Record.No_Peserta, 2) IN ('-2','-3','-4') AND Medical_Record.No_Proposal='2012.0311' AND Peserta.No_Proposal='2012.0311'

-- Gender distribution (khusus dependent) - Jagaraga InPatient - istri/suami
SELECT COUNT(*) AS 'Gender distribution Jagaraga IP dependent istri/suami', Peserta.Kelamin 
FROM Medical_Record INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(Medical_Record.No_Peserta, 2)='-1' AND Medical_Record.No_Proposal='2012.0311' AND Peserta.No_Proposal='2012.0311'
GROUP BY Peserta.Kelamin 

-- Gender distribution (khusus dependent) - Jagaraga InPatient - anak
SELECT COUNT(*) AS 'Gender distribution Jagaraga IP dependent anak'
FROM Medical_Record  INNER JOIN
Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE Sub_Kelompok IN ('0302','0304') AND RIGHT(Medical_Record.No_Peserta, 2) IN ('-2','-3','-4') AND Medical_Record.No_Proposal='2012.0311' AND Peserta.No_Proposal='2012.0311'

-- ini buat cross-check data gender
-- Kasus Semangat dependent
SELECT COUNT(*) AS 'Jumlah Kasus Semangat dependent' 
FROM Medical_Record INNER JOIN
  Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta
WHERE RIGHT(Medical_Record.No_Peserta, 2) IN ('-1','-2','-3','-4') AND Medical_Record.No_Proposal='2012.0309' AND Peserta.No_Proposal='2012.0309'
-- Kasus Jagaraga dependent
SELECT COUNT(*) AS 'Jumlah Kasus Jagaraga dependent' 
FROM Medical_Record INNER JOIN
  Peserta ON Peserta.No_Peserta=Medical_Record.No_Peserta 
WHERE RIGHT(Medical_Record.No_Peserta, 2) IN ('-1','-2','-3','-4') AND Medical_Record.No_Proposal='2012.0311' AND Peserta.No_Proposal='2012.0311'
