| Table almost too large to query [message #371105] |
Sun, 27 August 2000 15:51 |
Chris
Messages: 128
Registered: November 1998
|
Senior Member |
|
|
Hi everyone,
Hopefully some charitable soul will want to take a stab at this one. I'm trying to tally up usage per package type. My problem is that the 'usage' table is ever changing and is so large that it just about blows up the server. Ideally it'd be nice if I could do the query in parts; connect a customer to their package, sum up usage for each customer, combine the two to return the amount of hours per package. If it's any help the 'usage table' (ACCT_BACKUP) uniquely connects to the USR table via the BILLINGID. The USR table then connects to the 'package table' (SVC_DATA) via the USR_ID.USR = ENTITY_ID.SVC_DATA. The ENTITY_PRF table is a required connection in order to properly match each user to their package.
Here's my code. If anyone has any ideas I'm all ears. Thank you.
SELECT B.VALUE AS DEAL_DESCRIPTION, ROUND(SUM(I.DELTA)/3600,2) AS HOURS_OF_USAGE
FROM SVC_DATA A, SVC_DATA B, SVC_DATA C, ENTITY_PRF D, USR H, ACCT_BACKUP I
WHERE A.ATTR_NAME = 'PRICING_PLAN_NUMBER'
AND B.ATTR_NAME = 'PRICING_PLAN_DETAIL'
AND C.ATTR_NAME = 'CHOSEN_PRICING_PLAN'
AND C.ENTITY_ID = H.USR_ID
AND C.VALUE = A.VALUE
AND A.ENTITY_ID = H.ENT_PRF_ID
AND A.CONTEXT = 2
AND B.CONTEXT = 2
AND C.CONTEXT = 1
AND A.SUBSCRIPT = B.SUBSCRIPT
AND A.ENTITY_ID = B.ENTITY_ID
AND D.ENT_PRF_ID = A.ENTITY_ID
AND D.STATUS = 'A'
AND H.STATUS = 'A'
AND I.TYPE = 2
AND TRUNC (I.CREATED) >= '25-JUL-00' AND TRUNC(I.CREATED) <= '31-JUL-2000'
AND H.BILLINGID = I.BILLINGID
AND H.BILLINGID NOT IN ('6789', '50016502')
AND H.LOGIN_NAME V_LOGIN_NAME
GROUP BY B.VALUE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
