Home » SQL & PL/SQL » SQL & PL/SQL » How to split one column with dates into two with periods (from-to) (ORACLE DB)
How to split one column with dates into two with periods (from-to) [message #675563] Sat, 06 April 2019 09:37 Go to next message
chrisk4@wp.pl
Messages: 7
Registered: April 2019
Junior Member
Hello,

I'm very 'green' in oracle so forgive for obvious question but i've got problem with split one column into periods (for each ID)



My source table look like this:



  

ID	DATE	SEL1	SEL2	SEL3	SEL3	SEL4	SEL5	SEL6	SEL7	SEL8
100095	2004-07-01	N	N	N	N	T	N	N	N	N
100095	2005-01-01	N	N	N	N	N	N	N	N	N
100096	2004-07-01	T	T	N	T	T	N	N	N	N
100096	2004-08-01	N	N	N	N	N	N	N	N	N
100097	2004-07-01	N	N	N	N	T	N	N	N	N
100097	2004-08-01	N	N	N	N	N	N	N	N	N
100098	2004-07-01	N	N	N	N	T	N	N	N	N
100098	2004-08-01	N	N	N	N	N	N	N	N	N
 

And want to archieve this result:

 

  

ID	DATE_FROM	DATE_TO	SEL1	SEL2	SEL3	SEL3	SEL4	SEL5	SEL6	SEL7	SEL8
100095	2004-07-01	2004-12-31	N	N	N	N	T	N	N	N	N
100095	2005-01-01		N	N	N	N	N	N	N	N	N
100096	2004-07-01	2004-07-31	T	T	N	T	T	N	N	N	N
100096	2004-08-01		N	N	N	N	N	N	N	N	N
100097	2004-07-01	2004-07-31	N	N	N	N	T	N	N	N	N
100097	2004-08-01		N	N	N	N	N	N	N	N	N
100098	2004-07-01	2004-07-31	N	N	N	N	T	N	N	N	N
100098	2004-08-01		N	N	N	N	N	N	N	N	N

In Above example i've got 2 periods but it could be more - periods for each ID should start and end without gaps - last value is empty (DATE_TO) (alternatively i would like to know how to put value in last DATE_TO)



Thanks in advance for reply

Regards

*BlackSwan added {code} tags. Please do so your self in the future.

[Updated on: Sat, 06 April 2019 09:57] by Moderator

Report message to a moderator

Re: How to split one column with dates into two with periods (from-to) [message #675565 is a reply to message #675563] Sat, 06 April 2019 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 66936
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to get what you want.

Have a look at LAG/LEAD functions.

Re: How to split one column with dates into two with periods (from-to) [message #675566 is a reply to message #675565] Sat, 06 April 2019 10:35 Go to previous messageGo to next message
chrisk4@wp.pl
Messages: 7
Registered: April 2019
Junior Member
Thanks Michel Smile

LEAD function almost do the job.
However in last period I got date when it should be null.

My script:

select distinct ID,DATE as date_from, (lead(DATE,1) over (order by ID))-1 as date_to,

How to achieve null in last row ?

Regards

PS.
Sorry for my unformated post i'm after night shift Wink
Re: How to split one column with dates into two with periods (from-to) [message #675567 is a reply to message #675566] Sat, 06 April 2019 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 66936
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 06 April 2019 16:44

...
Also always post your Oracle version, with 4 decimals, as solution depends on it.
...
With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to get what you want.
...
Re: How to split one column with dates into two with periods (from-to) [message #675568 is a reply to message #675567] Sat, 06 April 2019 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 66936
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And I don't see the purpose of DISTINCT as you already get only distinct rows.

Re: How to split one column with dates into two with periods (from-to) [message #675569 is a reply to message #675566] Sat, 06 April 2019 10:42 Go to previous messageGo to next message
chrisk4@wp.pl
Messages: 7
Registered: April 2019
Junior Member
Sorry again - results of the query are unacceptable - so i have to reedit this Sad

Regards
Re: How to split one column with dates into two with periods (from-to) [message #675570 is a reply to message #675569] Sat, 06 April 2019 11:03 Go to previous message
chrisk4@wp.pl
Messages: 7
Registered: April 2019
Junior Member
Finally Smile

select ID,DATE as date_from, lead(DATE,1) over (partition by ID order by date)-1 as date_to

Thanks for help Smile

Regards
Previous Topic: Storing only DATE I timestamp data type column
Next Topic: Import/Entry from txt file
Goto Forum:
  


Current Time: Tue Apr 07 13:19:29 CDT 2020