Query advice and suggestions

[t16turbo]

I need to write a query that wil return time booked to a project, grouped by client.

This is not a problem in itself, although I need to return time booked by support staff AND time booked by other staff.

All time booked is stored in the same tables.
I'm just wondering if the most effective/efficient way of doing this would be to have two nested SELECT statments in the FROM clause(one for support staff time, and one for non-support staff time).

and then just return the time portion of each of these in the main query's select statement.

OR, is there a better way of doing this kind of thing?

thanks in advance,

 

[Dagon]

Without knowing your table structures, it's difficult to answer this. What does your client/time table look like ?
Is it something like:

client
worker
worker_type
time_allocated

Or is it more complicated than that ?

 

[t16turbo]

its slightly more complicated than that.
its spread out over a resource table (containing the OBS unit of the resource - which is how I will determine support staff or not), a task table joined to the project table, an assignment table, joined to the task table and the resource table.

a simple query that returns all time booked to the project

SELECT
p.ID,
P.NAME,
SUM(a.PRACTSUM) as actuals

FROM 
srm_projects p,
prtask t,
prassignment a

WHERE p.id = t.prprojectid
a.prtaskid = t.prid
GROUP BY 
p.ID,
P.NAME

That returns the total actuals against each project.
to get actuals booked by a certain resource (i.e. two types of resource) I'd include the resource table and then filter on a cetrtain resource type (giving me the time for that one particular resource)

do you think nested SELECTs would work or woudl a CASE statement be used here??
thanks fo ryour help

 

[SantaMufasa]

Turbo,

How about rather than Dagon's (or my) guessing about so much about your situation, you go ahead and post your "best shot" at the code your you are alternating between (amongst), and we can assert any additional suggestions to your proposals.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[t16turbo]

that makes sense!!

right, here's my code:

SELECT
p.ID,
P.NAME,
SUM(a.PRACTSUM/3600) as actuals,
Support.actuals as SupportTime,
NonSupport.actuals as NonSupportTime
FROM 
srm_projects p,
prtask t,
prassignment a,
(SELECT
p.ID,
SUM(a.PRACTSUM/3600) as actuals

FROM 
srm_projects p,
prtask t,
prassignment a,
nbi_resource_current_facts r,
nbi_dim_obs obs

WHERE 
a.PRRESOURCEID = r.RESOURCE_ID
and obs.obs_unit_id = r.OBS1_UNIT_ID
and p.id = t.prprojectid
and a.prtaskid = t.prid
and obs.LEVEL5_NAME = 'Support'

GROUP BY 
p.ID) Support,
(SELECT
p.ID,
SUM(a.PRACTSUM/3600) as actuals

FROM 
srm_projects p,
prtask t,
prassignment a,
nbi_resource_current_facts r,
nbi_dim_obs obs

WHERE 
a.PRRESOURCEID = r.RESOURCE_ID
and obs.obs_unit_id = r.OBS1_UNIT_ID
and p.id = t.prprojectid
and a.prtaskid = t.prid
and obs.LEVEL5_NAME <>'Support'

GROUP BY 
p.ID) NonSupport

WHERE 
NonSupport.id(+) = p.id and
Support.id(+) = p.id and
p.id = t.prprojectid
and a.prtaskid = t.prid

GROUP BY 
p.ID,
P.NAME,
Support.actuals,
NonSupport.actuals

as you can see I have 2 nested queries. Each one giving me the portion of time I want to see.
The SQL will eventually be in an Actuate report which will then be published to our Clarity project management system.

I'm just trying to improve and learn better ways of doing things or more efficeint ways..

 

[SantaMufasa]

Frankly, Turbo, I rely very heavily upon "in-line VIEWS" as you have done in this example ("In-lines" are slightly more efficient than "declared" database-stored VIEWS since you needn't access the database for the VIEW definition. The main benefit of "declared" VIEWs is that it hides the complexity/details of your SELECT from anyone else that may be using the VIEW.)

Therefore, if this code produces the results you want, then go for it. If you are concerned about the execution speed, then you must go through the process of evaluating execution plans and tweaking the code on an "as-needed" basis, VIEWs or not.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

Try the following


SELECT
p.ID,
P.NAME,
SUM(a.PRACTSUM/3600) as actuals,
sum(decode(obs.LEVEL5_NAME,'Support',a.PRACTSUM/3600,0)) SupportTime,
sum(decode(obs.LEVEL5_NAME,'Support',0,a.PRACTSUM/3600)) NonSupportTime
FROM
srm_projects p,
prtask t,
prassignment a,
nbi_resource_current_facts r,
nbi_dim_obs obs
WHERE
a.PRRESOURCEID = r.RESOURCE_ID
and obs.obs_unit_id = r.OBS1_UNIT_ID
and p.id = t.prprojectid
and a.prtaskid = t.prid
and obs.LEVEL5_NAME = 'Support'
GROUP BY
p.ID,
P.NAME;


Bill
Oracle DBA/Developer
New York State, USA

 

[Dagon]

I think I would take an opposite view to Santa on this one. The SQL looks horribly complicated and not very efficient. Tables are being self-joined quite pointlessly. I think you should be able to do it with case statements in the sums. Something along the lines of:

SELECT
p.ID,
SUM(a.PRACTSUM/3600) as actuals
SUM(CASE WHEN obs.LEVEL5_NAME = 'Support' THEN a.PRACTSUM/3600 ELSE 0 END) as support,
SUM(CASE WHEN obs.LEVEL5_NAME <>'Support'
THEN A.PRACTSUM/3600 ELSE 0 END) as non_support
FROM 
srm_projects p,
prtask t,
prassignment a,
nbi_resource_current_facts r,
nbi_dim_obs obs
WHERE 
a.PRRESOURCEID = r.RESOURCE_ID
and obs.obs_unit_id = r.OBS1_UNIT_ID
and p.id = t.prprojectid
and a.prtaskid = t.prid
GROUP BY 
p.ID

 

[SantaMufasa]

Dagon,

Please don't misundertand my point. I'm not suggesting that "willy-nilly" pointless joins of complex in-line views has any value, especially if there is a better option that uses no joins.

I'm just saying, "In-Line VIEWs are fine to use if there is no more-efficient option." That is precisely why I said, "...go through the process of evaluating execution plans and tweaking the code on an "as-needed" basis, VIEWs or not." I certainly did not take the time to evaluate Turbo's code to determine if there is "a better way" (although I should have, to avoid exposure to misunderstanding ).

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

I wish that you could edit in this forum. I made a mistake in my code, see the corrected code below.


SELECT
p.ID,
P.NAME,
SUM(a.PRACTSUM/3600) as actuals,
sum(decode(obs.LEVEL5_NAME,'Support',a.PRACTSUM/3600,0)) SupportTime,
sum(decode(obs.LEVEL5_NAME,'Support',0,a.PRACTSUM/3600)) NonSupportTime
FROM
srm_projects p,
prtask t,
prassignment a,
nbi_resource_current_facts r,
nbi_dim_obs obs
WHERE
a.PRRESOURCEID = r.RESOURCE_ID
and obs.obs_unit_id = r.OBS1_UNIT_ID
and p.id = t.prprojectid
and a.prtaskid = t.prid
GROUP BY
p.ID,
P.NAME;

Bill
Oracle DBA/Developer
New York State, USA

 

[t16turbo]

thansk guys.
that works.
well, myold idea works, but I must say I DO like to keep things looking neat and easy to debug.