Hello riaz,
I tried your sql. But it returns some of rows duplicate. Anyway thanks for assistance.
I myself have found out some better sql .. So I want to withdraw my question.
Rgds,
bhombal
Main Topics
Browse All TopicsI wrote an sql statement as follows
SELECT a.proj_name, billable_offshore_members,
non_billable_offshore_memb
( nvl(billable_offshore_memb
+ nvl(non_billable_offshore_
) total_offshore_team_size
FROM (SELECT proj_name,
COUNT (offshore_onsite_notrep_fl
FROM ta_mis_emp_info
WHERE fortnight_no = 2004031
AND billable_flg = 'Y'
AND offshore_onsite_notrep_flg
AND project_id NOT IN ('SBU', 'PROCESS')
GROUP BY proj_name) a,
(SELECT proj_name,
COUNT (offshore_onsite_notrep_fl
FROM ta_mis_emp_info
WHERE fortnight_no = 2004031
AND billable_flg = 'N'
AND offshore_onsite_notrep_flg
AND project_id NOT IN ('SBU', 'PROCESS')
GROUP BY proj_name) b
WHERE a.proj_name = b.proj_name(+)
Which produces result as
P1 7 1 8
P2 5 1 6
P3 4 1 5
P4 56 1 57
P5 12 4 16
Now my question is, Is there any better alternative sql can be written?
I tried something, but that didn't produce desire result. Which is
SELECT a.proj_name,
COUNT (a.offshore_onsite_notrep_
COUNT (b.offshore_onsite_notrep_
( NVL (COUNT (a.offshore_onsite_notrep_
+ NVL (COUNT (b.offshore_onsite_notrep_
) total_offshore_team_size
FROM ta_mis_emp_info a, ta_mis_emp_info b
WHERE a.proj_name = b.proj_name(+)
AND a.fortnight_no = 2004031
AND b.fortnight_no(+) = 2004031
AND a.billable_flg = 'Y'
AND b.billable_flg(+) = 'N'
AND a.offshore_onsite_notrep_f
AND b.offshore_onsite_notrep_f
AND a.project_id NOT IN ('SBU', 'PROCESS')
AND b.project_id(+) NOT IN ('SBU', 'PROCESS')
GROUP BY a.proj_name
Which produces result as
P1 7 7 14
P2 5 5 10
P3 4 4 8
P4 56 56 132
P5 12 12 24
CAN ANYONE PROVIDE SOME BETTER ALTERNATIVE?
Thanx in advance,
bhombal
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi,
Try this SQL.
SELECT a.proj_name, billable_offshore_members,
non_billable_offshore_memb
( nvl(billable_offshore_memb
+ nvl(non_billable_offshore_
) total_offshore_team_size
FROM (SELECT proj_name,
COUNT (offshore_onsite_notrep_fl
FROM ta_mis_emp_info
WHERE fortnight_no = 2004031
AND billable_flg = 'Y'
AND offshore_onsite_notrep_flg
(project_id <> 'SBU' AND project_id <> 'PROCESS')
GROUP BY proj_name) a,
(SELECT proj_name,
COUNT (offshore_onsite_notrep_fl
FROM ta_mis_emp_info
WHERE fortnight_no = 2004031
AND billable_flg = 'N'
AND offshore_onsite_notrep_flg
(project_id <> 'SBU' AND project_id <> 'PROCESS')
GROUP BY proj_name) b
WHERE a.proj_name = b.proj_name(+)
Also, create following indexes.. These will speed up the query execution AND WOULD TAKE LESS TIME TO EXECUTE.
1) create index idx_1 on ta_mis_emp_info(proj_name)
2) create index idx_2 on ta_mis_emp_info(fortnight_
3) If u are using Oracle 9i :
create bitmap index idx_3 on ta_mis_emp_info(project_id
If u are using lesser version than 9i, user
create index idx_3 on ta_mis_emp_info(project_id
Rgds,
NHM
Business Accounts
Answer for Membership
by: riazpkPosted on 2004-03-17 at 04:40:14ID: 10614567
Can you try this:
e_members, non_billab le_offshor e_members, bers,0)+ nvl(non_billable_offshore_ members,0) ) total_offshore_team_size t,null) billable_offshore_members, t,null) non_billable_offshore_memb ers
NT (offshore_onsite_notrep_fl g) cnt = 'F'
select proj_name,billable_offshor
(nvl(billable_offshore_mem
from
(
SELECT proj_name, decode(billable_flg,'Y',cn
decode(billable_flg,'N',cn
FROM (
SELECT proj_name,billable_flg,COU
FROM ta_mis_emp_info
WHERE fortnight_no = 2004031
AND offshore_onsite_notrep_flg
AND project_id NOT IN ('SBU', 'PROCESS')
GROUP BY proj_name,billable_flg)
)
Also it will be great if you provide ddl script table and insertion commands for sample data. May be we able to help you better.