SpeedThink
MIS
Background:
I am interested in creating a Matrix Report in SQLPlus that will result in a count of accounts for several
contracts that had a particular diagnosis grouped by month not individual dates within the month from April 22, 2002 through August 11, 2002.
Currently, I am thinking about doing this in two steps;
1st, query the database using a business intelligence application such as Brio to obtain all of the records for
the accounts with all of the discharge dates for all days for each month from April 22, 2002 through August
11, 2004 (over 100,000 records!) and then import into Microsoft Excel and perform a pivot and group by month. Due to the time involved, there must be a more efficient way considering that I need to perform this every week!
For example,
Account_id Account Discharge_date
X24 1234B April 1, 2002
X24 1234C April 2, 2002
X24 1234D April 3, 2002
X25 2464A April 1, 2002
.
.
.
Is there a more efficient method within SQLPlus that would utilize the "decode" method, "case" method or the "group by" clause?
For example, using a "Group By" clause and re-formatting the discharge_date
select ep.account_id, count(pe.encounter_no) as total,
to_char(pe.discharge_date,'Mon-YYYY') as discharge_date
from patient_encounter pe, encounter_payor ep
where contract_id in ('X24','X25','X26',)
and pe.discharge_date>'22-APR-2002'
and pe.encounter_no=ep.encounter_no
group by ep.contract_id, discharge_date
order by contract_id
This SQL script displays the discharge_date as Month-Year. However, it appears that the "group by" clause should be altered to actually group the records by the Month and Year of the account. As the SQL script is currently written, I would receive more than one record if there are accounts with the same contract_id that had a discharge date on different days within a particular month. Specifically, if I had 3 accounts with the same contract_id with the following discharge_dates, I would receive three records:
Contract_id Discharge Date Displayed Discharge_Date
X24 Apr 22, 2002 Apr 2002
X24 Apr 23, 2002 Apr 2002
X24 Apr 24, 2002 Apr 2002
.
.
.
Ideally, Results should be like this:
CID Apr2002 May 2002 June 2002 . . .
X24 20,000 300,000 250,000
X25 15,000 200,000 500,000
X26 28,000 150,000 200,000
Further, the results of the SQL query is used to assist in the input of "From" and "To" dates into an "insert" query. Specifically, I need to insert account numbers into the "Calc_Account" table within the Oracle-based application using SQLPlus. The tricky part is that the "Calc_Account" table within my Oracle-based application is limited to no more than 100,000 accounts at any given point in time.
After the accounts are entered into the "Calc_Account" table, another process titled "Calc" generates an expected reimbursement" for the account and the account number is removed from the "Calc_Account" table.
Then, I have to repeat the procedure over and over until all of the requested accounts, for a particular date period, have an expected reimbursment generated.
Any insight?
Ideal would be another SQL query script that would "provide" me with the "From" and "To" dates to enter into my "insert" query. For example, it would work as follows;
Step 1: I would specify the "threshold" needed, say 75,000 accounts
Step 2: The query result would give me several date ranges, in sequential order, that each contained no more than 75,000 accounts (i.e. Apr 22, 2002 - May 15, 2002; May 16, 2002 - July 28, 2002; ...)
(Of course, the second SQL query would probably be limited to one account at a time...)
I am interested in creating a Matrix Report in SQLPlus that will result in a count of accounts for several
contracts that had a particular diagnosis grouped by month not individual dates within the month from April 22, 2002 through August 11, 2002.
Currently, I am thinking about doing this in two steps;
1st, query the database using a business intelligence application such as Brio to obtain all of the records for
the accounts with all of the discharge dates for all days for each month from April 22, 2002 through August
11, 2004 (over 100,000 records!) and then import into Microsoft Excel and perform a pivot and group by month. Due to the time involved, there must be a more efficient way considering that I need to perform this every week!
For example,
Account_id Account Discharge_date
X24 1234B April 1, 2002
X24 1234C April 2, 2002
X24 1234D April 3, 2002
X25 2464A April 1, 2002
.
.
.
Is there a more efficient method within SQLPlus that would utilize the "decode" method, "case" method or the "group by" clause?
For example, using a "Group By" clause and re-formatting the discharge_date
select ep.account_id, count(pe.encounter_no) as total,
to_char(pe.discharge_date,'Mon-YYYY') as discharge_date
from patient_encounter pe, encounter_payor ep
where contract_id in ('X24','X25','X26',)
and pe.discharge_date>'22-APR-2002'
and pe.encounter_no=ep.encounter_no
group by ep.contract_id, discharge_date
order by contract_id
This SQL script displays the discharge_date as Month-Year. However, it appears that the "group by" clause should be altered to actually group the records by the Month and Year of the account. As the SQL script is currently written, I would receive more than one record if there are accounts with the same contract_id that had a discharge date on different days within a particular month. Specifically, if I had 3 accounts with the same contract_id with the following discharge_dates, I would receive three records:
Contract_id Discharge Date Displayed Discharge_Date
X24 Apr 22, 2002 Apr 2002
X24 Apr 23, 2002 Apr 2002
X24 Apr 24, 2002 Apr 2002
.
.
.
Ideally, Results should be like this:
CID Apr2002 May 2002 June 2002 . . .
X24 20,000 300,000 250,000
X25 15,000 200,000 500,000
X26 28,000 150,000 200,000
Further, the results of the SQL query is used to assist in the input of "From" and "To" dates into an "insert" query. Specifically, I need to insert account numbers into the "Calc_Account" table within the Oracle-based application using SQLPlus. The tricky part is that the "Calc_Account" table within my Oracle-based application is limited to no more than 100,000 accounts at any given point in time.
After the accounts are entered into the "Calc_Account" table, another process titled "Calc" generates an expected reimbursement" for the account and the account number is removed from the "Calc_Account" table.
Then, I have to repeat the procedure over and over until all of the requested accounts, for a particular date period, have an expected reimbursment generated.
Any insight?
Ideal would be another SQL query script that would "provide" me with the "From" and "To" dates to enter into my "insert" query. For example, it would work as follows;
Step 1: I would specify the "threshold" needed, say 75,000 accounts
Step 2: The query result would give me several date ranges, in sequential order, that each contained no more than 75,000 accounts (i.e. Apr 22, 2002 - May 15, 2002; May 16, 2002 - July 28, 2002; ...)
(Of course, the second SQL query would probably be limited to one account at a time...)