How To Get Week Number From specified date. [message #443277] |
Sun, 14 February 2010 15:27  |
hemalatha_81
Messages: 16 Registered: February 2010
|
Junior Member |
|
|
Hello All,
i am working on a sql query for grouping of data based on weeks.
However the Grouping of data is not based on the calender Week Which means For example.
From_date -10/01/2010
to_date -10/02/2010
If we are calculating the week on the from_date it will give you calender week.
from the above from_date (10/01/2010) it is week2 from the calender. .
What i require is (From_date+ 1st 7 days ) Should be the week1
(From_date+ 2nd 7 days ) should be week2.
Please look into the Below sample report data.
Report Output Should be
from_date week
10/01/2010 Week1
11/01/2010 week1
12/01/2010 week1
15/01/2010 week1
17/01/2010 week1
18/01/2010 week2
19/01/2010 week2
Please let me know your valuable inputs in the timely manner.
|
|
|
Re: How To Get Week Number From specified date. [message #443278 is a reply to message #443277] |
Sun, 14 February 2010 18:23  |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Why is it you refuse to follow Posting Guidelines?
Why is it you have not posted any actual SQL?
Below shows how to generate week numbers based upon date range.
You are free to manipulate, massage or mangle the SQL below to meet your specific requirements.
SQL> @cal5
SQL> select trunc((dte-to_date('&BEG_DATE','YYYY-MM-DD'))/7)+1 Week ,dte
2 from (select to_date('&&BEG_DATE','YYYY-MM-DD') +level-1 dte from dual
3 connect by level <= TO_DATE('&&END_DATE','YYYY-MM-DD')+1 - to_date('&&BEG_DATE','YYYY-MM-DD'))
4 /
old 1: select trunc((dte-to_date('&BEG_DATE','YYYY-MM-DD'))/7)+1 Week ,dte
new 1: select trunc((dte-to_date('2010-01-13','YYYY-MM-DD'))/7)+1 Week ,dte
old 2: from (select to_date('&&BEG_DATE','YYYY-MM-DD') +level-1 dte from dual
new 2: from (select to_date('2010-01-13','YYYY-MM-DD') +level-1 dte from dual
old 3: connect by level <= TO_DATE('&&END_DATE','YYYY-MM-DD')+1 - to_date('&&BEG_DATE','YYYY-MM-DD'))
new 3: connect by level <= TO_DATE('2010-01-31','YYYY-MM-DD')+1 - to_date('2010-01-13','YYYY-MM-DD'))
WEEK DTE
---------- ---------
1 13-JAN-10
1 14-JAN-10
1 15-JAN-10
1 16-JAN-10
1 17-JAN-10
1 18-JAN-10
1 19-JAN-10
2 20-JAN-10
2 21-JAN-10
2 22-JAN-10
2 23-JAN-10
WEEK DTE
---------- ---------
2 24-JAN-10
2 25-JAN-10
2 26-JAN-10
3 27-JAN-10
3 28-JAN-10
3 29-JAN-10
3 30-JAN-10
3 31-JAN-10
19 rows selected.
[Updated on: Sun, 14 February 2010 20:14] Report message to a moderator
|
|
|