Home » SQL & PL/SQL » SQL & PL/SQL » Query Help
Query Help [message #328293] Thu, 19 June 2008 11:28 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
Hey everyone,

I have the following query that i used a bunch of unions to get different counts. I want to compare all the self pay accounts to all the accounts per site location.

This is very slow due, and since i am pretty much running the same query for both types, is there away to get rid of the unions or make this more efficient?

also, there can be multple charges per each account. thats why i have that query seperate.

Select 
    sSite,
    Category, 
    Sum(Num_of_Accounts) As Num_of_Accounts, 
    Sum(Collection_Accounts) As Collection_Accounts,
    Sum(Num_of_Statements) Num_of_Statements,
    SUM(Total_Charges_Amount) Total_Charges_Amount
from (
--------------------------------
--Self Pay--------------------- 
SELECT 
    ACCOUNT.CPCODE sSite, 
    'Self Pay Patients' Category,
    Count(ACCOUNT.CODE) Num_of_Accounts,
    SUM(CASE WHEN (ACCOUNT.EXCEPTION IN ('CA','CP')) THEN 1 ELSE 0 END) Collection_Accounts,
    sum(ACCOUNT.STATEMENTS) Num_of_Statements,
    0 Total_Charges_Amount
FROM   
    ACCOUNT ACCOUNT
WHERE 
    (ACCOUNT.BILLSTATUS='S')
    AND to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'
GROUP BY
    ACCOUNT.CPCODE
UNION
SELECT 
    ACCOUNT.CPCODE sSite, 
    'Self Pay Patients' Category,
    0 Num_of_Accounts,
    0 Collection_Accounts,
    0 Num_of_Statements,
    SUM(CHARGES.PRAMOUNT) Total_Charges_Amount
FROM   
    ACCOUNT ACCOUNT
    JOIN CHARGES CHARGES ON ((ACCOUNT.CPCODE=CHARGES.CPCODE) AND (ACCOUNT.CODE=CHARGES.ACCOUNT) AND (CHARGES.TYPE='C') AND (CHARGES.SPLITFLAG IS  NULL))
WHERE 
    (ACCOUNT.BILLSTATUS='S')
    AND to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'
GROUP BY
    ACCOUNT.CPCODE


--------------------------------
--ALL Accounts------------------

UNION
SELECT 
    ACCOUNT.CPCODE sSite, 
    'All Patients' Category,
    Count(ACCOUNT.CODE) Num_of_Accounts,
    SUM(CASE WHEN (ACCOUNT.EXCEPTION IN ('CA','CP')) THEN 1 ELSE 0 END) Collection_Accounts,
    sum(ACCOUNT.STATEMENTS) Num_of_Statements,
    0 Total_Charges_Amount
FROM   
    ACCOUNT ACCOUNT
WHERE  to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'
GROUP BY
    ACCOUNT.CPCODE
UNION
SELECT 
    ACCOUNT.CPCODE sSite, 
    'All Patients' Category,
    0 Num_of_Accounts,
    0 Collection_Accounts,
    0 Num_of_Statements,
    SUM(CHARGES.PRAMOUNT) Total_Charges_Amount
FROM   
    ACCOUNT ACCOUNT
    JOIN CHARGES CHARGES ON ((ACCOUNT.CPCODE=CHARGES.CPCODE) AND (ACCOUNT.CODE=CHARGES.ACCOUNT) AND (CHARGES.TYPE='C') AND (CHARGES.SPLITFLAG IS  NULL))
WHERE to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'
GROUP BY
    ACCOUNT.CPCODE
--------------------------------


) U
Group By sSite, Category
Re: Query Help [message #328294 is a reply to message #328293] Thu, 19 June 2008 11:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link. It might be of help to you.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1612

Regards

Raj
Re: Query Help [message #328309 is a reply to message #328294] Thu, 19 June 2008 13:08 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Thanks. I will take a look.
Re: Query Help [message #328344 is a reply to message #328293] Thu, 19 June 2008 16:42 Go to previous messageGo to next message
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
Junior Member
First of all, I have no idea of what type of DBA support/knowledge you have. So some of this may be basic.

The first thing I usually look at is are the table statistics up to date. I would analyze the tables.

Second, I wonder if the columns in the where clause are indexed.

Third and most probably the case is
where to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20070131'

Is logdate a VARCHAR2 column? It should be a date, indexed and never modified in the where clause. Always function the value, not the column. It should be like:
where ACCOUNT.LOGDATE between TO_DATE('20070101', 'YYYYMMDD') AND TO_DATE('20070131', 'YYYYMMDD')

If for what ever bad reason, this is a VARCHAR2 column, you could add a function based index that matches the to_char(to_date... you're doing. Then it'll use the index.

Lastly, on this type of report, I would usually expect to see UNION ALL instead of UNION. If multiple records ever came back the same, only one would be returned.

Re: Query Help [message #328356 is a reply to message #328344] Thu, 19 June 2008 19:19 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
rdebruyn,

Thanks for the reply. We work mostly with SQL server at my work, but this is a thrid part system, that we have access to run queries against.

Logdate is indexed, and is a juliandate field. I will check the type tomarrow when i get back to work.

I will try the date logic you suggest tomarrow. I ran the query this afternoon in SQL plus, and it took just over 2 hours to return.

The charges table has over 50million records, and acounts has 1.4 million for 2007
Re: Query Help [message #328510 is a reply to message #328293] Fri, 20 June 2008 06:37 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
rdebruyn,

The logdate is a number column. Julian date.

What would be the best way to query on this column?
Re: Query Help [message #328516 is a reply to message #328510] Fri, 20 June 2008 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
fmrock wrote on Fri, 20 June 2008 13:37
rdebruyn,

The logdate is a number column. Julian date.

What would be the best way to query on this column?

What kind of query?
What kind of operation do want to do?
What kind of display do want to do?

Regards
Michel

Re: Query Help [message #328518 is a reply to message #328510] Fri, 20 June 2008 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe this will help you:
SQL> select to_char(sysdate,'J') from dual;
TO_CHAR
-------
2454638

1 row selected.

SQL> select to_date(2454638,'J') from dual;
TO_DATE(2454638,'J'
-------------------
20/06/2008 00:00:00

1 row selected.

Regards
Michel
Re: Query Help [message #328530 is a reply to message #328518] Fri, 20 June 2008 07:08 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
The query is above, but I am using this logic for the date.

WHERE to_char(to_date(ACCOUNT.LOGDATE,'j'),'YYYYMMDD') between '20070101' and '20071231'


rdebruyn said its not a good idea to use functions on the column, but since its a number column, not sure if this is ok or not, I am dealing with alot of records, so i would like to make it as effecient as possible.

Thanks again for your help michel and rdebruyn
Re: Query Help [message #328536 is a reply to message #328530] Fri, 20 June 2008 07:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If there is an index on the column, you could ensure it still gets used by writing the query like this:
WHERE ACCOUNT.LOGDATE between to_number(to_char(to_date('20070101','yyyymmdd'),'j')) 
                          and to_number(to_char(to_date('20071231','yyyymmdd'),'j'))
Re: Query Help [message #328548 is a reply to message #328536] Fri, 20 June 2008 07:58 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Thanks,

I saw a huge improvement on running this on the accounts table.

I am running it now with it joined to charges.
Re: Query Help [message #328571 is a reply to message #328548] Fri, 20 June 2008 09:17 Go to previous message
fmrock
Messages: 45
Registered: December 2006
Member
Wow is all i have to say. Thank you guys SO much for you help in this. This will help many of my queries and reports.

I changed the Query to this.

The only thing that is i still need to add to this, is for each site, fin_class, I want to know what percentage the number of accounts per finclass from the total. Is there a way to do this?

Select 
	sSite,
	Fin_Class, 
	Sum(Num_of_Accounts) As Num_of_Accounts, 
	Sum(Collection_Accounts) As Collection_Accounts,
	Sum(Num_of_Statements) Num_of_Statements,
	SUM(Total_Charges_Amount) Total_Charges_Amount,
	GROUPING(sSite) AS gSite,
	GROUPING(Fin_Class) AS gFin_Class
from (
--------------------------------------------------------------
SELECT 
	ACCOUNT.CPCODE sSite, 
	ACCOUNT.BILLSTATUS Fin_Class,
	Count(ACCOUNT.CODE) Num_of_Accounts,
	SUM(CASE WHEN (ACCOUNT.EXCEPTION IN ('CA','CP')) THEN 1 ELSE 0 END) Collection_Accounts,
	sum(ACCOUNT.STATEMENTS) Num_of_Statements,
	0 Total_Charges_Amount
FROM   
	ACCOUNT ACCOUNT
WHERE 
	ACCOUNT.LOGDATE between to_number(to_char(to_date('20070101','yyyymmdd'),'j')) and to_number(to_char(to_date('20071231','yyyymmdd'),'j'))
GROUP BY
	ACCOUNT.CPCODE,ACCOUNT.BILLSTATUS
UNION ALL
SELECT 
	ACCOUNT.CPCODE sSite, 
	ACCOUNT.BILLSTATUS Fin_Class,
	0 Num_of_Accounts,
	0 Collection_Accounts,
	0 Num_of_Statements,
	SUM(CHARGES.PRAMOUNT) Total_Charges_Amount
FROM   
	ACCOUNT ACCOUNT
	JOIN CHARGES CHARGES ON ((ACCOUNT.CPCODE=CHARGES.CPCODE) AND (ACCOUNT.CODE=CHARGES.ACCOUNT) AND (CHARGES.TYPE='C') AND (CHARGES.SPLITFLAG IS  NULL))
WHERE 
	ACCOUNT.LOGDATE between to_number(to_char(to_date('20070101','yyyymmdd'),'j')) and to_number(to_char(to_date('20071231','yyyymmdd'),'j'))
GROUP BY
	ACCOUNT.CPCODE,ACCOUNT.BILLSTATUS
--------------------------------------------------------------
) U
Group By ROLLUP(sSite, Fin_Class)

Previous Topic: Usage of materialized views
Next Topic: Compare a varchar2 variable to a String using PL/SQL function
Goto Forum:
  


Current Time: Sun Dec 04 20:30:33 CST 2016

Total time taken to generate the page: 0.22278 seconds