Dear All,
thx banyak buat masukan yang teman di berikan, saya udah coba2 script2 itu
dan berhasil semua.
begiru juga yang di berikan oleh saudara Achmad.
teapi kendalanya system sangat berat waktu running query tersebut. karena sum
itu sudah saya Group by
supaya teman2 gak bingung, saya coba berikan contoh query yang saya pake.
select a.coverage_id,a.claims_count,(a.claims_count/b.claims_count) hasil,
a.CHARGE
from
(SELECT coverage_id "COVERAGE_ID", SUM (claims_count) "CLAIMS_COUNT",
AVG(CHARGE) "CHARGE"
FROM (SELECT coverage_id, COUNT (nvl(claims_id,0)) "CLAIMS_COUNT",
SUM (nvl(due_total,0)) "CHARGE",
SUM (nvl(paid_to_claimant,0)) "BENEFIT_PAID"
FROM claims
WHERE payor_code = 'EKA'
AND corporate_code = 'SINDO01'
AND CLAIMS_STATUS IN('40','41','42')
GROUP BY coverage_id)
GROUP BY coverage_id) a,
(SELECT COUNT (nvl(claims_id,0)) "CLAIMS_COUNT"
FROM claims
WHERE payor_code = 'EKA'
AND corporate_code = 'SINDO01'
AND CLAIMS_STATUS IN('40','41','42')
GROUP BY corporate_code)b
dan hasilnya akan seperti ini...
coverage_id claims_count hasil
charge
GP 514 0.988461538461538
102239571
H&S 6 0.0115384615384615
14759608
dimana 0.988461538461538 = claims_count(514 + 6) / 514
dan0.0115384615384615 = claims_count(514 + 6) / 6
dan begitu seterusnya.
atas bantuan teman2 selama ini saya ucapkan banyak terima kasih.
thanks
Regards,
Krishna
________________________________
From: Tedi Rivan <[email protected]>
To: [email protected]
Sent: Sunday, June 14, 2009 12:18:53 AM
Subject: [indo-oracle] Re: Mohon bantuan masalah query...
dear krishna,
--nilai average dalam bentuk 1 baris
select sum(:data1) /
(sum(:data1) + sum(:data2) + sum(:data3) + sum(:data4)) avg1 ,
sum(:data2) /
(sum(:data1) + sum(:data2) + sum(:data3) + sum(:data4)) avg2,
sum(:data3) /
(sum(:data1) + sum(:data2) + sum(:data3) + sum(:data4)) avg3,
sum(:data4) /
(sum(:data1) + sum(:data2) + sum(:data3) + sum(:data4)) avg4
from dual -- table A
atau mungkin maksudnya dari keempat nilai tersebut adalah nilai dari hasil
group by data, sy contohkan spt ini :
pertama2 anda coba bikin function get_summary_ all dari sum(:data_all)
[data1+data2+ data3+data4]
select 'data group' desc_data, sum(:data_nilai_ 1_sd_4)/: get_summary_ all
average
from dual -- table A
group by 'data group';
mudah2an bisa membantu...
- tedirivan -
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
