Home » SQL & PL/SQL » SQL & PL/SQL » Help with PL SQL query - Analytical Function or group by (12c)
Help with PL SQL query - Analytical Function or group by [message #664982] |
Tue, 15 August 2017 13:23 |
namitanamburi
Messages: 35 Registered: March 2009
|
Member |
|
|
Below is the emp table with emp_id as primary key and emp_job_id being the foreign key to emp_job table.
Below is the table data for three employees with Emp_job_id IN (100,200,300)
Emp_ID entered Emp_job_id Status
1 1/1/2017 8:00 100 JOINED
2 1/2/2017 10:00 100 REVIEWED
3 1/3/2017 7:00 100 REVIEW ON HOLD
4 1/4/2017 9:00 100 REVIEW APPROVED
5 1/5/2017 9:00 100 REVIEW DENIED
Emp_ID entered Emp_job_id Status
6 2/10/2017 8:00 200 JOINED
7 2/11/2017 0:00 200 REVIEWED
8 2/12/2017 7:00 200 REVOKED
9 2/13/2017 9:00 200 REJECTED
Emp_ID entered Emp_job_id Status
10 1/1/2017 8:00 300 REVIEW ON HOLD
11 1/2/2017 10:00 300 IN PROGRESS
12 1/3/2017 7:00 300 REVOKED
13 1/4/2017 9:00 300 REJECTED
14 1/5/2017 9:00 300 REVIEW DENIED
If status IN (REVIEW ON HOLD,REVIEW APPROVED,REVIEW DENIED) THEN I want to pick min(entered) date.
Emp_ID entered Emp_job_id Status
1 1/1/2017 8:00 100 JOINED
2 1/2/2017 10:00 100 REVIEWED
3 1/3/2017 7:00 100 REVIEW ON HOLD
4 1/4/2017 9:00 100 REVIEW APPROVED
5 1/5/2017 9:00 100 REVIEW DENIED
Here I pick min entered which is 1/3/2017 7:00 (emp_id = 3)
If status IN (REVOKED,REJECTED) THEN I want to pick max(entered) date.
Emp_ID entered Emp_job_id Status
6 2/10/2017 8:00 200 JOINED
7 2/11/2017 0:00 200 REVIEWED
8 2/12/2017 7:00 200 REVOKED
9 2/13/2017 9:00 200 REJECTED
Here I pick max of entered which is 2/13/2017 9:00 (emp_id = 9)
If an employee has status IN (REVIEW ON HOLD,REVIEW APPROVED,REVIEW DENIED) and
status IN (REVOKED,REJECTED) then (REVOKED,REJECTED) takes priority, so pick only (REVOKED,REJECTED) then use max(entered of REVOKED,REJECTED).
Emp_ID entered Emp_job_id Status
10 1/1/2017 8:00 300 REVIEW ON HOLD
11 1/2/2017 10:00 300 IN PROGRESS
12 1/3/2017 7:00 300 REVOKED
13 1/4/2017 9:00 300 REJECTED
14 1/5/2017 9:00 300 REVIEW DENIED
Here I pick max of entered BETWEEN (REVOKED,REJECTED)
)which is 1/4/2017 9:00
(emp_id = 13)
Any help would be highly appreciated.
[Updated on: Tue, 15 August 2017 13:30] Report message to a moderator
|
|
|
|
Re: Help with PL SQL query - Analytical Function or group by [message #665127 is a reply to message #664983] |
Tue, 22 August 2017 17:10 |
namitanamburi
Messages: 35 Registered: March 2009
|
Member |
|
|
my apologies for the delay.
here is the code.
CREATE TABLE emp
(
emp_id NUMBER ,
entered DATE,
emp_job_id NUMBER,
status VARCHAR2(100)
);
select * from emp;
INSERT INTO emp VALUES (1,TO_TIMESTAMP('01-JAN-2017 08:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'JOINED' );
INSERT INTO emp VALUES (2,TO_TIMESTAMP('02-JAN-2017 10:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'REVIEWED' );
INSERT INTO emp VALUES (3,TO_TIMESTAMP('03-JAN-2017 07:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'REVIEW ON HOLD' );
INSERT INTO emp VALUES (4,TO_TIMESTAMP('04-JAN-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'REVIEW APPROVED' );
INSERT INTO emp VALUES (5,TO_TIMESTAMP('05-JAN-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 100, 'REVIEW DENIED' );
INSERT INTO emp VALUES (6,TO_TIMESTAMP('10-FEB-2017 08:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 200, 'JOINED' );
INSERT INTO emp VALUES (7,TO_TIMESTAMP('11-FEB-2017 00:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 200, 'REVIEWED' );
INSERT INTO emp VALUES (8,TO_TIMESTAMP('12-FEB-2017 07:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 200, 'REVOKED' );
INSERT INTO emp VALUES (9,TO_TIMESTAMP('13-FEB-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 200, 'REJECTED' );
INSERT INTO emp VALUES (10,TO_TIMESTAMP('01-JAN-2017 08:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'REVIEW ON HOLD' );
INSERT INTO emp VALUES (11,TO_TIMESTAMP('02-JAN-2017 10:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'IN PROGRESS' );
INSERT INTO emp VALUES (12,TO_TIMESTAMP('03-JAN-2017 07:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'REVOKED' );
INSERT INTO emp VALUES (13,TO_TIMESTAMP('04-JAN-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'REJECTED' );
INSERT INTO emp VALUES (14,TO_TIMESTAMP('05-JAN-2017 09:00:00:00','DD-MON-YYYY HH24:MI:SS:FF'), 300, 'REVIEW DENIED' );
commit;
I want to write query that can return
max of entered date if status IN ('REVOKED','REJECTED')
min of entered date if status IN ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED')
max of entered date if status has both ('REVOKED','REJECTED') and ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED')
SELECT MIN(entered)
FROM emp
WHERE status IN ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED')
AND emp_job_id = 100;
SELECT MAX(entered)
FROM emp
WHERE status IN ('REVOKED','REJECTED')
AND emp_job_id = 200;
WITH emp_data as (
SELECT entered,status,
ROW_NUMBER()
OVER (PARTITION BY emp_job_id
ORDER BY CASE WHEN status IN ('REVOKED','REJECTED') THEN 1
WHEN status IN ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED') THEN 2
ELSE 3
END)
FROM emp
WHERE status IN ('REVIEW ON HOLD','REVIEW APPROVED','REVIEW DENIED','REVOKED','REJECTED')
AND emp_job_id = 300)
select max(entered) from emp_data where status IN ('REVOKED','REJECTED');
Any help is highly appreciated.
|
|
|
|
|
Re: Help with PL SQL query - Analytical Function or group by [message #665228 is a reply to message #665222] |
Sat, 26 August 2017 07:47 |
|
Flyby
Messages: 188 Registered: March 2011 Location: Belgium
|
Senior Member |
|
|
I came up with WITH aggregatebyjobidandstatus
AS ( SELECT emp_job_id
,status
,MAX (entered) entered_date_latest_by_status
,MIN (entered) entered_date_oldest_by_status
,CASE WHEN STATUS IN ('REVOKED', 'REJECTED') THEN 1 ELSE 0 END
NOREVIEW_COUNT
,CASE
WHEN STATUS IN
('REVIEW ON HOLD'
,'REVIEW APPROVED'
,'REVIEW DENIED') THEN
1
ELSE
0
END
REVIEW_COUNT
FROM emp
WHERE status IN ('REVIEW ON HOLD'
,'REVIEW APPROVED'
,'REVIEW DENIED'
,'REVOKED'
,'REJECTED')
GROUP BY emp_job_id, status)
SELECT emp_job_id
,CASE
WHEN SUM (REVIEW_COUNT) > 0 AND SUM (NOREVIEW_COUNT) > 0 THEN
(SELECT MAX (entered_date_latest_by_status)
FROM aggregatebyjobidandstatus
WHERE pickmydate.emp_job_id = aggregatebyjobidandstatus.emp_job_id
AND NOREVIEW_COUNT = 1)
WHEN SUM (REVIEW_COUNT) > 0 THEN
(SELECT MIN (entered_date_oldest_by_status)
FROM aggregatebyjobidandstatus
WHERE pickmydate.emp_job_id = aggregatebyjobidandstatus.emp_job_id
AND REVIEW_COUNT = 1)
ELSE
(SELECT MAX (entered_date_latest_by_status)
FROM aggregatebyjobidandstatus
WHERE pickmydate.emp_job_id = aggregatebyjobidandstatus.emp_job_id
AND NOREVIEW_COUNT = 1)
END
DATE_AND_TIME_TO_PICK
FROM aggregatebyjobidandstatus pickmydate
GROUP BY emp_job_id
order by emp_job_id
Result (DD/MM/YYYY)
EMP_JOB_ID DATE_AND_TIME_TO_PICK
100 03/01/2017
200 13/02/2017
300 04/01/2017
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:39:09 CDT 2024
|