OraFAQ Forum: SQL & PL/SQL » Display date ranges in one column as separate date periods (start and end date) in two?

Home » SQL & PL/SQL » SQL & PL/SQL » Display date ranges in one column as separate date periods (start and end date) in two? (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458675 is a reply to message #458672]

Tue, 01 June 2010 03:55

Michel Cadot
Messages: 68646
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This has been asked and answered many times, for instance there.

As indicated in forum, you should post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel

Report message to a moderator

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 #458688 is a reply to message #458684]

Tue, 01 June 2010 04:40

Michel Cadot
Messages: 68646
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This does not work if you have several period in the same month.

Regards
Michel

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

Report message to a moderator

Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458693 is a reply to message #458690]

Tue, 01 June 2010 05:18

Michel Cadot
Messages: 68646
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

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

OP just said:
Quote:

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.

Nothing about the restriction of one period per month. Just the test case is not complete.

Please keep your lines of code in 80 character width.

Regards
Michel

Report message to a moderator

Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458694 is a reply to message #458693]

Tue, 01 June 2010 05:27

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Oh yes,an incomplete requirement leads to confusion.Hope OP will understand and try to provide complete test case in his next post.

I had to understand analyzing the output only.Analyzing the output,
I understood that in a month there can only be one period ( assumption: whatever data provided by OP)

Thanks for the feedback.

Regards
Ved

Report message to a moderator

Re: Display date ranges in one column as separate date periods (start and end date) in two? [message #458695 is a reply to message #458692]

Tue, 01 June 2010 05:38

Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome

Senior Member

lprbsql wrote on Tue, 01 June 2010 12:16

'ORA-32638: Non unique addressing in MODEL dimensions.

Duplicate date values give that error.

lprbsql wrote on Tue, 01 June 2010 12:16

This seems to work though...

...I forgot about the tabibitosan method, it's great.

Report message to a moderator

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 #458727 is a reply to message #458701]

Tue, 01 June 2010 08:50

Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome

Senior Member

Its_me_ved wrote on Tue, 01 June 2010 13:25

A question here: Won't the cost would be high here for additional sort?

What are you using to compare this query?
I don't see additional sorts. What do you exactly intend?

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 Cool

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

Report message to a moderator

Previous Topic:	Usage of 2D Arrays, temporary Pl/Sql Tables, Dynamic Queries
Next Topic:	Recycle bin not working

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 12:39:16 CDT 2024