Home » SQL & PL/SQL » SQL & PL/SQL » data split by range
data split by range [message #357898] Fri, 07 November 2008 01:40 Go to next message
domisj
Messages: 4
Registered: May 2008
Location: Vilnius
Junior Member
i need to split date by periods, without using cursor(only sql).
For example, i have 1 row start_date: 2008.04.02, end_date 2008.06.07.

i need to split this row in 3 rows by range:
2008.04.02-2008.04.30
2008.05.01-2008.05.31
2008.06.01-2008.06.07
Re: data split by range [message #357900 is a reply to message #357898] Fri, 07 November 2008 01:48 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@domisj,

I think Row Generator Techniques will help you there.

Regards,
Jo
Re: data split by range [message #358025 is a reply to message #357900] Fri, 07 November 2008 15:00 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
As @joicejohn suggested, use a row generator to get a list of months.

SELECT add_months(m, -level) b, add_months(m,-level+1)-1 e
FROM (
    SELECT trunc(sysdate, 'MONTH') m FROM DUAL
)
CONNECT BY LEVEL <= 100


Then join that to your table select GREATEST() and LEAST() dates as appropriate to get your ranges.

Ross Leishman
Previous Topic: Optimization.
Next Topic: VIEW not showing all fields
Goto Forum:
  


Current Time: Fri Dec 02 21:09:28 CST 2016

Total time taken to generate the page: 0.12427 seconds