[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORACLE SQL Statement

Posted on 2008-11-19
3
Medium Priority
?
737 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:jramos74
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
AielloJ earned 1500 total points
ID: 22999259
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
 

Author Comment

by:jramos74
ID: 22999941
Thank you. I will test it tonight.
0
 

Author Comment

by:jramos74
ID: 23000048
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question