Display rows for the past 4 week

[brusko_zaragoza]

Hi Experts,

Need your help. Below is the result generated by the script.
Currently, the script does display all the rows for the year 2009 and 2008.

Would appreciate your help if you can modify the script for me to display only the rows for the past 4 week. Please observed the generated RESULT, on START_DATE column, the latest date is 04-4-2009, next 04-3-2009, 04-2-2009 and other rows within the past 2 weeks (Pls find below expected result).

[B]RESULT:[/B]
 
START_DAT   NUM_LOGS MBYTES    
--------- ---------- ----------
01-1-2009        224      22400
01-2-2009        206      20600
01-3-2009        212      21200
01-4-2009        339      33900
01-5-2009        126      12600
02-1-2009        257      25700
02-2-2009        293      29300
02-3-2009        295      29500
02-4-2009        254      25400
03-1-2009        209      20900
03-2-2009        272      27200
03-3-2009        275      27500
03-4-2009        243      24300
03-5-2009        127      12700
04-1-2009        226      22600
04-2-2009        248      24800
04-3-2009        235      23500
04-4-2008        267      26700
04-4-2009         94       9400
04-5-2008        125      12500
05-1-2008        325      32500
 
START_DAT   NUM_LOGS MBYTES    
--------- ---------- ----------
05-2-2008        330      33000
05-3-2008        313      31300
05-4-2008        303      30300
05-5-2008        155      15500
06-1-2008        381      38100
06-2-2008        197      19700
06-3-2008        263      26300
06-4-2008        238      23800
06-5-2008         75       7500
07-1-2008        236      23600
07-2-2008        195      19500
07-3-2008        222      22200
07-4-2008        264      26400
07-5-2008        129      12900
08-1-2008       1016     101600
08-2-2008        734      73400
08-3-2008        432      43200
08-4-2008        406      40600
08-5-2008        130      13000
09-1-2008        250      25000
09-2-2008        225      22500
 
START_DAT   NUM_LOGS MBYTES    
--------- ---------- ----------
09-3-2008        212      21200
09-4-2008        228      22800
09-5-2008        121      12100
10-1-2008        232      23200
10-2-2008        318      31800
10-3-2008        248      24800
10-4-2008        288      28800
10-5-2008        158      15800
11-1-2008        303      30300
11-2-2008        309      30900
11-3-2008        261      26100
11-4-2008        290      29000
11-5-2008         45       4500
12-1-2008        282      28200
12-2-2008        327      32700
12-3-2008        240      24000
12-4-2008        149      14900
12-5-2008         71       7100
 
60 rows selected.

SCRIPT:

 
SELECT   start_date, num_logs,
         TO_CHAR (ROUND (num_logs * (vl.BYTES / (1024 * 1024)), 2),
                  '999999999'
                 ) AS mbytes
    FROM (SELECT   TO_CHAR (vlh.first_time, 'MM-W-YYYY') AS start_date,
                   COUNT (vlh.thread#) num_logs
              FROM v$log_history vlh
          GROUP BY TO_CHAR (vlh.first_time, 'MM-W-YYYY')) log_hist,
         (SELECT DISTINCT BYTES
                     FROM v$log) vl
ORDER BY log_hist.start_date;

EXPECTED RESULT:
 
START_DAT   NUM_LOGS MBYTES    
--------- ---------- ----------
04-4-2009         94       9400
04-3-2009        235      23500
04-2-2009        248      24800
04-1-2009        226      22600
.
.
rows past 2 weeks

[tyro]

hmmm ok let me see, this should work (not tested)

SELECT   to_char(to_date(start_date),'MM-W-YYYY') start_date, num_logs,
         TO_CHAR (ROUND (num_logs * (vl.BYTES / (1024 * 1024)), 2),
                  '999999999'
                 ) AS mbytes
    FROM (SELECT   TO_CHAR (vlh.first_time, 'DD-MON-YYYY') AS start_date,
                   COUNT (vlh.thread#) num_logs
              FROM v$log_history vlh
          GROUP BY TO_CHAR (vlh.first_time, 'DD-MON-YYYY')) log_hist,
         (SELECT DISTINCT BYTES
                     FROM v$log) vl
   WHERE to_date(start_date,'DD-MON-YYYY') > (SYSDATE - 28)
ORDER BY log_hist.start_date;

You can change the SYSDATE-28 for any range you like.