Home » SQL & PL/SQL » SQL & PL/SQL » Need a help with SQL (Oracle 12C)
Need a help with SQL [message #661788] |
Sun, 02 April 2017 20:13 |
|
mangeshbhasme
Messages: 4 Registered: April 2017
|
Junior Member |
|
|
I have been trying to write SQL which will give grade start date and grade end date. ( when grade changes and when grade ended ). I have tried with Min analytical function with partition by clause. But it gives wrong result. So I am trying to explore other options.
select assignment_id
, grade_id
,effective_start_date
,effective_end_date
, FIRST_VALUE(effective_start_date)OVER( partition by assignment_id,grade_id ORDER BY effective_start_date ASC ROWS UNBOUNDED PRECEDING) AS first_grade
--, sum(effective_start_date+1) over ( order by effective_start_date ) a
--, LISTAGG(effective_start_date||'-'||effective_end_date,',') within group ( ORDER BY effective_start_date ) over ( partition by assignment_id,grade_id ) AS fv
, dense_rank() over ( partition by assignment_id order by grade_id ) grade_rank
from per_all_assignments_f paaf
where assignment_id = 32354
Below are sample expected output. We need to calculate Grade start date and grade end date as below. Thank you so much for help
Assignment ID Grade ID Effective_start_date Effective_end_date Grade Start Date Grade End Date
32354 8-Oct-73 30-Dec-78
32354 758 31-Dec-78 27-Aug-79 31-Dec-78 27-Aug-79
32354 759 28-Aug-79 1-Jun-80 28-Aug-79 1-Jun-80
32354 758 2-Jun-80 15-Jun-80 2-Jun-80 4-Mar-82
32354 758 16-Jun-80 12-Oct-80 2-Jun-80 4-Mar-82
32354 758 13-Oct-80 11-Jan-81 2-Jun-80 4-Mar-82
32354 758 12-Jan-81 4-Mar-82 2-Jun-80 4-Mar-82
32354 759 5-Mar-82 15-Oct-83 5-Mar-82 30-Oct-83
32354 759 16-Oct-83 16-Oct-83 5-Mar-82 30-Oct-83
32354 759 17-Oct-83 30-Oct-83 5-Mar-82 30-Oct-83
Below scripts.
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,null,to_date('08-OCT-1973','DD-MON-RRRR'),to_date('30-DEC-1978','DD-MON-RRRR'),3);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('31-DEC-1978','DD-MON-RRRR'),to_date('27-AUG-1979','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('28-AUG-1979','DD-MON-RRRR'),to_date('01-JUN-1980','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('02-JUN-1980','DD-MON-RRRR'),to_date('15-JUN-1980','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('16-JUN-1980','DD-MON-RRRR'),to_date('12-OCT-1980','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('13-OCT-1980','DD-MON-RRRR'),to_date('11-JAN-1981','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('12-JAN-1981','DD-MON-RRRR'),to_date('04-MAR-1982','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('05-MAR-1982','DD-MON-RRRR'),to_date('15-OCT-1983','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('16-OCT-1983','DD-MON-RRRR'),to_date('16-OCT-1983','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('17-OCT-1983','DD-MON-RRRR'),to_date('30-OCT-1983','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('31-OCT-1983','DD-MON-RRRR'),to_date('13-NOV-1983','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('14-NOV-1983','DD-MON-RRRR'),to_date('26-FEB-1984','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('27-FEB-1984','DD-MON-RRRR'),to_date('16-SEP-1984','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('17-SEP-1984','DD-MON-RRRR'),to_date('24-FEB-1985','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('25-FEB-1985','DD-MON-RRRR'),to_date('05-MAY-1985','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('06-MAY-1985','DD-MON-RRRR'),to_date('13-JUL-1985','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('14-JUL-1985','DD-MON-RRRR'),to_date('29-SEP-1985','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('30-SEP-1985','DD-MON-RRRR'),to_date('20-OCT-1985','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('21-OCT-1985','DD-MON-RRRR'),to_date('26-JAN-1986','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('27-JAN-1986','DD-MON-RRRR'),to_date('16-MAR-1986','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('17-MAR-1986','DD-MON-RRRR'),to_date('08-JAN-1987','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('09-JAN-1987','DD-MON-RRRR'),to_date('30-AUG-1987','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('31-AUG-1987','DD-MON-RRRR'),to_date('25-OCT-1987','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('26-OCT-1987','DD-MON-RRRR'),to_date('22-NOV-1987','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('23-NOV-1987','DD-MON-RRRR'),to_date('31-JAN-1988','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('01-FEB-1988','DD-MON-RRRR'),to_date('31-DEC-1989','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('01-JAN-1990','DD-MON-RRRR'),to_date('08-JUL-1990','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('09-JUL-1990','DD-MON-RRRR'),to_date('04-NOV-1990','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('05-NOV-1990','DD-MON-RRRR'),to_date('06-JAN-1991','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('07-JAN-1991','DD-MON-RRRR'),to_date('26-JAN-1991','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('27-JAN-1991','DD-MON-RRRR'),to_date('30-MAY-1993','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('31-MAY-1993','DD-MON-RRRR'),to_date('31-MAY-1993','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('01-JUN-1993','DD-MON-RRRR'),to_date('29-AUG-1993','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('30-AUG-1993','DD-MON-RRRR'),to_date('31-OCT-1993','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('01-NOV-1993','DD-MON-RRRR'),to_date('28-NOV-1993','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('29-NOV-1993','DD-MON-RRRR'),to_date('02-JAN-1994','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('03-JAN-1994','DD-MON-RRRR'),to_date('06-MAR-1994','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('07-MAR-1994','DD-MON-RRRR'),to_date('26-NOV-1995','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('27-NOV-1995','DD-MON-RRRR'),to_date('01-JAN-1996','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('02-JAN-1996','DD-MON-RRRR'),to_date('20-JUL-1997','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('21-JUL-1997','DD-MON-RRRR'),to_date('28-NOV-1999','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('29-NOV-1999','DD-MON-RRRR'),to_date('06-FEB-2000','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('07-FEB-2000','DD-MON-RRRR'),to_date('25-MAR-2001','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('26-MAR-2001','DD-MON-RRRR'),to_date('21-JUL-2002','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('22-JUL-2002','DD-MON-RRRR'),to_date('09-FEB-2003','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('10-FEB-2003','DD-MON-RRRR'),to_date('21-DEC-2003','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('22-DEC-2003','DD-MON-RRRR'),to_date('19-FEB-2006','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('20-FEB-2006','DD-MON-RRRR'),to_date('29-JUN-2007','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('30-JUN-2007','DD-MON-RRRR'),to_date('20-SEP-2007','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('21-SEP-2007','DD-MON-RRRR'),to_date('14-JAN-2008','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,759,to_date('15-JAN-2008','DD-MON-RRRR'),to_date('31-JAN-2008','DD-MON-RRRR'),2);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('01-FEB-2008','DD-MON-RRRR'),to_date('20-MAY-2008','DD-MON-RRRR'),1);
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,758,to_date('21-MAY-2008','DD-MON-RRRR'),to_date('31-DEC-4712','DD-MON-RRRR'),1);
*BlackSwan added {code} tags. Please do so yourself in the future
[Updated on: Sun, 02 April 2017 20:23] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: Need a help with SQL [message #661794 is a reply to message #661793] |
Sun, 02 April 2017 22:18 |
|
mangeshbhasme
Messages: 4 Registered: April 2017
|
Junior Member |
|
|
In below example, for row #2 grade id 758 assigned on 31-Dec-78 effective start date. So grade start date os 31-Dec-78. Now, grade id got changed in on row #3 from 758 to 759. Hence, grade end date for row #2 calculated as 27-Aug-79.
On row #3, grade id 759 assigned on 28-Aug-79 and got changed on row #4. So grade start date calculated as 28-Aug-79 and grade end date as 1-Jun-80.
On row #4, grade id 758 assigned on 2-jun-80 and remain same on 16-Jun-80, 13-oct-80, 12-jan-81 so grade start date calculated as 16-Jun-80 and grade got changed on row#8 from 758 to 759. Hence, grade end date calculated as 4-Mar-82.
In short, grade start date says when grade id got recently assigned and when it got changed.
Please note that next row effective start date will always equals to prior row effective end date + 1.
Assignment ID Grade ID Effective_start_date Effective_end_date Grade Start Date Grade End Date
32354 8-Oct-73 30-Dec-78
32354 758 31-Dec-78 27-Aug-79 31-Dec-78 27-Aug-79
32354 759 28-Aug-79 1-Jun-80 28-Aug-79 1-Jun-80
32354 758 2-Jun-80 15-Jun-80 2-Jun-80 4-Mar-82
32354 758 16-Jun-80 12-Oct-80 2-Jun-80 4-Mar-82
32354 758 13-Oct-80 11-Jan-81 2-Jun-80 4-Mar-82
32354 758 12-Jan-81 4-Mar-82 2-Jun-80 4-Mar-82
32354 759 5-Mar-82 15-Oct-83 5-Mar-82 30-Oct-83
32354 759 16-Oct-83 16-Oct-83 5-Mar-82 30-Oct-83
32354 759 17-Oct-83 30-Oct-83 5-Mar-82 30-Oct-83
Thank you so much for looking into it. Appreciate your help.
[mod-edit: code tags added by bb]
[Updated on: Sun, 02 April 2017 22:26] by Moderator Report message to a moderator
|
|
|
Re: Need a help with SQL [message #661796 is a reply to message #661794] |
Sun, 02 April 2017 22:56 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> select assignment_id, grade_id,
2 min(effective_start_date) grade_start_date,
3 max(effective_end_date) grade_end_date
4 from (select assignment_id, grade_id, effective_start_date, effective_end_date,
5 sum(grade_change) over (order by effective_start_date) grade_group
6 from (select assignment_id, grade_id, effective_start_date, effective_end_date,
7 decode
8 (grade_id,
9 lag(grade_id) over (order by effective_start_date), 0,
10 1) grade_change
11 from assignment))
12 group by assignment_id, grade_id, grade_group
13 order by grade_start_date
14 /
ASSIGNMENT_ID GRADE_ID GRADE_STA GRADE_END
------------- ---------- --------- ---------
32354 08-Oct-73 30-Dec-78
32354 758 31-Dec-78 27-Aug-79
32354 759 28-Aug-79 01-Jun-80
32354 758 02-Jun-80 04-Mar-82
32354 759 05-Mar-82 30-Oct-83
32354 758 31-Oct-83 26-Feb-84
32354 759 27-Feb-84 16-Sep-84
32354 758 17-Sep-84 13-Jul-85
32354 759 14-Jul-85 20-Oct-85
32354 758 21-Oct-85 08-Jan-87
32354 759 09-Jan-87 30-Aug-87
32354 758 31-Aug-87 22-Nov-87
32354 759 23-Nov-87 31-Dec-89
32354 758 01-Jan-90 30-May-93
32354 759 31-May-93 31-May-93
32354 758 01-Jun-93 06-Feb-00
32354 759 07-Feb-00 31-Jan-08
32354 758 01-Feb-08 31-Dec-12
18 rows selected.
|
|
|
|
|
Re: Need a help with SQL [message #661813 is a reply to message #661809] |
Mon, 03 April 2017 18:15 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I added two partition by clauses to the query below. Please see if that is any more efficient. Please run the query twice and use the second time for comparison, so that you are not counting hard parsing during the first run.
select assignment_id, grade_id,
min(effective_start_date) grade_start_date,
max(effective_end_date) grade_end_date
from (select assignment_id, grade_id, effective_start_date, effective_end_date,
sum(grade_change) over
(partition by assignment_id order by effective_start_date) grade_group
from (select assignment_id, grade_id, effective_start_date, effective_end_date,
decode
(grade_id,
lag(grade_id) over
(partition by assignment_id order by effective_start_date), 0,
1) grade_change
from assignment))
group by assignment_id, grade_id, grade_group
order by grade_start_date
/
|
|
|
Re: Need a help with SQL [message #661858 is a reply to message #661788] |
Tue, 04 April 2017 06:59 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
[quote title=mangeshbhasme wrote on Sun, 02 April 2017 20:13]I have been trying to write SQL which will give grade start date and grade end date. ( when grade changes and when grade ended ). I have tried with Min analytical function with partition by clause. But it gives wrong result. So I am trying to explore other options.
Insert into assignment (ASSIGNMENT_ID,GRADE_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,RA) values (32354,null,to_date('08-OCT-1973','DD-MON-RRRR'),to_date('30-DEC-1978','DD-MON-RRRR'),3);
As an aside to your question, let me point out that you should not be using the RRRR mask in your to_date/to_char functions. You should be using YYYY only. The 'RR' and 'RRRR' masks were intended to be a temporory fix to buy some time for Y2K remediation. That was over 17 years ago. RR was not intended to be a permanent solution, and it's continued use at this point will almost certainly have unintended results. Seventeen years after its intended use and it will be not so much a "solution" as a ticking time-bomb.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:29:56 CDT 2024
|