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 Go to next message
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 #661789 is a reply to message #661788] Sun, 02 April 2017 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Need a help with SQL [message #661791 is a reply to message #661789] Sun, 02 April 2017 20:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

We need a couple of additional details.
1) post CREATE TABLE ASSIGNMENT statement
2) explain why the posted desired results are the correct results.

Report message to a moderator

Re: Need a help with SQL [message #661792 is a reply to message #661791] Sun, 02 April 2017 21:06 Go to previous messageGo to next message
mangeshbhasme
Messages: 4
Registered: April 2017
Junior Member
Here is create script for table.

create table assignment  
(  ASSIGNMENT_ID  number
 , GRADE_ID  number
 , EFFECTIVE_START_DATE  date 
 , EFFECTIVE_END_DATE  date
 , RA  number
 );

Thank you for posting this question.

[Updated on: Sun, 02 April 2017 21:17]

Report message to a moderator

Re: Need a help with SQL [message #661793 is a reply to message #661792] Sun, 02 April 2017 21:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Provide your expected result set and explain the rules/reasons that lead to it.
You have provided expected results.
Please explain the rules/reasons that lead to it.

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
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.

Report message to a moderator

Re: Need a help with SQL [message #661809 is a reply to message #661796] Mon, 03 April 2017 11:34 Go to previous messageGo to next message
mangeshbhasme
Messages: 4
Registered: April 2017
Junior Member
Thank you for reply.

We tried this solution and result is perfect. However, we are facing serious performance issue if we fire this query for all assignments. It worked very well if we run for individual assignments. Is there any other way to get same result without using analytical function? or if we want to use Analytical function then what indexes we need to create to improve performance?

Thank you

Report message to a moderator

Re: Need a help with SQL [message #661810 is a reply to message #661809] Mon, 03 April 2017 11:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
INDEX only provides benefit for columns in the WHERE clause.

Report message to a moderator

Re: Need a help with SQL [message #661813 is a reply to message #661809] Mon, 03 April 2017 18:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
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
/

Report message to a moderator

Re: Need a help with SQL [message #661858 is a reply to message #661788] Tue, 04 April 2017 06:59 Go to previous message
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.

Report message to a moderator

Previous Topic: how to send tablespace mail alerts with PL/SQL
Next Topic: Split clob data and store in table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 07:45:33 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.