Changeset 19

Show
Ignore:
Timestamp:
09/04/08 17:36:40 (6 years ago)
Author:
robert
Message:

fix errors caused by adding new tables to schema. fix duplicate rows in monthly summary output

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • trunk/table_growth/otools.summarize_table_growth.sql

    r17 r19  
    99IF to_char(current_date,'dd') <> '01' THEN 
    1010    insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
    11     select a.table_owner, a.schema_name, a.table_name, a.actual_size, a.actual_size-b.actual_size AS table_growth, 1, a.capture_time 
     11    select a.table_owner, a.schema_name, a.table_name, a.actual_size, a.actual_size-coalesce(b.actual_size,0) AS table_growth, 1, a.capture_time 
    1212    from otools.table_growth a  
    1313        left join otools.table_growth b  
     
    2222IF to_char(current_date,'dd') = '01' THEN 
    2323    insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
    24     select a.table_owner, a.schema_name, a.table_name, actual_size, sum(growth_size), 2, (current_date - '1 month'::interval)  
     24    select a.table_owner, a.schema_name, a.table_name, max(actual_size), sum(growth_size), 2, (current_date - '1 month'::interval)  
    2525    from otools.table_growth a  
    2626    where sum_flag=1 and capture_time between (current_date - '1 month'::interval) and current_date