SQL & PL/SQL

Hi Everyone,

I'm working on a procedure that is used to calculate data in weekly and monthly, currently I can use EXTRACT function to extract the week number/month from given date (as the SQL below). The week number/month, extracted from previous step, will be put into a loop for calculating purpose. And we need to determine the start date and end date of specific week/month, could you please share me how get those date from given week number or month ?

SELECT min(my_datetime) INTO Zmin_date_time FROM audit_event;

--Minimum year in database

SELECT EXTRACT( YEAR FROM MIN(my_datetime) ) INTO Zmin_year FROM audit_event;

--Minimum month in database

SELECT EXTRACT( MONTH FROM MIN(my_datetime) ) INTO Zmin_month FROM audit_event;

--Minimum week in database

SELECT TO_NUMBER(to_timestamp(MIN(my_datetime),'DD-MON-RR HH.MI.SS.FF AM'), 'WW') INTO Zmin_week FROM audit_event;

Zcurr_date_time := sysdate;

--Current year

SELECT EXTRACT( YEAR FROM MIN(Zcurr_date_time) ) INTO Zcurr_year FROM dual;

--Current month

SELECT EXTRACT( MONTH FROM MIN(Zcurr_date_time) ) INTO Zcurr_month FROM dual;

--Current week

SELECT TO_NUMBER(to_timestamp(MIN(Zcurr_date_time),'DD-MON-RR HH.MI.SS.FF AM'), 'WW') INTO Zcurr_week FROM dual;

WHILE Zmin_week <= Zcurr_week

BEGIN

Zstart_date_of_week = ???

Zend_date_of_week = ???

/*Do Some Logic*/

SELECT Zmin_week = Zmin_week + 1

END

WHILE Zmin_month <= Zcurr_month

BEGIN

Zstart_date_of_month = ???

Zend_date_of_month = ???

/*Do Some Logic*/

SELECT Zmin_month = Zmin_month + 1

END

For example: Assuming that I get the week number of current date (March 30, 2016), and the result should be 14. And then I increase the week number up to 15, how can I determine the start and end date of week for week number 15 ?

Thanks

Khoa Tran