Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

optimized sql query with 4 more output columns

user8680179Sep 13 2011 — edited Sep 15 2011
TableStructure 
CREATE TABLE EMPLOYEE (
	ID VARCHAR2(4) NOT NULL,
	FIRST_NAME VARCHAR2(10),
	LAST_NAME VARCHAR2(10),
	START_DATE DATE,
	END_DATE DATE,
	SALARY NUMBER(8,2),
	CITY VARCHAR2(10),
	DESCRIPTION VARCHAR2(15)
);
TableData 
INSERT INTO EMPLOYEE VALUES ('02', 'Celi', 'Black', to_date('01/16/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('02', 'David', 'Cat', to_date('09/17/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('04/15/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1232.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('01', 'Alastiar', 'Celi', to_date('01/18/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('01', 'Andrew', 'Celi', to_date('01/19/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('02', 'sturat', 'Celi', to_date('01/22/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('02', 'morgan', 'Celi', to_date('01/20/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('01', 'kevin', 'Celi', to_date('01/17/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('02', 'jonathan', 'Celi', to_date('01/21/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('02', 'brsnan', 'Celi', to_date('01/23/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('01', 'Jason', 'Celia', to_date('07/25/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('07/25/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1234.56, 'Vancouver', 'Programmer');
INSERT INTO EMPLOYEE VALUES ('01', 'David', 'Celia', to_date('08/01/2007 11:20:18', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/18/2007 11:20:18', 'MM/DD/YYYY HH24:MI:SS'), 46000, 'Vancouver', 'Programmer');
INSERT INTO EMPLOYEE VALUES ('01', 'Celi', 'Green', to_date('07/30/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/04/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4322.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('02', 'Jason', 'Jaso', to_date('03/21/1976 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('02/21/1986 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6661.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('01', 'James', 'Jason', to_date('12/12/1978 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('03/15/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6544.78, 'Vancouver', 'Tester');
INSERT INTO EMPLOYEE VALUES ('02', 'David', 'Larry', to_date('12/31/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('02/12/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7897.78, 'Vancouver', 'Manager');
INSERT INTO EMPLOYEE VALUES ('01', 'Celia', 'Rice', to_date('10/24/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('04/21/1999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2344.78, 'Vancouver', 'Manager');
INSERT INTO EMPLOYEE VALUES ('02', 'Celi', 'White', to_date('01/15/1984 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');

INSERT INTO EMPLOYEE VALUES ('02', 'Celi', 'Black', to_date('12/11/1977 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('08/08/1998 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2334.78, 'Vancouver', 'Tester');
SQLQuery 
SELECT Name,minStartDate,maxStartDate,cnt FROM(SELECT Name, count(*) cnt, min(start_date) minStartDate, max(start_date) maxStartDate
FROM
(
select  first_name Name,start_date from employee WHERE start_date between 
to_date('11/12/1978 8:20:18', 'dd/mm/yyyy HH24:MI:SS') AND to_date('18/9/1996 10:20:18', 'dd/mm/yyyy HH24:MI:SS') 
union all
select  last_name Name,start_date from employee WHERE start_date between 
to_date('11/12/1978 8:20:18', 'dd/mm/yyyy HH24:MI:SS') AND to_date('18/9/1996 10:20:18', 'dd/mm/yyyy HH24:MI:SS') 
)
GROUP BY Name
HAVING COUNT(*) > 1)
where minStartDate > to_date('23/10/1982 10:09:18', 'dd/mm/yyyy HH24:MI:SS') AND 
maxStartDate < to_date('31/7/1987 10:21:18', 'dd/mm/yyyy HH24:MI:SS') ;
Above sql output 
(Name,minStartDate,maxStartDate,cnt)(Celi,1984-01-15 00:00:00,1987-07-30 00:00:00,10)
Now i want 4 more output column besides the above mentioned output which will be as : countNameInFirstNameWhenIdIs1,countNameInFirstNameWhenIdIs2,countNameInLastNameWhenIdIs1,countNameInLastNameWhenIdIs2
assumption
- id values can be 1 or 2.

Means i want final output as 
(Name,minStartDate,maxStartDate,cnt,countNameInFirstNameWhenIdIs1,countNameInFirstNameWhenIdIs2,countNameInLastNameWhenIdIs1,countNameInLastNameWhenIdIs2)(Celi,1984-01-15 00:00:00,1987-07-30 00:00:00,10,1,2,3,4)
As Celi is coming 1 time in first_name when id=1
As Celi is coming 2 time in first_name when id=2
As Celi is coming 3 time in last_name when id=1
As Celi is coming 4 time in last_name when id=2
What will be optimized sql?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 13 2011
Added on Sep 13 2011
6 comments
166 views