Monday, 7 October 2013

Query Oracle for min date and max date per weeks using parameter Month

For Sunday is the first day of weeks. 

Query:

select WOM "Minggu ke -",to_number(min(to_char(dt,'dd'))) "tanggal Awal",to_number(max(to_char(dt,'dd'))) "tanggal akhir" from 
(
select to_date(:vmonth,'mmyy'),rownum,to_date(:vmonth,'mmyy')+rownum-1 dt,to_char(to_date(:vmonth,'mmyy'),'D') "first days",to_char(to_date(:vmonth,'mmyy'),'D')+rownum-2 "days + rownum", floor((to_char(to_date(:vmonth,'mmyy'),'D')+rownum-2)/7)+1 "WOM"    
from all_objects
where rownum <= last_day(to_date(:vmonth,'mmyy'))-to_date(:vmonth,'mmyy')+1
)
group by WOM
order by WOM

Example, with parameter = '1013' => October 2013.



For Monday is the first day of weeks. 

Query:

select WOM "Minggu ke -",to_number(min(to_char(dt,'dd'))) "tanggal Awal",to_number(max(to_char(dt,'dd'))) "tanggal akhir" from 
(
select to_date(:vmonth,'mmyy'),rownum,to_date(:vmonth,'mmyy')+rownum-1 dt,to_char(to_date(:vmonth,'mmyy'),'D') "first days",to_char(to_date(:vmonth,'mmyy'),'D')+rownum-2 "days + rownum", floor((to_char(to_date(:vmonth,'mmyy'),'D')+rownum-3)/7)+1 "WOM"    
from all_objects
where rownum <= last_day(to_date(:vmonth,'mmyy'))-to_date(:vmonth,'mmyy')+1
)
group by WOM
order by WOM

Example, with parameter = '1013' => October 2013.


Enjoyy... :D


0 comments:

Post a Comment