Home » SQL & PL/SQL » SQL & PL/SQL » Display date ranges in one column as separate date periods (start and end date) in two? (10g)
| Display date ranges in one column as separate date periods (start and end date) in two? [message #458672] |
Tue, 01 June 2010 03:33  |
lprbsql
Messages: 12
Registered: June 2010
|
Junior Member |
|
|
Hello,
I'm trying to work out how to take a table like this:
ID Date
125 02-Feb-07
125 16-Mar-07
125 23-May-07
125 24-May-07
125 25-May-07
333 02-Jan-09
333 03-Jan-09
333 04-Jan-09
333 17-Mar-09
And display the data like this:
ID Period Period Start Period End
125 1 02-Feb-07 02-Feb-07
125 2 16-Mar-07 16-Mar-07
125 3 23-May-07 25-May-07
333 1 02-Jan-09 04-Jan-09
333 2 17-Mar-09 17-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
I'd really appreciate any suggestions.
Many thanks,
lprbsql
|
|
|
|
|
|
| Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458676 is a reply to message #458672] |
Tue, 01 June 2010 03:56  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Hint:
Min/Max/Row_number analytical function
to_char(dt,'mm')
I proceed this way..
SQL> ed
Wrote file afiedt.buf
1 create table f01 as (
2 select 125 id, to_date('02-Feb-2007','dd-mon-yyyy') dt from dual union all
3 select 125, to_date('16-Mar-2007','dd-mon-yyyy') from dual union all
4 select 125, to_date('23-May-2007','dd-mon-yyyy') from dual union all
5 select 125, to_date('24-May-2007','dd-mon-yyyy') from dual union all
6 select 125, to_date('25-May-2007','dd-mon-yyyy') from dual union all
7 select 333, to_date('02-Jan-2009','dd-mon-yyyy') from dual union all
8 select 333, to_date('03-Jan-2009','dd-mon-yyyy') from dual union all
9 select 333, to_date('04-Jan-2009','dd-mon-yyyy') from dual union all
10* select 333, to_date('17-Mar-2009','dd-mon-yyyy') from dual )
SQL> /
Table created.
SQL> select * from f01;
ID DT
---------- ---------
125 02-FEB-07
125 16-MAR-07
125 23-MAY-07
125 24-MAY-07
125 25-MAY-07
333 02-JAN-09
333 03-JAN-09
333 04-JAN-09
333 17-MAR-09
9 rows selected.
SQL> select distinct id, min(dt) over ( partition by id,to_char(dt,'mm') order by to_char(dt,'mm')) dt1,
2 max(dt) over ( partition by id,to_char(dt,'mm') order by to_char(dt,'mm')) dt2 from f01
3 order by id,dt1;
ID DT1 DT2
---------- --------- ---------
125 02-FEB-07 02-FEB-07
125 16-MAR-07 16-MAR-07
125 23-MAY-07 25-MAY-07
333 02-JAN-09 04-JAN-09
333 17-MAR-09 17-MAR-09
Good luck!
Regards
Ved
[Updated on: Tue, 01 June 2010 04:10] Report message to a moderator |
|
|
|
| Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458678 is a reply to message #458672] |
Tue, 01 June 2010 04:18 |
 |
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
|
Senior Member |
|
|
This may do it.
Processing ...
with inp as (
select 125 as id,
to_date('02-Feb-07','dd-mon-yy','nls_date_language=''ENGLISH''') as day from dual
union all
select 125,to_date('16-Mar-07','dd-mon-yy','nls_date_language=''ENGLISH''') from dual
union all
select 125,to_date('23-May-07','dd-mon-yy','nls_date_language=''ENGLISH''') from dual
union all
select 125,to_date('24-May-07','dd-mon-yy','nls_date_language=''ENGLISH''') from dual
union all
select 125,to_date('25-May-07','dd-mon-yy','nls_date_language=''ENGLISH''') from dual
union all
select 333,to_date('02-Jan-09','dd-mon-yy','nls_date_language=''ENGLISH''') from dual
union all
select 333,to_date('03-Jan-09','dd-mon-yy','nls_date_language=''ENGLISH''') from dual
union all
select 333,to_date('04-Jan-09','dd-mon-yy','nls_date_language=''ENGLISH''') from dual
union all
select 333,to_date('17-Mar-09','dd-mon-yy','nls_date_language=''ENGLISH''') from dual
)
select id,
row_number() over (partition by id order by grp) as period,
min(day) as p_start,max(day) as p_end
from (
select *
from inp i
model
partition by (id)
dimension by (day )
measures (rownum as grp)
rules (
grp[any] order by day = presentv(grp[cv()-1],grp[cv()-1],grp[cv()])
)
)
group by id,grp
Query finished, retrieving results...
ID PERIOD P_START P_END
-------- ---------- ------------- -------------
125 1 02/02/2007 02/02/2007
125 2 16/03/2007 16/03/2007
125 3 23/05/2007 25/05/2007
333 1 02/01/2009 04/01/2009
333 2 17/03/2009 17/03/2009
5 row(s) retrieved
Bye Alessandro
[Updated on: Tue, 01 June 2010 05:19] by Moderator Report message to a moderator |
|
|
|
| Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458684 is a reply to message #458676] |
Tue, 01 June 2010 04:36 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Modified: Added the period field
SQL> SELECT DISTINCT id,
2 Dense_rank() OVER(PARTITION BY id ORDER BY id, To_char(dt, 'mm')
3 ) period,
4 MIN(dt) OVER ( PARTITION BY id, To_char(dt, 'mm') ORDER BY
5 To_char(dt, 'mm'))
6 dt1,
7 MAX(dt) OVER ( PARTITION BY id, To_char(dt, 'mm') ORDER BY
8 To_char(dt, 'mm'))
9 dt2
10 FROM f01
11 ORDER BY id,
12 dt1 ;
ID PERIOD DT1 DT2
---------- ---------- --------- ---------
125 1 02-FEB-07 02-FEB-07
125 2 16-MAR-07 16-MAR-07
125 3 23-MAY-07 25-MAY-07
333 1 02-JAN-09 04-JAN-09
333 2 17-MAR-09 17-MAR-09
Regards
Ved
[Updated on: Tue, 01 June 2010 04:37] Report message to a moderator |
|
|
|
|
|
| Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458690 is a reply to message #458688] |
Tue, 01 June 2010 04:49 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Quote:
This does not work if you have several period in the same month.
@Michel : How can be several period in the same month?
As per the test case provided by OP, he is picking two dates as min or max for the month. We can have only one period in a month ( FROM_DT, TO_DATE) for the id
Or we can do like this...
SQL> select id,row_number() over (partition by q.id order by q.id) period, dt1,dt2
from ( select distinct id, min(dt) over ( partition by id,to_char(dt,'mm') order by to_char(dt,'mm')) dt1,
2 max(dt) over ( partition by id,to_char(dt,'mm') order by to_char(dt,'mm')) dt2 from f01) q
3 order by q.id,q.dt1
4 /
ID PERIOD DT1 DT2
---------- ---------- --------- ---------
125 1 02-FEB-07 02-FEB-07
125 2 16-MAR-07 16-MAR-07
125 3 23-MAY-07 25-MAY-07
333 1 02-JAN-09 04-JAN-09
333 2 17-MAR-09 17-MAR-09
Regards
Ved
[Updated on: Tue, 01 June 2010 05:17] Report message to a moderator |
|
|
|
| Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458692 is a reply to message #458690] |
Tue, 01 June 2010 05:16 |
lprbsql
Messages: 12
Registered: June 2010
|
Junior Member |
|
|
Hello,
Great stuff. Thanks very much to you all.
Alessandro, thank you - this looks very interesting. But when I try it on the real DB it comes up with this error: 'ORA-32638: Non unique addressing in MODEL dimensions.'
I think the lesson for me (as Michel said) there is to give you guys a proper test case.
Ved, thanks very much for this. Unfortunately, I still can't get it to show multiple date ranges in the same month.
This seems to work though...
SELECT sp.id id
,sp.period Period
,MIN(sp.date) Start_Date
,MAX(sp.date) End_Date
FROM ( select id
,date
,dense_rank() over (partition by id order by grp) period
FROM (
SELECT id
,date
,date-row_number() over (partition by id order by date) grp
FROM mytable )
ORDER BY id,date
) sp
GROUP BY sp.id,sp.period;
Many thanks for all your help.
Cheers,
lprbsql
|
|
|
|
|
|
|
|
|
|
| Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458701 is a reply to message #458695] |
Tue, 01 June 2010 06:25 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
A question here: Won't the cost would be high here for additional sort?
SQL> explain plan for
2 SELECT sp.id id
3 ,sp.period Period
4 ,MIN(sp.dt) Start_dt
5 ,MAX(sp.dt) End_dt
6 FROM ( select id
7 ,dt
8 ,dense_rank() over (partition by id order by grp) period
9 FROM (
10 SELECT id
11 ,dt
12 ,dt-row_number() over (partition by id order by dt) grp
13 FROM f01 )
14 ORDER BY id,dt
15 ) sp
16 GROUP BY sp.id,sp.period;
Explained.
Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1631597866
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 420 | 8 (50)| 00:00:01 |
| 1 | HASH GROUP BY | | 12 | 420 | 8 (50)| 00:00:01 |
| 2 | VIEW | | 12 | 420 | 7 (43)| 00:00:01 |
| 3 | SORT ORDER BY | | 12 | 336 | 7 (43)| 00:00:01 |
| 4 | WINDOW SORT | | 12 | 336 | 7 (43)| 00:00:01 |
| 5 | VIEW | | 12 | 336 | 5 (20)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | WINDOW SORT | | 12 | 264 | 5 (20)| 00:00:01 |
| 7 | TABLE ACCESS FULL| F01 | 12 | 264 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Regards
Ved
[Updated on: Tue, 01 June 2010 06:25] Report message to a moderator |
|
|
|
|
|
| Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #460742 is a reply to message #458672] |
Mon, 14 June 2010 06:05 |
cocbwwbwobwo
Messages: 1
Registered: June 2010
Location: Japan
|
Junior Member |
|
|
This thread explains Tabibitosan method 
forums.oracle.com/forums/thread.jspa?threadID=1005478
with mytable(ID,dayC) as (
select 125,date '2007-02-02' from dual union
select 125,date '2007-05-16' from dual union
select 125,date '2007-05-23' from dual union
select 125,date '2007-05-24' from dual union
select 125,date '2007-05-25' from dual union
select 333,date '2009-01-02' from dual union
select 333,date '2009-01-03' from dual union
select 333,date '2009-01-04' from dual union
select 333,date '2009-03-17' from dual)
select ID,Row_Number() over(partition by ID order by dis) as rn,
min(dayC) as staD,max(dayC) as endD
from (select ID,dayC,
dayC-Row_Number() over(partition by ID order by dayC) as dis
from mytable)
group by ID,dis
order by ID,dis;
ID RN STAD ENDD
--- -- -------- --------
125 1 07-02-02 07-02-02
125 2 07-05-16 07-05-16
125 3 07-05-23 07-05-25
333 1 09-01-02 09-01-04
333 2 09-03-17 09-03-17
|
|
|
|
Goto Forum:
Current Time: Wed Jan 22 09:58:59 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
