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?
 
AielloJConnect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.