ORACLE SQL Statement

TABLE A  Project_Table

ProjectID      ProjectName            ProjectOwner
1AA      Funding Project      John
1AC      Computer Security      Jane
2DE      Loan Project            Kevin
23J      Audit Project            David


TABLE B  Staff_Table

StaffID            ProjectID            StaffName            Role
1            1AA            John            Owner
2            1AA            Jane            Assistant 1
3            1AA            David            Assistant 2
4            23J            David            Owner
5            23J            Kevin            Assistant 1
6             1AC            Jane            Owner
7            2DE            Kevin            Owner
8            2DE            David            Assistant 1
9            2DE            Jane            Assistant 2      


TABLE C  TimeCard

ID            PROJECTID            StaffID                  Hours
1            1AA            1                  15
2            1AA            2                  3
3            1AA            3                  12
4             1AC            6                  10
5            23J            4                  8
6            2DE            9                  2

How do I create my sql so I can get the following output:

Name            Hours on own Project      Hours on other Projects
John            15            0
Jane            10            5
Kevin            0            8

Kind a new at SQL.  I know how to create SQL statements but not complicated as this.

Thank you for any assistance.
jramos74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AielloJCommented:
jramos74:

Try the SQL below.  It works by returning a 1 or 0 based on whether the project owner is the same as the staff member in each SUM'd row the OwnProject alias and the reverse in the OtherProject alias.

I didn't get to test it as my Oracle server is down, but it's pretty close if not working.
SELECT
  StaffName,
  SUM(
  IF (St.StaffName = PT.ProjectOwner)
  THEN
    1
  ELSE
    0
  END IF) AS OwnProject,
  SUM(IF (St.StaffName = PT.ProjectOwner)
  THEN
    0
  ELSE
    1
  END IF) AS OtherProject
FROM
  TimeCard TC
 INNER JOIN
  Staff_Table ST
 ON
  TC.StaffID = ST.StaffID
 INNER JOIN
  Project_Table PT
 ON
  ST.ProjectID = PT.ProjectID
GROUP BY
  St.StaffName,
  IF (St.StaffName = PT.ProjectOwner)
  THEN
    1
  ELSE
    0
  END IF),
  IF (St.StaffName = PT.ProjectOwner)
  THEN
    0
  ELSE
    1
  END IF)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jramos74Author Commented:
Thank you. I will test it tonight.
0
jramos74Author Commented:
Hello.

What about if my TimeCard Table is as follows:  Instead of having hrs sum already the columns for the hours are broken down into days.

ID            PROJECTID            StaffID             Day1  Day2 Day3 Day4 Day5 Day6 Day7
1            1AA                        1                             0        5       5       0       0        5       0      
2            1AA                        2                             0        3       0      0         0       0       0
3            1AA                       3                              0        3       0      3         3       0       3
4             1AC                       6                             5        0       0       0         0       0       5
5            23J                         4                             8        0        0       0         0        0       0
6            2DE                         9                             0        2        0      0         0         0       0
 
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.