REM Program     : snap_matrix_daily.sql
REM Purpose     : Display the first snapshot ids for every 2 hours for the last 45 days in a daily 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_date   FORMAT A10     HEADING 'Run Date'
COLUMN snap_00     FORMAT 99999   HEADING '00:00'
COLUMN snap_02     FORMAT 99999   HEADING '00:02'
COLUMN snap_04     FORMAT 99999   HEADING '00:04'
COLUMN snap_06     FORMAT 99999   HEADING '00:06'
COLUMN snap_08     FORMAT 99999   HEADING '00:08'
COLUMN snap_10     FORMAT 99999   HEADING '00:10'
COLUMN snap_12     FORMAT 99999   HEADING '00:12'
COLUMN snap_14     FORMAT 99999   HEADING '00:14'
COLUMN snap_16     FORMAT 99999   HEADING '00:16'
COLUMN snap_18     FORMAT 99999   HEADING '00:18'
COLUMN snap_20     FORMAT 99999   HEADING '00:20'
COLUMN snap_21     FORMAT 99999   HEADING '00:22'

SELECT TO_CHAR(snap_time, 'MM/DD/YY') snap_date,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '00', snap_id, NULL)) snap_00,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '02', snap_id, NULL)) snap_02,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '04', snap_id, NULL)) snap_04,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '06', snap_id, NULL)) snap_06,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '08', snap_id, NULL)) snap_08,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '10', snap_id, NULL)) snap_10,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '12', snap_id, NULL)) snap_12,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '14', snap_id, NULL)) snap_14,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '16', snap_id, NULL)) snap_16,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '18', snap_id, NULL)) snap_18,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '20', snap_id, NULL)) snap_20,
       MAX(DECODE(TO_CHAR(snap_time, 'hh24'), '22', snap_id, NULL)) snap_22
FROM stats$snapshot
WHERE snap_time > sysdate - 45
GROUP BY TO_CHAR(snap_time, 'MM/DD/YY'), TO_CHAR(snap_time, 'YY/MM/DD')
ORDER BY TO_CHAR(snap_time, 'YY/MM/DD') desc
/