Home » SQL & PL/SQL » SQL & PL/SQL » Conditional Query
Conditional Query [message #227579] |
Wed, 28 March 2007 12:49  |
sandhyaa
Messages: 79 Registered: February 2007
|
Member |
|
|
Hi All,
Please help me in accomplishing the below output using a query.
I have 3 tables
--EMPLOYEE_TIME
CREATE TABLE EMPLOYEE_TIME(EMPLOYEE NUMBER, WEEK_DATE DATE, TASK NUMBER, HOURS NUMBER)
--PROJECT_TASK (One PROJECT can have many TASK)
CREATE TABLE PROJECT_TASK(TASK NUMBER, PROJECT NUMBER)
--EMPLOYEE_TASK_RATE
CREATE TABLE EMPLOYEE_TASK_RATE(EMPLOYEE NUMBER, PROJECT NUMBER, TASK NUMBER, RATE NUMBER)
INSERT INTO PROJECT_TASK VALUES(100, 1001);
INSERT INTO PROJECT_TASK VALUES(100, 1002);
INSERT INTO PROJECT_TASK VALUES(100, 1003);
INSERT INTO PROJECT_TASK VALUES(200, 2000);
INSERT INTO PROJECT_TASK VALUES(200, 2001);
INSERT INTO PROJECT_TASK VALUES(200, 2002);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(10, NULL, 1001, 13);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(11, NULL, 1001, 14);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(11, NULL, 1002, 15);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(12, 200, NULL, 21);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(12, NULL, 2000, 17);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(13, NULL, 2001, 16);
--Time can only be booked against a TASK
INSERT INTO EMPLOYEE_TIME VALUES(10, '01-APR-2006', 1001, 7);
INSERT INTO EMPLOYEE_TIME VALUES(10, '02-APR-2006', 1001, 8);
INSERT INTO EMPLOYEE_TIME VALUES(11, '01-APR-2006', 1001, 8);
INSERT INTO EMPLOYEE_TIME VALUES(12, '01-APR-2006', 2000, 8);
INSERT INTO EMPLOYEE_TIME VALUES(13, '02-APR-2006', 2001, 9);
INSERT INTO EMPLOYEE_TIME VALUES(12, '03-APR-2006', 2002, 8);
How to write a query based on table EMPLOYEE_TIME and EMPLOYEE_TASK_RATE to get Employee Hour Rate Details.
If rate is not defined/available at TASK level and it is defined at PROJECT Level, then PROJECT level Rate should be used. Otherwise Rate should be taken based on TASK only.
So the output should be:
EMPLOYEE WEEK_DATE TASK HOURS RATE
10 01-APR-2006 1001 7 13
10 02-APR-2006 1001 8 13
11 01-APR-2006 1001 8 14
12 01-APR-2006 2000 8 17
13 02-APR-2006 2001 9 16
12 03-APR-2006 2002 8 21 -- 21 is taken as the rate because employee 12 does not
have a rate for TASK 2002 in EMPLOYEE_TASK_RATE table
and 200 is the PROJECT for TASK 2002
Please help in accomplishing this.
Thanks
Sandi
|
|
|
Re: Conditional Query [message #227591 is a reply to message #227579] |
Wed, 28 March 2007 13:52   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
look at nvl and decode (or case if you like).
if t1.col1 is null then return t2.col2
if t1.col1 is null then return t2.col2 [else return t1.col1].
decode(t1.col1, null, t2.col2 [, t1.col1)]
|
|
|
|
|
Re: Conditional Query [message #227707 is a reply to message #227579] |
Thu, 29 March 2007 02:30  |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Try following (I assumed existance of PROJECT table with RATE column). Everything else is based on your table's definitions:
CREATE TABLE PROJECT ( PROJECT NUMBER, RATE NUMBER);
SELECT ET.EMPLOYEE, ET.WEEK_DATE, ET.TASK, NVL(ETR.RATE,(SELECT RATE FROM PROJECT P WHERE P.PROJECT = PT.PROJECT )) RATE
FROM EMPLOYEE_TIME ET
JOIN PROJECT_TASK PT ON ( PT.TASK = ET.TASK )
JOIN EMPLOYEE_TASK_RATE ETR ON ( ETR.EMPLOYEE = ET.EMPLOYEE AND ETR.TASK = ET.TASK AND ETR.PROJECT = PT.PROJECT )
HTH.
Michael
|
|
|
Goto Forum:
Current Time: Mon Feb 17 05:25:35 CST 2025
|