Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    7

    Unanswered: Display rows for the last 4 week

    Hi Experts,

    Need your help. I need to display rows for the last past 4 week.
    Please find below script and results. Thank in advance.


    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;


    RESULT:

    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 269 26900
    04-4-2009 92 9200
    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    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;
    >I need to display rows for the last past 4 week.
    How is "last 4 weeks" quantified precisely?

    Some flavor of WHERE clause will be needed.
    Show us what you tried.


    >Please find below script and results.
    What do you expect to be done with the SQL & results?
    Last edited by anacedent; 04-23-09 at 23:13.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2009
    Posts
    7

    Display rows the past 4 weeks

    Thanks for the prompt reply.

    Currently the script does displays all the rows in 2009, also including rows of 2008.

    Using the same script, we need to modify the script and filter (display) rows starting from the latest START_DATE (which is 04-4-2009 mentioned in the result) and display the last 4 week of rows starting from 04-4-2009.

    Expected result is starting from 04-4-2009 is:

    START_DAT NUM_LOGS MBYTES
    --------- ---------- ----------
    04-4-2009 92 9200
    04-3-2009 235 23500
    04-2-2009 248 24800
    04-1-2009 226 22600
    .
    .
    .

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How is "last 4 weeks" quantified precisely?
    Some flavor of WHERE clause will be needed.
    Show us what you tried.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •