Home » SQL & PL/SQL » SQL & PL/SQL » Hi Experts,
Hi Experts, [message #243613] Fri, 08 June 2007 00:54 Go to next message
Messages: 64
Registered: May 2005

Help me with my issue.

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: Hi Experts, [message #243617 is a reply to message #243613] Fri, 08 June 2007 01:08 Go to previous message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Follow the previous post (Duplicate/Split rows per week).
Give a meaningful name to your topic.
This one is closed.

Previous Topic: Common Rows
Next Topic: varray out parameter in a procedure
Goto Forum:

Current Time: Fri Aug 18 22:45:45 CDT 2017

Total time taken to generate the page: 0.14005 seconds