/* 
Program     : awr_snap_wkly_matrix.sql
Purpose     : List AWR Snapshots in a weekly matrix
Author      : Daniel W. Fink OptimalDBA.com
Created     : March 17, 2011
Update      : 
Parameters  : &1 DBID
            : &2 Instance ID
            : Called from cust_awr_10g.sql
Exit Code   : Not Used
Comments    : 
Disclaimer  : No warranty is provided for any use of the script, statements or logic included. 
              This script, statements and logic are for personal use only and may not be included 
              as part of a commercial product. 
              Please address any comments to script_feedback@optimaldba.com
              This comment block and all lines above must be included.

*/

COLUMN sort_ord                        NOPRINT
COLUMN snap_week                       NOPRINT

DEFINE awr_dbid = &1
DEFINE awr_instid = &2

SET HEADING off PAGESIZE 0
TTITLE off

PROMPT *** Snapshots in AWR repository for Database &usr_db_name Instance &usr_inst_name
PROMPT *** Weekly by hour (intermediate snapshots not listed)

SELECT     DISTINCT 001 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL	         hour_of_day
         , 'Sunday'	 sunday_snapid
         , 'Monday'	 monday_snapid
         , 'Tuesday'	 tuesday_snapid
         , 'Wednesday'	 wednesday_snapid
         , 'Thursday'	 thursday_snapid
         , 'Friday'	 friday_snapid
         , 'Saturday'	 saturday_snapid
FROM       sys.wrm$_snapshot
UNION ALL
SELECT     010 sort_ord
         , TO_CHAR(s.first_sunday, 'YYYYMMDD')	snap_week
         , NULL hour_of_day
         , TO_CHAR(s.first_sunday,   'MM/DD/YY') sunday_snapid
         , TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
         , TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
         , TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
         , TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
         , TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
         , TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM       ( SELECT     NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
             FROM       sys.wrm$_snapshot
             WHERE      dbid = &awr_dbid
               AND      instance_number = &awr_instid
           ) s
UNION ALL
SELECT     011 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL hour_of_day
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
GROUP BY   TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING     MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT     DISTINCT 020 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , '---------'   hour_of_day
         , '---------'	 sunday_snapid
         , '---------'	 monday_snapid
         , '---------'	 tuesday_snapid
         , '---------'	 wednesday_snapid
         , '---------'	 thursday_snapid
         , '---------'	 friday_snapid
         , '---------'	 saturday_snapid
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
UNION ALL
SELECT     030 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
GROUP BY   TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
         , TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT     DISTINCT 999 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL	 hour_of_day
         , NULL	 sunday_snapid
         , NULL	 monday_snapid
         , NULL	 tuesday_snapid
         , NULL	 wednesday_snapid
         , NULL	 thursday_snapid
         , NULL	 friday_snapid
         , NULL	 saturday_snapid
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
ORDER BY   snap_week
         , sort_ord
         , hour_of_day
/

SET HEADING ON