Home » SQL & PL/SQL » SQL & PL/SQL » Query Help
| Query Help [message #328293] |
Thu, 19 June 2008 11:28  |
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 #328344 is a reply to message #328293] |
Thu, 19 June 2008 16:42  |
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 |
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 #328530 is a reply to message #328518] |
Fri, 20 June 2008 07:08 |
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 |
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 |
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 |
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)
|
|
|
|
Goto Forum:
Current Time: Sun Dec 08 17:57:53 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
