REM Program     : snap_matrix_weekly.sql
REM Purpose     : Display the first snapshot ids for the last 45 days in a weekly format
REM Author      : Daniel W. Fink OptimalDBA.com
REM Created     : January 13, 2007
REM Update      : 
REM Parameters  : Not Used
REM Exit Codes  : Not Used
REM Comments    : 
REM Disclaimer  : No warranty is provided for any use of the script, statements or logic included. 
REM               This script, statements and logic are for personal use only and may not be included as part of a commercial product. 
REM               This comment block and all lines above must be included.

COLUMN snap_week    FORMAT A10     HEADING 'Week of'
COLUMN snap_sun     FORMAT 99999   HEADING 'Sunday'
COLUMN snap_mon     FORMAT 99999   HEADING 'Monday'
COLUMN snap_tue     FORMAT 99999   HEADING 'Tuesday'
COLUMN snap_wed     FORMAT 99999   HEADING 'Wednesday'
COLUMN snap_thu     FORMAT 99999   HEADING 'Thursday'
COLUMN snap_fri     FORMAT 99999   HEADING 'Friday'
COLUMN snap_sat     FORMAT 99999   HEADING 'Saturday'

WITH snap_weekly AS
   ( SELECT (TRUNC(snap_time) - TO_NUMBER(TO_CHAR(snap_time, 'D'))+1) snap_week_start,
            TRUNC(snap_time) snap_date,
            MIN(snap_id) first_snap
     FROM stats$snapshot
     WHERE snap_time > sysdate - (45 + TO_NUMBER(TO_CHAR(sysdate, 'D'))+1)
     GROUP BY (TRUNC(snap_time) - TO_NUMBER(TO_CHAR(snap_time, 'D'))+1), TRUNC(snap_time)
   )
SELECT snap_week_start snap_week,
       MAX(DECODE(TO_CHAR(snap_date,'D'),1,first_snap, null)) snap_sun,
       MAX(DECODE(TO_CHAR(snap_date,'D'),2,first_snap, null)) snap_mon,
       MAX(DECODE(TO_CHAR(snap_date,'D'),3,first_snap, null)) snap_tue,
       MAX(DECODE(TO_CHAR(snap_date,'D'),4,first_snap, null)) snap_wed,
       MAX(DECODE(TO_CHAR(snap_date,'D'),5,first_snap, null)) snap_thu,
       MAX(DECODE(TO_CHAR(snap_date,'D'),6,first_snap, null)) snap_fri,
       MAX(DECODE(TO_CHAR(snap_date,'D'),7,first_snap, null)) snap_sat
FROM snap_weekly
GROUP BY snap_week_start
ORDER BY snap_week_start
/