ajun wrote:
>
> Dear pakar2 oracle.
>
> Aq lg bikin report di report builder yg isinya mencari Qty dr minggu
> -1 s/d minggu ke-5 setiap bulannya dlm setahun.
> Perhitungan per minggu ditentukan dr hari Senin s/d minggu ( bukan tgl
> 1 s/d tgl 7 sbg minggu pertama).
> Jadi kalo kita liat kalender januari 2009 maka minggu ke-1 adalah 1- 4
> jan 2009, minggu ke-2 : 5 - 11 jan 2009 dst.
>
> Mohon bantuannya dr pakar2 oracle.
>
> Thanks atas pencerahannya.
>
> AJ.
>
>
SQL> SELECT ROWNUM week, date_from, date_to FROM (
2 SELECT MIN(d_day) date_from, MAX(d_day) date_to FROM (
3 SELECT d_day, NEXT_DAY(d_day-7,'MON') next_day FROM (
4 SELECT TRUNC(SYSDATE,'MM') + ROWNUM - 1 d_day FROM ALL_OBJECTS
5 WHERE ROWNUM <= 31)
6 WHERE d_day <= LAST_DAY(SYSDATE))
7 GROUP BY next_day
8 ORDER BY next_day);
WEEK DATE_FROM DATE_TO
---------- ---------------------- ----------------------
1 04/01/2009 12:00:00 AM 04/05/2009 12:00:00 AM
2 04/06/2009 12:00:00 AM 04/12/2009 12:00:00 AM
3 04/13/2009 12:00:00 AM 04/19/2009 12:00:00 AM
4 04/20/2009 12:00:00 AM 04/26/2009 12:00:00 AM
5 04/27/2009 12:00:00 AM 04/30/2009 12:00:00 AM
CMIIW,
AH
