Home » SQL & PL/SQL » SQL & PL/SQL » Help with this Analytical Query (Windowing Function) Question (Oracle 10gR2)
Help with this Analytical Query (Windowing Function) Question [message #570610] Mon, 12 November 2012 14:00 Go to next message
rasa
Messages: 45
Registered: February 2006
Member
I have a simple requirement the, solution for which is getting elusive for me.

Consider this dataset in a table called A_STATUS:

A_ID       BEGIN_DATE      END_DATE      S_NAME
----     ----------      --------        --------
6057	1/1/2012	1/5/2012	Active
6057	1/6/2012	1/8/2012	Active
6057	1/10/2012	1/12/2012	Active
6057	1/13/2012	1/18/2012	Active
6057	1/20/2012	1/31/2012	Active


I would like to flatten the results to:

A_ID       BEGIN_DATE      END_DATE      S_NAME
----     ----------      --------        --------
6057	1/1/2012	1/8/2012	Active
6057	1/10/2012	1/18/2012	Active
6057	1/20/2012	1/31/2012	Active


That is because, if the BEGIN_DATE differs by 1 day of the END_DATE of the preceding row, then it means it is continuous. So, the rows are flattened as shown above.

Now, when, I try this:


SELECT A_ID, S_NAME, MIN(BEGIN_DATE)  BEGIN_DATE, MAX(END_DATE) END_DATE	  
FROM (
	SELECT A.A_ID,
	       A.BEGIN_DATE,
	       A.END_DATE,
	       A.S_NAME,			
	       CASE WHEN A.BEGIN_DATE 
- LAG(A.END_DATE, 1, A.END_DATE) OVER(PARTITION BY A_ID, S_NAME 
	ORDER BY BEGIN_DATE) < 0 THEN A.BEGIN_DATE
		    WHEN A.BEGIN_DATE 
- LAG(A.END_DATE, 1, A.END_DATE) OVER(PARTITION BY A_ID, S_NAME 
	ORDER BY BEGIN_DATE) = 1
	            THEN FIRST_VALUE(A.BEGIN_DATE) OVER 
(PARTITION BY A.A_ID, A.S_NAME ORDER BY A.BEGIN_DATE ROWS UNBOUNDED PRECEDING) 	
		ELSE
		    A.BEGIN_DATE
		END AS DIFF										   
	FROM A_STAT A
	)
GROUP BY A_ID, S_NAME, DIFF
ORDER BY 1, 2, 3;	


I am getting the following results:

A_ID	S_NAME	BEGIN_DATE	END_DATE
-----   ------- ----------      ------------
6057	Active	1/1/2012	1/18/2012
6057	Active	1/10/2012	1/12/2012
6057	Active	1/20/2012	1/31/2012


The problem is that it has flattened with 1/1/2012 to 1/18/2012 instead of 1/8/2012. Likewise, the next row, should have been 1/10/2012 to 1/18/2012 instead of 1/12/2012.

It looks like I must be able to somehow window the partition that I am operating upon in order to keep flattening as I go from one partition to the other. The key is if the BEGIN_DATE is 1 day ahead of the prior END_DATE, I must treat that as continuous.

Here is the test-table:
create table A_STAT
(
  a_id       NUMBER(9) not null,
  begin_date DATE,
  end_date   DATE,
  s_name     VARCHAR2(16)
);


Here is the test-data population:
insert into A_STAT (A_ID, BEGIN_DATE, END_DATE, S_NAME)
values (6057, to_date('01-01-2012', 'dd-mm-yyyy'), to_date('05-01-2012', 'dd-mm-yyyy'), 'Active');

insert into A_STAT (A_ID, BEGIN_DATE, END_DATE, S_NAME)
values (6057, to_date('06-01-2012', 'dd-mm-yyyy'), to_date('08-01-2012', 'dd-mm-yyyy'), 'Active');

insert into A_STAT (A_ID, BEGIN_DATE, END_DATE, S_NAME)
values (6057, to_date('10-01-2012', 'dd-mm-yyyy'), to_date('12-01-2012', 'dd-mm-yyyy'), 'Active');

insert into A_STAT (A_ID, BEGIN_DATE, END_DATE, S_NAME)
values (6057, to_date('13-01-2012', 'dd-mm-yyyy'), to_date('18-01-2012', 'dd-mm-yyyy'), 'Active');

insert into A_STAT (A_ID, BEGIN_DATE, END_DATE, S_NAME)
values (6057, to_date('20-01-2012', 'dd-mm-yyyy'), to_date('31-01-2012', 'dd-mm-yyyy'), 'Active');

[Updated on: Mon, 12 November 2012 14:09] by Moderator

Report message to a moderator

Re: Help with this Analytical Query (Windowing Function) Question [message #570614 is a reply to message #570610] Mon, 12 November 2012 15:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Assuming dates do not overlap:

with t1 as (
            select  a_id,
                    begin_date,
                    end_date,
                    s_name,
                    case lag(end_date) over(partition by a_id,s_name order by begin_date,end_date) + 1
                      when begin_date then 0 
                      else 1
                    end start_of_group
              from  a_stat
           ),
     t2 as (
            select  a_id,
                    begin_date,
                    end_date,
                    s_name,
                    sum(start_of_group) over(partition by a_id,s_name order by begin_date,end_date) grp
              from  t1
           )
select  a_id,
        min(begin_date) begin_date,
        max(end_date) end_date,
        s_name
  from  t2
  group by a_id,
           s_name,
           grp
  order by a_id,
           begin_date
/

 A_ID BEGIN_DATE END_DATE   S_NAME
----- ---------- ---------- ----------------
 6057 01/01/2012 01/08/2012 Active
 6057 01/10/2012 01/18/2012 Active
 6057 01/20/2012 01/31/2012 Active

SQL> 


And if dates do overlap:

with t1 as (
            select  a_id,
                    begin_date,
                    end_date,
                    s_name,
                    case
                      when max(end_date) over(
                                              partition by a_id,s_name
                                              order by begin_date,end_date
                                              rows between unbounded preceding and 1 preceding
                                             ) + 1 >= begin_date then 0 
                      else 1
                    end start_of_group
              from  a_stat
           ),
     t2 as (
            select  a_id,
                    begin_date,
                    end_date,
                    s_name,
                    sum(start_of_group) over(partition by a_id,s_name order by begin_date,end_date) grp
              from  t1
           )
select  a_id,
        min(begin_date) begin_date,
        max(end_date) end_date,
        s_name
  from  t2
  group by a_id,
           s_name,
           grp
  order by a_id,
           begin_date
/

 A_ID BEGIN_DATE END_DATE   S_NAME
----- ---------- ---------- ----------------
 6057 01/01/2012 01/08/2012 Active
 6057 01/10/2012 01/18/2012 Active
 6057 01/20/2012 01/31/2012 Active

SQL>  


SY.
Re: Help with this Analytical Query (Windowing Function) Question [message #570615 is a reply to message #570614] Mon, 12 November 2012 15:27 Go to previous message
rasa
Messages: 45
Registered: February 2006
Member
@Solomon -- Subquery RFing is the part, I totally missed. This is an awesome technique I have learned today. Thank you very much.
Previous Topic: convert datetime to date
Next Topic: ORA-00054
Goto Forum:
  


Current Time: Mon Oct 20 22:28:42 CDT 2014

Total time taken to generate the page: 0.08350 seconds