Lebih simple menggunakan function to_char(tgl, 'W') atau 'w' saya lupa, silahkan baca detailnya di help developer. nantinya akan mengeluarkan nilai minggu ke-sekian dari satu tahun.
select to_char(tgl, 'W'), sum(qty) from tbl_data where tgl between x1 and x2 group by to_char(tgl, 'W'); ~roes~ ________________________________ From: ajun <[email protected]> To: [email protected] Sent: Friday, April 3, 2009 1:00:55 PM Subject: Re: [indo-oracle] menentukan minggu dlm setahun Terima kasih atas bantuannya Pak. ----- Original Message ----- From: Awaluddin Hamid To: indo-oracle@ yahoogroups. com Sent: Thursday, April 02, 2009 5:30 PM Subject: Re: [indo-oracle] menentukan minggu dlm setahun 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 ------------ --------- --------- --------- --------- --------- - No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.238 / Virus Database: 270.11.37/2036 - Release Date: 04/01/09 21:02:00 [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
