SQL Case Statement_Group dates by month! 1

[SpeedThink]

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 contracts (CID) with all of the individual discharge dates 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,
CID 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 or the "case" method?

For example, I am comtemplating something involving the following decode statement as shown below:

Select *
from (select distinct account_id,
count(decode (discharge_date, '22-Apr-02', account_no)) April 2002,
count(decode (discharge_date, '23-Apr-02', account_no)) April 2002,
count(decode (discharge_date, '24-Apr-02', account_no)) April 2002,
count(decode (discharge_date, '25-Apr-02', account_no)) April 2002 ...
from patient_encounter
where account_id in ('X24','X25')
Group by account_id, month)
order by 1;


Using Case Statements:

Select contract_id, CASE WHEN discharge_date between '22-Apr-02' and '30-Apr-02' THEN 'April 2002'
.
.
.


Results should be like this:

CID April 2002 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

Any insight?

Thanks in advance.

 

[Bastien]

have you tried to group by months with the mysql DATE_FORMAT functions?

Honestly, if you are receiving the correct data from the query, wht not let the application level code handle the presentation?

Select account_id, count(account_id) as total, date_format('%M %Y', discharge_date) as month
from 
    from patient_encounter
    where account_id in ('X24','X25')
    Group by account_id, month
    order by account_id

which would yield results like this:

CID total month
X24 20,000 April 2002
X24 300,000 May 2002
X24 250,000 June 2002
X25 15,000 April 2002
X25 200,000 May 2002
X25 500,000 June 2002
...

then a simple loop in the application can take care of the arrangement for viewing the data...

what format is the report in? Excel, web page (html)?

Bastien

Cat, the other other white meat

 

[SpeedThink]

This does not seem to work.

For some reason, SQLPlus does not allow me to group by "month."

Therefore, I am able to get the following query to display the discharge_date as Month-Year, i.e. Jan-2002.

select ep.cid, count(pe.encounter_no) as total,
to_char(pe.discharge_date,'Mon-YYYY') as discharge_date
from patient_encounter pe, encounter_payor ep
where cid = '&CID'
and pe.discharge_date>'22-APR-2004'
and pe.encounter_no=ep.encounter_no
group by ep.cid, discharge_date
order by cid

However, I receive a separate row for each encounter that has a discharge date within a particular month. For example, if there are several encounters with the following discharge dates - January 2, 2002, January 3, 2002, or January 4, 2002, the result of the query will output three records with the same discharge date of "Jan 2002" instead of providing an aggregate total for all encounters during January 2002 in one record.

 

[Bastien]

One: this a MySQL forum, not Oracle

Two: group by ep.cid, to_date( discharge_date,'MON')



Bastien

Cat, the other other white meat