Topic: SQL*Plus and PL/SQL >> complex query help
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: complex query help |
| Kalindi |
Posted: Apr 12, 2006 03:17:13 PM |
Total Post: 2
Joined: Apr, 2006
|
spool k:\sql\resolve\report\IP_RESOLVE_RATE.txt;
ACCEPT START_DATE CHAR -
PROMPT 'ENTER STARTDATE (YYYY-MM):'
ACCEPT END_DATE CHAR -
PROMPT 'ENTER ENDDATE (YYYY-MM):'
CREATE OR REPLACE PACKAGE TESTPACK AS
BEGINDATE VARCHAR2(20) := '&START_DATE';
ENDDATE VARCHAR2(20) := '&END_DATE';
END TESTPACK;
/
CREATE TABLE ResolvePayment (
ENCOUNTERID VARCHAR2(12),
RESOLVEPAYMENTS NUMBER(11,2),
PTTYPE VARCHAR2(15),
hcfadrgcode VARCHAR2(4),
INSPLANCODE VARCHAR2(26),
BDIND VARCHAR2(1),
ARBAL NUMBER(11,2),
TOTALACTUALCHARGES NUMBER(11,2),
TOTALACTUALCASHPAYMENTS NUMBER(11,2),
RESOLVERATE NUMBER(6,4),
GlobalRate NUMBER(6,4),
GlobalRateIND CHAR(1));
set heading off
set pagesize 999
set linesize 200
DECLARE
BEGINDATE VARCHAR2(20) := '&START_DATE';
ENDDATE VARCHAR2(20) := '&END_DATE';
cursor resolverate is
select encounter.pttype,encounter.hcfadrgcode, encounter.insplancode, DECODE(Sum(encounter.TOTALACTUALCASHPAYMENTS),0,grr, SUm(encounter.TOTALACTUALCASHPAYMENTS)/sum(encounter.totalactualcharges)) rr, globalresolverate.grr, sum(encounter.TOTALACTUALCASHPAYMENTS)/sum(encounter.totalactualcharges) rate
from encounter, (select hcfadrgcode, sum(TOTALACTUALCASHPAYMENTS)/sum(totalactualcharges) grr
from encounter
where type = 'S'
and to_char(dischargetimestamp, 'YYYY-MM') >= BEGINDATE
and to_char(dischargetimestamp, 'YYYY-MM') <= ENDDATE
and totalactualcharges > 0
and (arbal = 0 or bdind = 'Y')
group by hcfadrgcode) globalresolverate
where encounter.type = 'S'
and to_char(dischargetimestamp, 'YYYY-MM') >= BEGINDATE
and to_char(dischargetimestamp, 'YYYY-MM') <= ENDDATE
and encounter.totalactualcharges > 0
and (encounter.arbal = 0 or encounter.bdind = 'Y')
and encounter.hcfadrgcode = globalresolverate.hcfadrgcode
group by encounter.pttype, encounter.hcfadrgcode, encounter.insplancode, grr;
BEGIN
FOR resolverate_rec in resolverate LOOP
INSERT INTO ResolvePayment( ENCOUNTERID,RESOLVEPAYMENTS,PTTYPE, hcfadrgcode,INSPLANCODE,BDIND,ARBAL,TOTALACTUALCHARGES,TOTALACTUALCASHPAYMENTS,RESOLVERATE,
GLOBALRATE, GlobalRateIND)
SELECT encounterid, (totalactualcharges * resolverate_rec.rr), pttype, hcfadrgcode, insplancode, bdind, arbal, totalactualcharges, TOTALACTUALCASHPAYMENTS, resolverate_rec.rr, resolverate_rec.grr, DECODE(resolverate_rec.rate,0,'Y','N')
from encounter
where type = 'S'
and hcfadrgcode = resolverate_rec.hcfadrgcode
and insplancode = resolverate_rec.insplancode
and to_char(dischargetimestamp, 'YYYY-MM') >= BEGINDATE
and to_char(dischargetimestamp, 'YYYY-MM') <= ENDDATE;
end loop;
END;
/
SELECT ENCOUNTERID, '|', RESOLVEPAYMENTS, '|', GlobalRateIND FROM ResolvePayment;
DROP TABLE ResolvePayment;
spool off;
spool k:\sql\resolve\report\OP_Resolve_Rate.txt;
Alright guys, In above query I would like to add few more things and I’m not sure how I would accomplish it. First, if the department = 7010 and Year(discharge date) = 2006 then I would like to sum(encounter.totalactualcharges) in half. But if the year is 2005 then I would just leave the formula as it is. Also, For all the other departments I don’t want to make any changes. Any input would be highly appreciated.
|
|
|
 Subhranil |
| Posted: Apr 13, 2006 01:50:37 AM |  |
|
Total Post: 28
Joined: Jul, 2005
|
Please send the structure of table encounter
|
|
|
|
|
| Kalindi |
| Posted: Apr 13, 2006 10:04:57 AM | |
|
Total Post: 2
Joined: Apr, 2006
|
Encounter:
Discharge Date (Number)
Discharge Year (String)
Encounter ID (String)
Encounter Type (String)
Total Actual Cash Payments (Number)
Total Actual Charges (Number)
AR Balance (Number)
Bad Debt Indicator (Number)
And department number is in other table:
Charge Detail:
Department (String)
Encounter ID (String)
Let me know if you need more information. Thanks for your help.
|
|
|
|
|
| nirav |
| Posted: Jun 09, 2006 09:00:49 AM | |
|
Total Post: 20
Joined: Jun, 2005
|
Here one way may be to define more than one cursor..for the special condition for dept 7010 and year 2006 , define another cursor and in that cursor calculate half of that total.
make sure that the cursor gets defined properly-i mean that one excludes the other.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 843 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 272 |
| neeraj sharma | 252 |
| Ramesh Jois | 245 |
| Bishal Khetan | 207 |
|
|
