1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Display rows for the past 4 week

Discussion in 'SQL PL/SQL' started by brusko_zaragoza, Apr 24, 2009.

  1. brusko_zaragoza

    brusko_zaragoza Active Member

    Messages:
    3
    Likes Received:
    1
    Trophy Points:
    65
    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).

    Code (Text):

    [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:
    Code (SQL):


    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;
     
    Code (Text):

    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
     
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hmmm ok let me see, this should work (not tested)

    Code (SQL):
    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.