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  |
rasa
Messages: 44
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  |
Solomon Yakobson
Messages: 1397
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 18 02:38:21 CDT 2013
Total time taken to generate the page: 0.12325 seconds
|
] 
Members
Search
Help
Register
Login
Home
