Home » SQL & PL/SQL » SQL & PL/SQL » Split the dates into weeks
Split the dates into weeks [message #243619] Fri, 08 June 2007 01:25 Go to next message
Messages: 64
Registered: May 2005
I have following table and data.

Date1 Date2 val
-------- --------- ---
5/1/2007 5/15/2007 42
5/16/2007 6/15/2007 43

I am trying to get the put as mentioned below. o/p should display the value per week. Week starts on Sat(7) and ends on Friday(6). All i need to do is break into display value per week.

required Output:
From To val
-------- --------- ---
5/1/2007 5/4/2007 42
5/5/2007 5/11/2007 42
5/12/2007 5/15/2007 42
5/16/2007 5/18/2007 43
5/19/2007 5/25/2007 43
5/26/2007 6/1/2007 43
6/2/2007 6/8/2007 43
6/9/2007 6/15/2007 43

select date1,
lag(next_day(date1,'friday')) over(order by date1) nxt_wk1,
from dates;

I have got the output:
From To Val
---- --------- ---
5/16/2007 5/4/2007 42

similar query:
select date1, date2, val,
lag(next_day(date1,'friday')) over(order by(next_day(date1,'friday'))) nx11,
lag(next_day(date2,'friday')) over(order by(next_day(date2,'friday'))) nx12,
lead(next_day(date1,'friday')) over(order by(next_day(date1,'friday'))) nx21,
lead(next_day(date2,'friday')) over(order by(next_day(date2,'friday'))) nx22
from dates;

I hope it is working fine for a single record. If yes, it has to apply for all the rows in a table using single sql statement. OR I would appreciate if some one could help me in their own way.I need lots of inputs from experts regarding this. So that i can understand better and i can learn more tech stuff from the forum.

Thanks in advance,
Re: Split the dates into weeks [message #243630 is a reply to message #243619] Fri, 08 June 2007 02:12 Go to previous messageGo to next message
Messages: 7880
Registered: March 2000
Senior Member
Stop creating threads for the same problem
Continue in your original thread.
Re: Split the dates into weeks [message #243648 is a reply to message #243619] Fri, 08 June 2007 03:42 Go to previous message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer is there: http://www.orafaq.com/forum/t/83222/102589/

Previous Topic: How can i findout column name?
Next Topic: Compute complex SQL query with decode
Goto Forum:

Current Time: Sat Oct 22 09:20:46 CDT 2016

Total time taken to generate the page: 0.08953 seconds