Solved

How do I create a simple formatted report?

Posted on 2007-11-29
44
821 Views
Last Modified: 2016-08-29
I do not completely understand how to create a report.  Is it a completely seperate file?  What starts the report?  


I am in the process of creating a formatted report.  I get the following errors:

SP2-0158: unknown SET option "newpage"
SP2-0852: Option not available in iSQL*Plus
SP2-0850: Command "spool" is not available in iSQL*Plus
SP2-0850: Command "spool" is not available in iSQL*Plus


The following will go after spool billingSummary.lst:

select ChargeCatagory, Cost
from
order by





rem Patient Billing Summary
 

set headsep!
 

ttitle     'Patient Billing Summary'

btitle     'from HADA database'
 

column ChargeCatagory format a20

column Cost format a20 word_wrapped
 

break on ChargeCatagory skip 1 on report
 

set linesize 80

set pagesize 60

set newpage 0

set feedback off
 

spool billingSummary.lst
 

spool off

Open in new window

0
Comment
Question by:CharleneS77
  • 22
  • 16
  • 4
  • +2
44 Comments
 

Author Comment

by:CharleneS77
ID: 20379334
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20379471
try the same thing in SQL*plus not in iSQL*Plus

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20379491
may be they are not supported in isql*plus.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20379534
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/apd.htm

This url gives the list of commands which are not supported in isql*plus.

set newpage is one among them and hence  not supported in isql*plus.

Try commenting out that line in your script and run to see the output as to how it looks without newpage.

Thanks
0
 

Author Comment

by:CharleneS77
ID: 20379701
When I ran my SQL in iSQLPlus, I was not getting any errors.

Now that I have run it in SQLPlus, I get the errors.  You can see my SQL code and the errors at this link:  
https://filedb.experts-exchange.com/incoming/ee-stuff/5899-PatientBillingSummary.txthttps://filedb.experts-exchange.com/incoming/ee-stuff/5900-HADS.txt
https://filedb.experts-exchange.com/incoming/ee-stuff/5901-SQLPlus-Errrors.txt
.


What is the difference?  What needs to be changed?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 20379791
>> references PATIENT_ADMISSION (Patient_ID)
THis line is giving the errors.

You should have a primary key or Unique key defined on PATIENT_ADMISSION (Patient_ID). This will solve all the errors.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20379991
without a primary key or unique key you cannot create a referential integrity relationship.

You can do it with the below command :

alter table PATIENT_ADMISSION constraint pk_patient_id_cons primary key ( patient_id );

Thanks
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 20382653
To answer your original question: yes, either SQL*Plus or iSQL*Plus can be used to produce simple formatted reports.  But, this is not the way that most organizations produce reports from Oracle.  Usually a report-writing tool, like: Oracle Reports, Oracle BI Publisher, Crystal Reports, etc. is used for producing regular reports.

SQL*Plus and iSQL*Plus are a programmers command-line interface to Oracle, and are very useful for DBA tasks,and some programming activity but they are not ideal for producing reports.
0
 

Author Comment

by:CharleneS77
ID: 20389760
I am making some progress.  I'm trying to figure out how to format the top of the report as follows:
Patient Name:                                     Patient ID:
Admission Date:                                 Discharge Date:


rem Patient Billing Summary
 

set headsep!
 

ttitle     'Patient Billing Summary'

btitle     'from HADA database'
 

column PatientName heading 'Patient Name:'

column PatientID format 'Patient ID:'

column AdmissionDate 'Admission Date:'

column DischargeDate 'Discharge Date:'
 

column PatientName format a30

column PatientID format a6

column AdmissionDate format a12

column DischargeDate format a12
 

column ChargeCatagory format a20

column Cost format $999.99 

	

break on ChargeCatagory skip 1 on report
 

set linesize 80

set pagesize 60

set newpage 0

set feedback off
 

spool billingSummary.lst
 

select FName || ' ' || MI || ' '  || LName as Patient_Name

from PATIENT_DEMOGRAPHIC;
 

spool off

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20390348
can you tell me how your output should look like with sample data ?

Just open the url and search for "COLUMN MANAGER_ID NEW_VALUE MGRVAR NOPRINT" without double quotes.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm#sthref1134

may be you want something like this.

Thanks
0
 

Author Comment

by:CharleneS77
ID: 20393105
                                                 Patient Billing Summary

Patient Name:  John K Smith                  Patient ID:  111111  
Admission Date:  04/02/2007                  Discharge Date:  04/09/2007

      Charge Category                      Cost
Facility Summary Charges:            $6,000.00
Medicine Summary Charges:            $350.00
Clinical Test Summary Charges:                           $600.00
Radiology Test Summary Charges:                           $0.00
Pathology Summary Charges:            $0.00

            Total Charges:       $6950.00
0
 

Author Comment

by:CharleneS77
ID: 20393112
Sorry about the formatting of the cost column.  

rem Patient Billing Summary

set headsep!

ttitle     'Patient Billing Summary'
btitle     'from HADS database'

column FName NOPRINT NEW_VALUE First_Name
column MI NOPRINT NEW_VALUE Middle_Initial
column LName NOPRINT NEW_VALUE Last_Name
column Patient_ID NOPRINT NEW_VALUE ID
column Admission_Date NOPRINT NEW_VALUE Admitted
column Discharge_Date NOPRINT NEW_VALUE Discharged

break on Charge_Category skip 1 on report
compute sum of Charge_Category on ID
compute sum of Charge_Category on report

LEFT 'Patient Name: ' First_Name || ' ' || Middle_Initial || ' ' ||
Last_Name-
RIGHT 'Patient ID: ' ID-
LEFT 'Admission Date: ' Admitted-
RIGHT 'Discharge Date: ' Discharged skip 2
0
 

Author Comment

by:CharleneS77
ID: 20393373
I'm still having a hard time with this.  Any suggestions are appreciated.
REM Patient Billing Summary
 

SET ECHO ON

SET HEADSEP!
 

COLUMN LName NOPRINT NEW_VALUE LastName

COLUMN Patient_ID NOPRINT NEW_VALUE ID
 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Last Name: ' LastName -

RIGHT 'Patient ID: ' ID skip 2

BREAK ON Patient_ID skip page

BTITLE 'from HADS database'
 

SELECT Patient_ID

FROM PATIENT_ADMISSION

WHERE PATIENT_ID IN (111111)

ORDER BY Patient_ID;
 

SPOOL patientSummary.lst
 

SELECT Patient_ID

FROM PATIENT_ADMISSION

WHERE Patient_ID IN (111111)

ORDER BY Patient_ID;
 

SPOOL OFF

Open in new window

0
 

Author Comment

by:CharleneS77
ID: 20393518
I am not able to get the correct output for my report.

I need my output to be formatted as follows:

                               Patient Billing Summary

Patient Name:  John K Smith                  Patient ID:  111111  
Admission Date:  04/02/2007                  Discharge Date:  04/09/2007

      Charge Category                                            Cost
Facility Summary Charges:                                  $6,000.00
Medicine Summary Charges:                               $350.00
Clinical Test Summary Charges:                          $600.00
Radiology Test Summary Charges:                      $0.00
Pathology Summary Charges:                              $0.00

            Total Charges:       $6950.00


Here is what I have for my report so far:

REM Patient Billing Summary

SET HEADSEP!

COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate

TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3

BREAK ON Patient_ID skip 2
BTITLE 'from HADS database'

SPOOL patientSummary.lst

select FName || ' ' || MI || ' '  || LName as Patient_Name, Patient_ID
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO;

select Adm_Date, Disc_Date
from PATIENT_LOCATION;

SPOOL OFF

create table PATIENT_DEMOGRAPHIC(

SSNO      		NUMBER NOT NULL,

LName		VARCHAR2(25) NOT NULL,     

MI                   	VARCHAR2(3),

FName           	VARCHAR2(25),

Street               	VARCHAR2(35),

City                 	VARCHAR2(25),

State                	VARCHAR2(2),

Zip                   	NUMBER,

DOB                	DATE NOT NULL,

Gender            	VARCHAR2(1) constraint check_Gender CHECK (Gender IN ('M', 'F')) NOT NULL,

Race                	VARCHAR2(3) constraint check_Race CHECK (Race IN ('AFR', 'WHI', 'API', 'HSP', 'OTH')),

constraint PATIENT_DEMOGRAPHIC_PK primary key (SSNO)

);

 

create table PATIENT_ADMISSION(

Patient_ID             	NUMBER NOT NULL,

SSNO  		NUMBER NOT NULL,     

Adm_Date            	TIMESTAMP NOT NULL,

Disc_Date            	DATE,

Adm_Type              VARCHAR2(2) constraint check_Adm_Type CHECK (Adm_Type IN ('IP', 'OP', 'TP', 'RP')),

constraint PATIENT_ADMISSION_PK primary key (Patient_ID),

constraint PATIENT_DEMOGRAPHIC_FK foreign key (SSNO)

references PATIENT_DEMOGRAPHIC (SSNO)

);

 

create sequence SEQ_PATIENT_ADMISSION_PK increment by 1 start with 111111;

 

create or replace trigger PATIENT_ADMISSION_TRIGGER

after insert or update on PATIENT_DEMOGRAPHIC

for each row

begin

      insert into PATIENT_ADMISSION (Patient_ID, SSNO, Adm_Date, Disc_Date, Adm_Type)

      values (SEQ_PATIENT_ADMISSION_PK.NextVal, :new.SSNO, SYSDATE, null, 'OP');

end;

/
 

show errors trigger PATIENT_ADMISSION_TRIGGER;

 

insert into PATIENT_DEMOGRAPHIC 

values ('999999999', 'Smith', 'K', 'John', '111 Baker Street', 'Smilesville', 'CA', '90833', '10-JUL-70', 'M', 'WHI');

 

create table PATIENT_ADMISSION_NEW as

select * from PATIENT_ADMISSION;

update PATIENT_ADMISSION_NEW

set Adm_Type = 'IP';

update PATIENT_ADMISSION pa1

set Adm_Type = (

select Adm_Type

from PATIENT_ADMISSION_NEW pa2

where pa1.patient_id = pa2.patient_id);
 

drop table PATIENT_ADMISSION_NEW;

 

update PATIENT_ADMISSION 

set Adm_Type = 'IP' 

where patient_id = 111111;

 

create table INSURANCE_COMPANY(

Comp_Code        	VARCHAR2(25) NOT NULL,

Comp_Name         	VARCHAR2(25),

Street                   	VARCHAR2(25),

City        		VARCHAR2(25),

State                     VARCHAR2(2),

Zip                     	NUMBER,

Phone                   	NUMBER,

Contact               	VARCHAR2(35),

constraint INSURANCE_COMPANY_PK primary key (Comp_Code)

);

 

create table PATIENT_INSURANCE(

Policy_Num            	VARCHAR2(20) NOT NULL,

Comp_Code           	VARCHAR2(20),

SSNO                    	NUMBER NOT NULL,

LName                 	VARCHAR2(25),

FName                	VARCHAR2(25),

Eff_Date              	DATE,

Exp_Date               	DATE,

Ins_Type         	VARCHAR2(3) constraint check_Ins_Type CHECK (Ins_Type IN ('PPO', 'HMO')),

Primary_Phy        	VARCHAR2(35),

constraint PATIENT_INSURANCE_PK primary key (Policy_Num, SSNO),

constraint INS_COMP_FK foreign key (Comp_Code)

references INSURANCE_COMPANY (Comp_Code),

constraint PATIENT_SSNO_FK foreign key (SSNO)

references PATIENT_DEMOGRAPHIC (SSNO)

);

 

insert into INSURANCE_COMPANY 

values ('JUP', 'Jupiter Insurance Inc', '111 Good Health Ave', 'Smilesville', 'MD', '20888', '3019998888', 'Ms. Patty Snyder');
 

insert into INSURANCE_COMPANY 

values ('GHI', 'Good Health Insurance Inc', '222 Pink Cheeks Drive', 'Healthville', 'NJ', '08956', '2028936784', 'Mr. Bob Simpson');

 

insert into PATIENT_INSURANCE 

values ('A0358F6', 'JUP', '999999999',  'Smith', 'John', '01-JAN-07', '31-DEC-07', 'PPO', 'Dr. Joseph Campbell');

 

create or replace trigger PPO_TRIGGER

before insert or update on PATIENT_INSURANCE

for each row

begin

	if :new.Ins_Type = 'PPO' and :new.Primary_Phy is null 

	then

	RAISE_APPLICATION_ERROR (

	-20001, 'Primary_Phy cannot be null when Ins_Type is PPO');

	end if;

end;

/
 

show errors trigger PPO_TRIGGER;

 

create table WARD_INFORMATION(

Ward_Code       	VARCHAR2(3) constraint check_Ward_Code CHECK (Ward_Code IN ('GWD', 'INC', 'CRD', 'ONC', 'OBG', 'PED')) NOT NULL,

Ward_Name        	VARCHAR2(25),

No_Beds              	NUMBER,

Location              	VARCHAR2(25),

constraint WARD_INFORMATION_PK primary key (Ward_Code)

);

 

create table PATIENT_LOCATION(

Patient_ID        	NUMBER NOT NULL,

Ward_Code            	VARCHAR2(3) NOT NULL,

Bed_Num            	NUMBER,

Adm_Date            	DATE,

Disc_Date            	DATE,

constraint PATIENT_LOCATION_PK primary key (Patient_ID, Ward_Code, Bed_Num, Adm_Date),

constraint PL_PATIENT_ID_FK foreign key (Patient_ID)

references PATIENT_ADMISSION (Patient_ID),

constraint WARD_FK foreign key (Ward_Code)

references WARD_INFORMATION (Ward_Code)

);

 

insert into WARD_INFORMATION 

values ('CRD', 'Cardiology', '30',  'Building 223');
 

insert into WARD_INFORMATION 

values ('ONC', 'Oncology', '20',  'Building 222');

 

insert into PATIENT_LOCATION 

values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'CRD', '3', '02-APR-07', '09-APR-07');

 

create table CHARGE_CODE(

Charge_Code        	VARCHAR2(3) NOT NULL,

Description            	VARCHAR2(30),

Charge_Category  	VARCHAR2(15),

constraint CHARGE_CODE_PK primary key (Charge_Code)

);

 

insert into CHARGE_CODE 

values ('RMS', 'Room Stay', 'Facility');

 

insert into CHARGE_CODE 

values ('SUP', 'Room Supplies', 'Facility');

 

insert into CHARGE_CODE 

values ('PRD', 'Prescription Drugs', 'Medicine');

 

insert into CHARGE_CODE 

values ('BLD', 'Blood Tests', 'Clinical Test');

 

insert into CHARGE_CODE 

values ('URI', 'Urine Tests', 'Clinical Test');

 

insert into CHARGE_CODE 

values ('XRA', 'X-Rays', 'Radiology Test');

 

insert into CHARGE_CODE 

values ('EKG', 'Electro Cardiogram', 'Radiology Test');

 

insert into CHARGE_CODE 

values ('MRI', 'Magnetic Resonance Imaging', 'Radiology Test');

 

insert into CHARGE_CODE 

values ('TIS', 'Tissue Tests', 'Pathology');

 

create table PATIENT_CHARGES(

Patient_ID            	NUMBER NOT NULL,

Charge_Code    	VARCHAR2(3),

Charge_Date        	TIMESTAMP,

Charge_Value       	NUMBER,

constraint PATIENT_CHARGES_PK primary key (Patient_ID, Charge_Code, Charge_Date),

constraint PC_PATIENT_ID_FK foreign key (Patient_ID)

references PATIENT_ADMISSION (Patient_ID),

constraint CHARGE_CODE_FK foreign key (Charge_Code)

references CHARGE_CODE (Charge_Code)

);

 

insert into PATIENT_CHARGES 

values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', '02-APR-07', '750');
 

insert into PATIENT_CHARGES 

values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'PRD', '04-APR-07', '350');
 

insert into PATIENT_CHARGES 

values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'EKG', '08-APR-07', '650');
 

update PATIENT_CHARGES set Charge_Value = ((to_Date('04092007','mmddyyyy') - to_Date(to_char(Charge_Date,'mmddyyyy'),'mmddyyyy')) + 1)  * Charge_Value 

where Charge_Code = 'RMS';
 

create table PATIENT_ADMISSION_NEW as

select * from PATIENT_ADMISSION;

update PATIENT_ADMISSION_NEW

set Disc_Date = SYSDATE;

update PATIENT_ADMISSION pa1

set Disc_Date = (

select Disc_Date

from PATIENT_ADMISSION pa2

where pa1. Disc_Date = pa2. Disc_Date);
 

drop table PATIENT_ADMISSION_NEW;

 

create or replace trigger PADM_DISC_DATE_TRIGGER

before insert or update on PATIENT_ADMISSION

for each row

begin

	if :new.Disc_Date > :new.Adm_Date 

	then

	RAISE_APPLICATION_ERROR (

	-20002, 'Disc_Date cannot come before Adm_Date');

	end if;

end;

/
 

show errors trigger PADM_DISC_DATE_TRIGGER;

 

create or replace trigger PLOC_DISC_DATE_TRIGGER

before insert or update on PATIENT_LOCATION

for each row

begin

	if :new.Disc_Date > :new.Adm_Date 

	then

	RAISE_APPLICATION_ERROR (

	-20003, 'Disc_Date cannot come before Adm_Date');

	end if;

end;

/
 

show errors trigger PLOC_DISC_DATE_TRIGGER;

 

create or replace trigger PATIENT_LOCATION_TRIGGER

after update on PATIENT_ADMISSION

for each row

when (new.Disc_Date is not null)

begin

	update PATIENT_LOCATION

  	set Disc_Date = :new.Disc_Date

  	where Patient_Id = :new.Patient_Id;

end;

/
 

show errors trigger PATIENT_LOCATION_TRIGGER;
 

create table ALL_PATIENTS(

PatientSSNO        	NUMBER NOT NULL,

PatientLName        	VARCHAR2(25) NOT NULL,

PatientMI               	VARCHAR2(3),

PatientFName         	VARCHAR2(25),

PatientStreet   	VARCHAR2(35),

PatientCity              VARCHAR2(25),

PatientState            VARCHAR2(2),

PatientZip           	NUMBER,

PatientDOB             DATE NOT NULL,

PatientGender      	VARCHAR2(1),

PatientRace      	VARCHAR2(3),

PatientAdm_Date  	TIMESTAMP NOT NULL,

constraint ALL_PATIENTS_PK primary key (PatientSSNO, PatientAdm_Date)

);

 

create or replace trigger ALL_PATIENTS_TRIGGER

after insert or update on PATIENT_DEMOGRAPHIC

for each row

begin

	insert into ALL_PATIENTS

      	(PatientSSNO, PatientLName, PatientMI, PatientFName, PatientStreet, PatientCity,

	PatientState, PatientZip, PatientDOB, PatientGender, PatientRace)

	values 

	(:new.SSNO, :new.LName, :new.MI, :new.FName, :new.Street, :new.City,

	:new.State, :new.Zip, :new.DOB, :new.Gender, :new.Race);

end;

/
 

show errors trigger ALL_PATIENTS_TRIGGER;
 

insert into ALL_PATIENTS 

values ('999999999', 'AllLName', 'ALL', 'AllFName', 'AllStreet', 'AllCity',  'AS', '99999', '05-APR-79', 'F', 'OTH', SYSDATE);

 

select * from PATIENT_DEMOGRAPHIC;

select * from PATIENT_ADMISSION;      

select * from INSURANCE_COMPANY;

select * from PATIENT_INSURANCE;

select * from WARD_INFORMATION;

select * from PATIENT_LOCATION;

select * from CHARGE_CODE;

select * from PATIENT_CHARGES;

select * from ALL_PATIENTS;

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20393711
what is the current output you are getting that which is incorrect ?

Also if you need total or aggregate like avg, sum, etc.. then you should also use

put this after your break on ... statement

compute sum of cost on patient_id

Also when you are using break/compute, your queries should have order by. In your case, it should be order by patient_id because that is the one which we have used in break/compute.

Thanks
0
 

Author Comment

by:CharleneS77
ID: 20393776
Here is my most recent output:


                             Patient Billing Summary

Patient Name: PatientName                                 Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


PATIENTNAME
-------------------------------------------------------
John K Smith



                               from HADS database


>>>>Also when you are using break/compute, your queries should have order by.

I don't fully understand how to use Break to compute the totals.  Where do these queries go?


I cannot figure out what I am doing wrong for Patient Name.  
Also, I am not sure how to format the bottom of the report (Charge Categories).

Thank you



REM Patient Billing Summary
 

SET HEADSEP!
 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NOPRINT NEW_VALUE ID

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3
 

BREAK ON Patient_ID skip 2

BTITLE 'from HADS database'
 

COLUMN Charge_Catagory format a30

COLUMN Cost format $9,999.99
 

SPOOL patientSummary.lst
 

select FName || ' ' || MI || ' '  || LName as PatientName,

PATIENT_LOCATION.Patient_ID, PATIENT_LOCATION.Adm_Date,

PATIENT_LOCATION.Disc_Date

from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION;
 

SPOOL OFF

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20393978
Do the below suggested code and the suggestions as well and then paste what output you are getting.
REM Patient Billing Summary

 

SET HEADSEP!

 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NOPRINT NEW_VALUE ID

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate

 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3

 

BREAK ON Patient_ID skip 2

compute sum of cost on patient_id -- but your select query below should select cost also

BTITLE 'from HADS database'

 

COLUMN Charge_Catagory format a30

COLUMN Cost format $9,999.99

 

SPOOL patientSummary.lst

 

select FName || ' ' || MI || ' '  || LName as Name, -- it should be Name not patientname here

PATIENT_LOCATION.Patient_ID, PATIENT_LOCATION.Adm_Date,

PATIENT_LOCATION.Disc_Date, --- add the cost column also here

from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION

where ... -- you have not added the join condition, so add that to link the three tables used

          -- in the from clause

order by patient_id; -- should be used here

 

SPOOL OFF

Open in new window

0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 20395358
Using SQL*Plus to produce formatted reports is possible, but not easy (as you have learned by now) and it introduces security risks.  Most organizations use a reporting tool for this task, and only allow DBAs and developers the permission to use SQL*Plus.
0
 

Author Comment

by:CharleneS77
ID: 20424794
I am close.  I need only use one select statement so it is not printed twice.  How do I get the correct Patient Name, Admission Date, and Discharge?  I am able to get the correct name for the column, but I am not sure how to put this all into one query.  My code is below.  Here is my output:

                             Patient Billing Summary

Patient Name: PatientName                                                        Patient ID:     111111
Admission Date: 02-DEC-07 08.27.42.000000 PM                    Discharge Date:


PATIENTNAME
-------------------------------------------------------
John K Smith



                               from HADS database


                             Patient Billing Summary

Patient Name: PatientName                                                       Patient ID:     111111
Admission Date: 02-DEC-07 08.27.42.000000 PM                    Discharge Date:


CHARGE_CATEGORY                       COST
------------------------------        -----------
Radiology Test                            $650.00
Medicine                                     $350.00
Facility                                     $6,000.00

                               from HADS database



REM Patient Billing Summary
 

SET HEADSEP!
 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NOPRINT NEW_VALUE ID

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3
 

select FName || ' ' || MI || ' '  || LName as PatientName, Patient_ID, Adm_Date, Disc_Date

from PATIENT_DEMOGRAPHIC JOIN PATIENT_ADMISSION

on (PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO)

where Patient_ID = 111111;
 

COLUMN Charge_Category format a30

COLUMN Cost format $99,999.99

BREAK on Patient_ID skip 2

COMPUTE sum of Cost on PatientID
 

select Charge_Category, SUM(Charge_Value) as Cost

from CHARGE_CODE, PATIENT_CHARGES

where CHARGE_CODE.Charge_Code = PATIENT_CHARGES.Charge_Code

group by CHARGE_CODE.Charge_Code, Description, Charge_Category, Patient_ID, 

PATIENT_CHARGES.Charge_Code, Charge_Date, Charge_Value

order by Patient_ID desc;

	

BTITLE 'from HADS database'

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20424910
i have already suggested  the below but why is that not done. can you do it :

select FName || ' ' || MI || ' '  || LName as Name, -- it should be Name not patientname here

Let me now update you that to build complex reports will all the formats you want in sql*plus may not be possible because it is not a reporting tool built for making good reports instead it is used as an application to interact with oracle database but i can try to help you if that is possible.

Just do the above change, and paste your modifed code and the output you are getting and also tell me what output do you need.

Thanks
0
 

Author Comment

by:CharleneS77
ID: 20425000
I have changed PatientName to Name.  The report is running three times because of the three queries.  I am having a hard time combining this into one query.

The output I need is:

Patient Billing Summary

Patient Name:  John K Smith                  Patient ID:  111111  
Admission Date:  04/02/2007                  Discharge Date:  04/09/2007

      Charge Category                                            Cost
Facility Summary Charges:                                  $6,000.00
Medicine Summary Charges:                               $350.00
Clinical Test Summary Charges:                          $600.00
Radiology Test Summary Charges:                      $0.00
Pathology Summary Charges:                              $0.00

            Total Charges:       $6950.00







The output I am currently getting is:

                             Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07








                               from HADS database


                             Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


PATIENTNAME
-------------------------------------------------------
John K Smith



                               from HADS database


                             Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


CHARGE_CATEGORY                       COST
------------------------------       -----------
Radiology Test                            $650.00
Medicine                                     $350.00
Facility                                     $6,000.00

                               from HADS database

REM Patient Billing Summary
 

SET HEADSEP!
 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NOPRINT NEW_VALUE ID

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3
 

COLUMN Charge_Category format a30

COLUMN Cost format $99,999.99

BREAK on Patient_ID skip 2

COMPUTE sum of Cost on PatientID
 

SPOOL patientSummary.lst
 

select FName || ' ' || MI || ' '  || LName as Name, PATIENT_ADMISSION.Patient_ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date

from PATIENT_LOCATION, PATIENT_DEMOGRAPHIC JOIN PATIENT_ADMISSION

on (PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO)

where PATIENT_ADMISSION.Patient_ID = 111111;
 

select FName || ' ' || MI || ' '  || LName as PatientName,

PATIENT_LOCATION.Patient_ID, PATIENT_LOCATION.Adm_Date,

PATIENT_LOCATION.Disc_Date

from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION;
 

select Charge_Category, SUM(Charge_Value) as Cost

from CHARGE_CODE, PATIENT_CHARGES

where CHARGE_CODE.Charge_Code = PATIENT_CHARGES.Charge_Code

group by CHARGE_CODE.Charge_Code, Description, Charge_Category, Patient_ID, 

PATIENT_CHARGES.Charge_Code, Charge_Date, Charge_Value

order by Patient_ID desc;
 

SPOOL OFF

	

BTITLE 'from HADS database'

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20426203
i do not know what to say now.

your second query does not have a where clause to join the three tables. hope you understand that with a join condition the output will be a cartersian join and the output will be a mess.

i think u need to work first on getting a single query which can get the output you want. Then we can play with formatting stuff later.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 20427689
With three separate queries, in SQL*Plus you will get three separate outputs.  If you want just one output, you need to combine the three queries into one.

Trying to use SQL*Plus though to preduce carefully formatted reports is *NOT* what SQL*Plus does best!  SQL*Plus is a command-line programming tool for Oracle. It works well for creating and modifiying tables, and for some data manipulation, but it is *NOT* primarily a report writer!  Yes, it can do simple reports.  But if you want carefully-formatted reports, you need to use a reporting tool, like: Oracle Reports, Oracle BI Publisher, Crystal Reports, etc.
0
 

Author Comment

by:CharleneS77
ID: 20427940
>>>>With three separate queries, in SQL*Plus you will get three separate outputs.  If you want just one output, you need to combine the three queries into one.

Right, the report is running three times because of the three queries.  I am having a hard time combining this into one query.


>>>>Trying to use SQL*Plus though to preduce carefully formatted reports is *NOT* what SQL*Plus does best!  SQL*Plus is a command-line programming tool for Oracle. It works well for creating and modifiying tables, and for some data manipulation, but it is *NOT* primarily a report writer!  Yes, it can do simple reports.  But if you want carefully-formatted reports, you need to use a reporting tool, like: Oracle Reports, Oracle BI Publisher, Crystal Reports, etc.

Thank you.  You have mentioned this multiple times, and I should have responded to let you know that SQL*Plus is what I need to use for this report.


>>>>your second query does not have a where clause to join the three tables. hope you understand that with a join condition the output will be a cartersian join and the output will be a mess.

I do understand that if the joined columns are not specified in the where clause, it will result in a Cartesian product.  All three of these tables do not have the same attributes.  I don't know the proper way to join these tables.


>>>>i think u need to work first on getting a single query which can get the output you want. Then we can play with formatting stuff later.

Exactly, the single query is the part I am having a problem with.  Here is my attempt:

select FName || ' ' || MI || ' '  || LName as Name, PATIENT_ADMISSION.Patient_ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, Charge_Category, SUM(Charge_Value) as Cost
from PATIENT_LOCATION, CHARGE_CODE, PATIENT_CHARGES, PATIENT_DEMOGRAPHIC JOIN PATIENT_ADMISSION
on (PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO)
where PATIENT_ADMISSION.Patient_ID = 111111 and CHARGE_CODE.Charge_Code = PATIENT_CHARGES.Charge_Code
group by CHARGE_CODE.Charge_Code, Description, Charge_Category, PATIENT_ADMISSION.Patient_ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date,
PATIENT_CHARGES.Charge_Code, Charge_Date, Charge_Value
order by PATIENT_ADMISSION.Patient_ID desc;

I get the following error:

select FName || ' ' || MI || ' '  || LName as Name, PATIENT_ADMISSION.Patient_ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, Charge_Category, SUM(Charge_Value) as Cost
                   *
Error at line 1:
ORA-00979:  not a GROUP BY expression


0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 20428079
I'm not familiar with the ANSI-standard join syntax ("join on...") but in standard Oracle query syntax whenever you use a "group" operator, like: "sum" on one or more columns, you *MUST* include every other column or column expression from the "select..." list that does not have a "group" operator in the "group by" clause.  In your case, the concatenated FName and LName value (at least) is not repeated in the "group by", but it must be.

On the issue of why you "need to use" SQL*Plus for this report, whoever is paying for this work needs to be advised that trying to get this job done without buying a reporting tool may end up costing more (at least in time and frustration) than buying a tool that is designed for the job.

For building a house, I could say that the builder "needs to use" a hammer and hand saw only, but the job will not get done nearly as fast as if I allow him to use power tools.
0
 

Author Comment

by:CharleneS77
ID: 20428853
There are no finances involved with this project.  I am working on this in a learning environment, not a real-world environment.  I am thankful for you pointing out the importance of (the reality of) reporting tools.

I am currently working on my query.  The following query produces the right output (without the SUM).

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code;

LNAME  MI   FNAME  PATIENT_ID  CHARGE_VAL  CHARGE_CAT   ADM_DATE       DISC_DATE
Smith     K    John      111111         6000                        Facility        02-APR-07       09-APR-07
Smith     K    John      111111         350                        Medicine        02-APR-07       09-APR-07
Smith     K    John      111111         650                        Radiology Test   02-APR-07       09-APR-07


When I attempt to also SUM(Charge_Value), I get an error.

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID;


select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category,
           *
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
       
ERROR at line 1:
ORA-00937: not a single-group group function


Any suggestions are appreciated.
0
 

Author Comment

by:CharleneS77
ID: 20428886
I'm sorry, you just pointed this out to me.  

>>>>in standard Oracle query syntax whenever you use a "group" operator, like: "sum" on one or more columns, you *MUST* include every other column or column expression from the "select..." list that does not have a "group" operator in the "group by" clause.

I will  correct my error.
0
 

Author Comment

by:CharleneS77
ID: 20429359
I have the query working except for SUM(Charge_Code).  Any suggestions on what I need to change to SUM the Charge_Code?

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

LN       MI   FN    PAT_ID    CHG_VAL  CHG_CAT        ADM_DATE       DISC_DATE       SUM(CHARGE_VALUE)
Smith   K   John 111111    350             Medicine        02-APR-07       09-APR-07       350
Smith   K   John 111111    650             Radiology Test  02-APR-07       09-APR-07       650
Smith   K   John 111111    6000           Facility        02-APR-07       09-APR-07       6000


select SUM(Charge_Value)
from PATIENT_CHARGES, PATIENT_ADMISSION
where PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID;

SUM(CHARGE_VALUE)
7000
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20429389
Remove the Charge_value from the GROUP BY list.

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;
0
 

Author Comment

by:CharleneS77
ID: 20429585
I corrected the Name.

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;
0
 

Author Comment

by:CharleneS77
ID: 20429717
What is causing the TTITLE 'Patient Billing Summary' to print twice?


OUTPUT:
                                        Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


CHARGE_CATEGORY      COST
------------------------------ -----------
Facility                              $6,000.00
Medicine                           $350.00
Radiology Test                 $650.00
                                         -----------
                                         from HADS database

                                         Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


CHARGE_CATEGORY      COST
------------------------------ -----------
                                         $7,000.00



                                           from HADS database



REM Patient Billing Summary
 

SET HEADSEP!
 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NOPRINT NEW_VALUE ID

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3
 

COLUMN Charge_Category format a30

COLUMN Cost format $99,999.99

BREAK on Patient_ID skip 2

COMPUTE sum of Cost on Patient_ID

COMPUTE sum of Cost on report
 

SPOOL patientSummary.lst
 

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category,

PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost

from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE

where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO

and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID

and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code

and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID

group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date,

PATIENT_LOCATION.Disc_Date

order by Patient_ID desc;
 

SPOOL OFF

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20433122
try the attached code :

Also just execute the select query and paste the output ( to see how many records it returns ):

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;


Thanks
REM Patient Billing Summary

 

SET HEADSEP!

set pagesize 100       --- we can set the pagesize to 100 lines ( just to make sure

                      -- that title does not come twice because of page size

 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NOPRINT NEW_VALUE ID

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate

 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3

 

COLUMN Charge_Category format a30

COLUMN Cost format $99,999.99

BREAK on Patient_ID skip 2

COMPUTE sum of Cost on Patient_ID

COMPUTE sum of Cost on report

 

SPOOL patientSummary.lst

 

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category,

PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost

from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE

where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO

and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID

and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code

and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID

group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date,

PATIENT_LOCATION.Disc_Date

order by Patient_ID desc;

 

SPOOL OFF

Open in new window

0
 

Author Comment

by:CharleneS77
ID: 20438476
It looks like that fixed the issue with the report being printed twice:

                             Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


CHARGE_CATEGORY      COST
------------------------------ -----------
Facility                              $6,000.00
Medicine                           $350.00
Radiology Test                  $650.00
                                        -----------
                                         $7,000.00


Now, as for formatting, I would like to add the words "Summary Charges:" to the end of each Charge_Category.  I would also like to add the words Total Charges before the total.  Here is an example of what I would like to do:

                                        Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


CHARGE_CATEGORY                           COST
---------------------------------------------- ---------------
Facility Summary Charges:                    $6,000.00
Medicine Summary Charges:                 $350.00
Radiology Test Summary Charges:       $650.00
                                                              ---------------
                                Total Charges:       $7,000.00
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20438650
what about this ?

Also just execute the select query and paste the output ( to see how many records it returns ):

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

i wanted to know whether this gets 1 record or 2 records which are duplicates. I want to know whether the report is getting duplicated because of data returned by the query or due to other formattting options.

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20438678
these two lines should be changed as shown below :

1)
COMPUTE sum of Cost on Patient_ID
COMPUTE sum of Cost on report

>>>>>

COMPUTE sum LABEL "Total Charges:"  of Cost on Patient_ID
COMPUTE sum LABEL "Total Charges:" of Cost on report

2) query should be changed as below ;

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID,
Charge_Category || ' Summary Charges:'  Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID,
Charge_Category||' Summary Charges:' , PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

Thanks
0
 

Author Comment

by:CharleneS77
ID: 20438857
Everything works except for the "Total Charges: ".

                              Patient Billing Summary
      
Patient Name: John K Smith                      Patient ID:     111111
Admission Date: 02-APR-07                      Discharge Date: 09-APR-07


CHARGE_CATEGORY                               COST
--------------------------------------------- -----------
Facility Summary Charges:                  $6,000.00
Medicine Summary Charges:                  $350.00
Radiology Test Summary Charges:        $650.00
                                                              -----------
                                                             $7,000.00


I removed the pagesize statement to see if Total Charges would print at the bottom of the "second" report.  For some reason, now the report is only running one time, even without the pagesize statement.  So I guess I don't need to set the pagesize.  What needs to changed to have the "Total Charges: " displayed?

Thank you

REM Patient Billing Summary
 

SET HEADSEP!
 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NOPRINT NEW_VALUE ID

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3
 

COLUMN Charge_Category format a40

COLUMN Cost format $99,999.99

BREAK on Patient_ID skip 2

COMPUTE sum LABEL "Total Charges: " of Cost on Patient_ID

COMPUTE sum LABEL "Total Charges: " of Cost on report
 

SPOOL patientSummary.lst
 

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category ||

' Summary Charges: ' Charge_Category,

PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost

from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE

where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO

and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID

and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code

and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID

group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category || ' Summary Charges: ',

PATIENT_LOCATION.Adm_Date,

PATIENT_LOCATION.Disc_Date

order by Patient_ID desc;
 

SPOOL OFF

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20439317
refer to the url on how to define a dummy column to get the output formatting you need :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12014.htm#i2697257

to make you understand, i did the below in my test database :

my table name is : A
it has two numeric columns : a and b

Thanks




break on a on report

column dummy format a14 heading '' justify right

compute sum label 'my sub charges:' of b on a

compute sum label 'total charges:' of b on report

select null dummy, a,b

from a order by a;
 

output:

                        A          B

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

                        4          2

               ********** ----------

               my sub cha          2

                       10          5

                                   5

               ********** ----------

               my sub cha         10

                          ----------

total charges:                    12

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20439540
I am trying to say that if we want to print some label for the computed column on report then we need to have a dummy column something like as shown in my pasted example.

so just modify your code to have the below :

-- i have given a14 because the length of 'total charges:' is 14. you can increase as per your need

1) single quote ' is used instead of double quote "
COMPUTE sum LABEL 'Total Charges: ' of Cost on Patient_ID
COMPUTE sum LABEL 'Total Charges: ' of Cost on report

2) two consecutive single quotes '' not double quote
column dummy format a14 heading '' justify right  

2) select null dummy, FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category ||
' Summary Charges: ' Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category || ' Summary Charges: ',
PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

Thanks

0
 

Author Comment

by:CharleneS77
ID: 20444201
It is adding a dummy column out to the left of the Charge_Category column, and still no 'Total Charges'.


                                     Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


                   CHARGE_CATEGORY                          COST
-------------- ---------------------------------------- ------------
                   Facility Summary Charges:             $6,000.00
                   Medicine Summary Charges:             $350.00
                   Radiology Test Summary Charges:   $650.00
                                                                          ------------
                                                                               $7,000.00



REM Patient Billing Summary
 

SET HEADSEP!
 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NOPRINT NEW_VALUE ID

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3
 

COLUMN Charge_Category format a40

COLUMN Cost format $999,999.99

BREAK on Patient_ID skip 2

COLUMN dummy format a14 heading '' justify right

COMPUTE sum LABEL 'Total Charges: ' of Cost on Patient_ID

COMPUTE sum LABEL 'Total Charges: ' of Cost on report
 

SPOOL patientSummary.lst
 

select null dummy, FName || ' ' || MI || ' ' || LName as Name,

PATIENT_ADMISSION.Patient_ID, Charge_Category ||

' Summary Charges: ' Charge_Category,

PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)

as Cost

from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION,

PATIENT_CHARGES, CHARGE_CODE

where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO

and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID

and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code

and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID

group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category

|| ' Summary Charges: ',

PATIENT_LOCATION.Adm_Date,

PATIENT_LOCATION.Disc_Date

order by Patient_ID desc;
 

SPOOL OFF

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20446505
can you give me the sql script to create all your tables and insert 1 record into it with patient id 1111, so that i can debug in my test database to see what is the issue.

Thanks
0
 

Author Comment

by:CharleneS77
ID: 20446759
Below is the sql that creates all tables in my database.  The patient, John K. Smith is inserted and he has the patient id 111111.

Thank you.
create table PATIENT_DEMOGRAPHIC(

SSNO      		NUMBER NOT NULL,

LName		VARCHAR2(25) NOT NULL,     

MI                   	VARCHAR2(3),

FName           	VARCHAR2(25),

Street               	VARCHAR2(35),

City                 	VARCHAR2(25),

State                	VARCHAR2(2),

Zip                   	NUMBER,

DOB                	DATE NOT NULL,

Gender            	VARCHAR2(1) constraint check_Gender CHECK (Gender IN ('M', 'F')) NOT NULL,

Race                	VARCHAR2(3) constraint check_Race CHECK (Race IN ('AFR', 'WHI', 'API', 'HSP', 'OTH')),

constraint PATIENT_DEMOGRAPHIC_PK primary key (SSNO)

);

 

create table PATIENT_ADMISSION(

Patient_ID             	NUMBER NOT NULL,

SSNO  		NUMBER NOT NULL,     

Adm_Date            	TIMESTAMP NOT NULL,

Disc_Date            	DATE,

Adm_Type              VARCHAR2(2) constraint check_Adm_Type CHECK (Adm_Type IN ('IP', 'OP', 'TP', 'RP')),

constraint PATIENT_ADMISSION_PK primary key (Patient_ID),

constraint PATIENT_DEMOGRAPHIC_FK foreign key (SSNO)

references PATIENT_DEMOGRAPHIC (SSNO)

);

 

create sequence SEQ_PATIENT_ADMISSION_PK increment by 1 start with 111111;

 

create or replace trigger PATIENT_ADMISSION_TRIGGER

after insert or update on PATIENT_DEMOGRAPHIC

for each row

begin

      insert into PATIENT_ADMISSION (Patient_ID, SSNO, Adm_Date, Disc_Date, Adm_Type)

      values (SEQ_PATIENT_ADMISSION_PK.NextVal, :new.SSNO, SYSDATE, null, 'OP');

end;

/

 

show errors trigger PATIENT_ADMISSION_TRIGGER;

 

insert into PATIENT_DEMOGRAPHIC 

values ('999999999', 'Smith', 'K', 'John', '111 Baker Street', 'Smilesville', 'CA', '90833', '10-JUL-70', 'M', 'WHI');

 

create table PATIENT_ADMISSION_NEW as

select * from PATIENT_ADMISSION;

update PATIENT_ADMISSION_NEW

set Adm_Type = 'IP';

update PATIENT_ADMISSION pa1

set Adm_Type = (

select Adm_Type

from PATIENT_ADMISSION_NEW pa2

where pa1.patient_id = pa2.patient_id);

 

drop table PATIENT_ADMISSION_NEW;

 

update PATIENT_ADMISSION 

set Adm_Type = 'IP' 

where patient_id = 111111;

 

create table INSURANCE_COMPANY(

Comp_Code        	VARCHAR2(25) NOT NULL,

Comp_Name         	VARCHAR2(25),

Street                   	VARCHAR2(25),

City        		VARCHAR2(25),

State                     VARCHAR2(2),

Zip                     	NUMBER,

Phone                   	NUMBER,

Contact               	VARCHAR2(35),

constraint INSURANCE_COMPANY_PK primary key (Comp_Code)

);

 

create table PATIENT_INSURANCE(

Policy_Num            	VARCHAR2(20) NOT NULL,

Comp_Code           	VARCHAR2(20),

SSNO                    	NUMBER NOT NULL,

LName                 	VARCHAR2(25),

FName                	VARCHAR2(25),

Eff_Date              	DATE,

Exp_Date               	DATE,

Ins_Type         	VARCHAR2(3) constraint check_Ins_Type CHECK (Ins_Type IN ('PPO', 'HMO')),

Primary_Phy        	VARCHAR2(35),

constraint PATIENT_INSURANCE_PK primary key (Policy_Num, SSNO),

constraint INS_COMP_FK foreign key (Comp_Code)

references INSURANCE_COMPANY (Comp_Code),

constraint PATIENT_SSNO_FK foreign key (SSNO)

references PATIENT_DEMOGRAPHIC (SSNO)

);

 

insert into INSURANCE_COMPANY 

values ('JUP', 'Jupiter Insurance Inc', '111 Good Health Ave', 'Smilesville', 'MD', '20888', '3019998888', 'Ms. Patty Snyder');

 

insert into INSURANCE_COMPANY 

values ('GHI', 'Good Health Insurance Inc', '222 Pink Cheeks Drive', 'Healthville', 'NJ', '08956', '2028936784', 'Mr. Bob Simpson');

 

insert into PATIENT_INSURANCE 

values ('A0358F6', 'JUP', '999999999',  'Smith', 'John', '01-JAN-07', '31-DEC-07', 'PPO', 'Dr. Joseph Campbell');

 

create or replace trigger PPO_TRIGGER

before insert or update on PATIENT_INSURANCE

for each row

begin

	if :new.Ins_Type = 'PPO' and :new.Primary_Phy is null 

	then

	RAISE_APPLICATION_ERROR (

	-20001, 'Primary_Phy cannot be null when Ins_Type is PPO');

	end if;

end;

/

 

show errors trigger PPO_TRIGGER;

 

create table WARD_INFORMATION(

Ward_Code       	VARCHAR2(3) constraint check_Ward_Code CHECK (Ward_Code IN ('GWD', 'INC', 'CRD', 'ONC', 'OBG', 'PED')) NOT NULL,

Ward_Name        	VARCHAR2(25),

No_Beds              	NUMBER,

Location              	VARCHAR2(25),

constraint WARD_INFORMATION_PK primary key (Ward_Code)

);

 

create table PATIENT_LOCATION(

Patient_ID        	NUMBER NOT NULL,

Ward_Code            	VARCHAR2(3) NOT NULL,

Bed_Num            	NUMBER,

Adm_Date            	DATE,

Disc_Date            	DATE,

constraint PATIENT_LOCATION_PK primary key (Patient_ID, Ward_Code, Bed_Num, Adm_Date),

constraint PL_PATIENT_ID_FK foreign key (Patient_ID)

references PATIENT_ADMISSION (Patient_ID),

constraint WARD_FK foreign key (Ward_Code)

references WARD_INFORMATION (Ward_Code)

);

 

insert into WARD_INFORMATION 

values ('CRD', 'Cardiology', '30',  'Building 223');

 

insert into WARD_INFORMATION 

values ('ONC', 'Oncology', '20',  'Building 222');

 

insert into PATIENT_LOCATION 

values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'CRD', '3', '02-APR-07', '09-APR-07');

 

create table CHARGE_CODE(

Charge_Code        	VARCHAR2(3) NOT NULL,

Description            	VARCHAR2(30),

Charge_Category  	VARCHAR2(15),

constraint CHARGE_CODE_PK primary key (Charge_Code)

);

 

insert into CHARGE_CODE 

values ('RMS', 'Room Stay', 'Facility');

 

insert into CHARGE_CODE 

values ('SUP', 'Room Supplies', 'Facility');

 

insert into CHARGE_CODE 

values ('PRD', 'Prescription Drugs', 'Medicine');

 

insert into CHARGE_CODE 

values ('BLD', 'Blood Tests', 'Clinical Test');

 

insert into CHARGE_CODE 

values ('URI', 'Urine Tests', 'Clinical Test');

 

insert into CHARGE_CODE 

values ('XRA', 'X-Rays', 'Radiology Test');

 

insert into CHARGE_CODE 

values ('EKG', 'Electro Cardiogram', 'Radiology Test');

 

insert into CHARGE_CODE 

values ('MRI', 'Magnetic Resonance Imaging', 'Radiology Test');

 

insert into CHARGE_CODE 

values ('TIS', 'Tissue Tests', 'Pathology');

 

create table PATIENT_CHARGES(

Patient_ID            	NUMBER NOT NULL,

Charge_Code    	VARCHAR2(3),

Charge_Date        	TIMESTAMP,

Charge_Value       	NUMBER,

constraint PATIENT_CHARGES_PK primary key (Patient_ID, Charge_Code, Charge_Date),

constraint PC_PATIENT_ID_FK foreign key (Patient_ID)

references PATIENT_ADMISSION (Patient_ID),

constraint CHARGE_CODE_FK foreign key (Charge_Code)

references CHARGE_CODE (Charge_Code)

);

 

insert into PATIENT_CHARGES 

values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', '02-APR-07', '750');

 

insert into PATIENT_CHARGES 

values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'PRD', '04-APR-07', '350');

 

insert into PATIENT_CHARGES 

values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'EKG', '08-APR-07', '650');

 

update PATIENT_CHARGES set Charge_Value = ((to_Date('04092007','mmddyyyy') - to_Date(to_char(Charge_Date,'mmddyyyy'),'mmddyyyy')) + 1)  * Charge_Value 

where Charge_Code = 'RMS';

 

create table PATIENT_ADMISSION_NEW as

select * from PATIENT_ADMISSION;

update PATIENT_ADMISSION_NEW

set Disc_Date = SYSDATE;

update PATIENT_ADMISSION pa1

set Disc_Date = (

select Disc_Date

from PATIENT_ADMISSION pa2

where pa1. Disc_Date = pa2. Disc_Date);

 

drop table PATIENT_ADMISSION_NEW;

 

create or replace trigger PADM_DISC_DATE_TRIGGER

before insert or update on PATIENT_ADMISSION

for each row

begin

	if :new.Disc_Date > :new.Adm_Date 

	then

	RAISE_APPLICATION_ERROR (

	-20002, 'Disc_Date cannot come before Adm_Date');

	end if;

end;

/

 

show errors trigger PADM_DISC_DATE_TRIGGER;

 

create or replace trigger PLOC_DISC_DATE_TRIGGER

before insert or update on PATIENT_LOCATION

for each row

begin

	if :new.Disc_Date > :new.Adm_Date 

	then

	RAISE_APPLICATION_ERROR (

	-20003, 'Disc_Date cannot come before Adm_Date');

	end if;

end;

/

 

show errors trigger PLOC_DISC_DATE_TRIGGER;

 

create or replace trigger PATIENT_LOCATION_TRIGGER

after update on PATIENT_ADMISSION

for each row

when (new.Disc_Date is not null)

begin

	update PATIENT_LOCATION

  	set Disc_Date = :new.Disc_Date

  	where Patient_Id = :new.Patient_Id;

end;

/

 

show errors trigger PATIENT_LOCATION_TRIGGER;

 

create table ALL_PATIENTS(

PatientSSNO        	NUMBER NOT NULL,

PatientLName        	VARCHAR2(25) NOT NULL,

PatientMI               	VARCHAR2(3),

PatientFName         	VARCHAR2(25),

PatientStreet   	VARCHAR2(35),

PatientCity              VARCHAR2(25),

PatientState            VARCHAR2(2),

PatientZip           	NUMBER,

PatientDOB             DATE NOT NULL,

PatientGender      	VARCHAR2(1),

PatientRace      	VARCHAR2(3),

PatientAdm_Date  	TIMESTAMP NOT NULL,

constraint ALL_PATIENTS_PK primary key (PatientSSNO, PatientAdm_Date)

);

 

create or replace trigger ALL_PATIENTS_TRIGGER

after insert or update on PATIENT_DEMOGRAPHIC

for each row

begin

	insert into ALL_PATIENTS

      	(PatientSSNO, PatientLName, PatientMI, PatientFName, PatientStreet, PatientCity,

	PatientState, PatientZip, PatientDOB, PatientGender, PatientRace)

	values 

	(:new.SSNO, :new.LName, :new.MI, :new.FName, :new.Street, :new.City,

	:new.State, :new.Zip, :new.DOB, :new.Gender, :new.Race);

end;

/

 

show errors trigger ALL_PATIENTS_TRIGGER;

 

insert into ALL_PATIENTS 

values ('999999999', 'AllLName', 'ALL', 'AllFName', 'AllStreet', 'AllCity',  'AS', '99999', '05-APR-79', 'F', 'OTH', SYSDATE);

 

select * from PATIENT_DEMOGRAPHIC;

select * from PATIENT_ADMISSION;      

select * from INSURANCE_COMPANY;

select * from PATIENT_INSURANCE;

select * from WARD_INFORMATION;

select * from PATIENT_LOCATION;

select * from CHARGE_CODE;

select * from PATIENT_CHARGES;

select * from ALL_PATIENTS;

Open in new window

0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 20447340
the label which you want to display "Total charges:" is not getting displayed because you have given noprint for the column patient_id.

I tried with some options and i think it is not going to work unless we are going to print the patient_id column.

The output is not exactly the one you wanted but you can try the attached code.

Thanks
REM Patient Billing Summary

 

SET HEADSEP!

 

COLUMN Name NOPRINT NEW_VALUE PatientName

COLUMN Patient_ID NEW_VALUE ID format 999999999999 

COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate

COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate

 

TTITLE CENTER 'Patient Billing Summary' skip 2 -

LEFT 'Patient Name: ' PatientName -

RIGHT 'Patient ID: ' ID skip 1 -

LEFT 'Admission Date: ' AdmissionDate -

RIGHT 'Discharge Date: ' DischargeDate skip 3

 

COLUMN Charge_Category format a40

COLUMN Cost format $999,999.99

BREAK on Patient_ID skip 2

COMPUTE sum LABEL 'Total Charges: ' of Cost on Patient_ID

COMPUTE sum LABEL 'Total Charges: ' of Cost on report

 

SPOOL patientSummary.lst

 

select FName || ' ' || MI || ' ' || LName as Name,

PATIENT_ADMISSION.Patient_ID, Charge_Category ||

' Summary Charges: ' Charge_Category,

PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)

as Cost

from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION,

PATIENT_CHARGES, CHARGE_CODE

where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO

and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID

and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code

and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID

group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category

|| ' Summary Charges: ',

PATIENT_LOCATION.Adm_Date,

PATIENT_LOCATION.Disc_Date

order by Patient_ID desc;

 

SPOOL OFF

Open in new window

0
 

Author Comment

by:CharleneS77
ID: 20447607
That did it:


                                     Patient Billing Summary

Patient Name: John K Smith                                Patient ID:     111111
Admission Date: 02-APR-07                              Discharge Date: 09-APR-07


PATIENT_ID CHARGE_CATEGORY                        COST
------------- ---------------------------------------- ------------
      111111Facility Summary Charges:            $6,000.00
                  Medicine Summary Charges:             $350.00
                  Radiology Test Summary Charges:   $650.00
*************                                                        ------------
Total Charges                                                   $7,000.00



Thank you!
0
 

Author Closing Comment

by:CharleneS77
ID: 31411854
nav_kum_v,

Thank you so much for your time and effort!

Char
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now