"In" slow trying to count unique people

[mdl2]

This is part of a 5 part union where I am trying to count the number of unique people and the amount of money spent
in 3 program areas. If a person is in program a, I want them counted in program A but not counted in B or C. If they are in B and not A then I want them counted in b and if they are in C then I only want them counted if they weren't in A or B. However, I need the total amount of monies spent by program. When I hard code the dates it works ok but when I added the report table so that we can run a report on demand to pick up the data it ground to a standstill. Can anyone see what I am doing incorrectly? Thanks

select 
SHORT_DESC, 
Age_category, 
0 COUNT_CLIENTCASE, 
sum(EXPENDED_AMT) SUM_EXPENDED_AMT, 
HRE_REPORTS_ID 
from ( 
SELECT DISTINCT 
NVL(CL.CLIENT_ID,999999) || NVL(C.FILE_NO,999999) clientcase, 
(case when to_number(TRUNC(REP.EFFECTIVE_DATE) 
             - cl.BIRTH_DATE) / 365 < 30 then 
              'Youth' 
         when  to_number(TRUNC(REP.EFFECTIVE_DATE) 
             - cl.BIRTH_DATE) / 365 >= 30 then 
            'Adult' 
         else 
             'Unknown' 
       end  )  Age_category, 
PR.PAY_REQUIRE_ID, 
C.FILE_NO, 
PB.SHORT_DESC, 
PR.EXPENDED_AMT, 
HRE_REPORTS_ID 
FROM 
PAY_USER.PAY_REQUIRE PR, 
PAY_USER.PAYMENT P, 
PAY_USER.CLIENT CL, 
PAY_USER.CASE C, 
PAY_USER.PROGRAM_BENEFIT PB, 
PAY_USER.HRE_REPORTS REP 
WHERE 
P.PAYMENT_ID = PR.PAYMENT_ID 
AND CL.CLIENT_ID(+) = PR.CLIENT_ID 
AND C.CASE_ID = P.CASE_ID 
AND P.CREATED_BY_ID<>1 
AND  TRUNC(NVL(P.PER_START_DATE,P.PAYMENT_DATE)) >= TRUNC(REP.EFFECTIVE_DATE) 
AND  TRUNC(NVL(P.PER_START_DATE,P.PAYMENT_DATE)) < TRUNC(REP.EFFECTIVE_TO) 
AND PB.PROGRAM_BENEFIT_ID = PR.REQ_TYPE_ID 
and REP.REPORT_ID  = 9517 
and PR.REQ_TYPE_ID = 842 
and P.CASE_ID   in 
(SELECT P2.CASE_ID 
  FROM 
PAY_USER.PAY_REQUIRE PR2, 
PAY_USER.PAYMENT P2 
WHERE 
P2.PAYMENT_ID = PR2.PAYMENT_ID 
AND P.CASE_ID = P2.CASE_ID 
AND P2.CREATED_BY_ID <>1 
AND  TRUNC(NVL(P2.PER_START_DATE,P2.PAYMENT_DATE)) >=  TRUNC(REP.EFFECTIVE_DATE) 
AND  TRUNC(NVL(P2.PER_START_DATE,P2.PAYMENT_DATE)) < TRUNC(REP.EFFECTIVE_TO) 
and PR2.REQ_TYPE_ID in (841,843)  )) 
group by SHORT_DESC, Age_category, HRE_REPORTS_ID

 

[rac2]

That is too much to think about specifically. Perhaps a general tip will help.

Look at the program a person is in as determining a subset of the details you are summarizing. For example Joe is in program B and C. He has some details rows for program B and other detail rows for program C. But the rows are all in one table, MyDetails with columns for person and program and expended_amt.

Put the expended_amt values from the three programs side-by-side using a self-join of the detail rows from each program.

SELECT person,
       SUM(a.expended_amt) AS "A_expense",
       SUM(b.expended_amt) AS "B_expense",
       SUM(c.expended_amt) AS "C_expense"
FROM MyDetails a
LEFT JOIN MyDetails b ON b.person = a.person
                AND b.program = 'B'
LEFT JOIN MyDetails c ON c.person = a.person
                AND c.program = 'C'
WHERE a.program = 'A'
GROUP BY person

This will show the amount for each person in each program.

Save that as a VIEW and call it PeopleProgramExpenses.
Write a query of that view using CASE expressions to determine which program to show the total expense for a person.

SELECT person,
       CASE
         WHEN A_expense > 0 
           THEN A_expense + B_expense + C_expense
         ELSE 0
       END AS "ABorC_expense",
       CASE
         WHEN A_expense = 0 AND B_expense > 0
           THEN B_expense + C_expense
         ELSE 0
       END AS "BorC_expense",
       CASE
         WHEN A_expense = 0 AND B_expense = 0
           THEN C_expense
         ELSE 0
       END AS "C_expense"
FROM PeopleProgramExpenses

I dont know if it is expence or expense, sorry.

 

[johnherman]

If you are using ORACLE, then consider using a MINUS query. My DBA always told me that for Oracle, MINUS is more efficient than IN or NOT IN.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright