Archives of the TeradataForum

Message Posted: Fri, 09 Aug 2002 @ 10:13:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

don't know if the following is of use?:

Regards,

Jon Downs

/*
 * OLD STATISTICS REPORT
 */

 SELECT DBNAME
      , TBNAME
      , COLNM
      , CASE
          WHEN ZEROIFNULL(COLDATE) > ZEROIFNULL(ALTDATE)
          THEN ZEROIFNULL(COLDATE)
          ELSE ZEROIFNULL(ALTDATE)
          END AS COLLECTION_DATE
 FROM
(
 SELECT C.DATABASENAMEI AS DATABASENAME
      , B.TVMNAMEI AS TABLENAME
      , A.FIELDNAME AS COLUMNNAME
      , CAST((CASE
              WHEN SUBSTR(FIELDSTATISTICS,1,1)= 'D2'XB
              THEN '2002-'
              WHEN SUBSTR(FIELDSTATISTICS,1,1)= 'D1'XB
              THEN '2001-'
              WHEN SUBSTR(FIELDSTATISTICS,1,1)= 'D0'XB
              THEN '2000-'
              WHEN SUBSTR(FIELDSTATISTICS,1,1)= 'CF'XB
              THEN '1999-'
              WHEN SUBSTR(FIELDSTATISTICS,1,1)= 'CE'XB
              THEN '1998-'
              WHEN SUBSTR(FIELDSTATISTICS,1,1)= 'CD'XB
              THEN '1997-'
              WHEN SUBSTR(FIELDSTATISTICS,1,1)= 'CC'XB
              THEN '1996-'
              ELSE NULL
              END)
       ||   (CASE
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '01'XB
              THEN '01-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '02'XB
              THEN '02-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '03'XB
              THEN '03-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '04'XB
              THEN '04-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '05'XB
              THEN '05-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '06'XB
              THEN '06-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '07'XB
              THEN '07-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '08'XB
              THEN '08-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '09'XB
              THEN '09-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '0A'XB
              THEN '10-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '0B'XB
              THEN '11-'
              WHEN SUBSTR(FIELDSTATISTICS,3,1)= '0C'XB
              THEN '12-' ELSE 'XX-' END)
       ||   (CASE
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '01'XB
              THEN '01'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '02'XB
              THEN '02'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '03'XB
              THEN '03'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '04'XB
              THEN '04'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '05'XB
              THEN '05'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '06'XB
              THEN '06'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '07'XB
              THEN '07'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '08'XB
              THEN '08'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '09'XB
              THEN '09'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '0A'XB
              THEN '10'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '0B'XB
              THEN '11'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '0C'XB
              THEN '12'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '0D'XB
              THEN '13'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '0E'XB
              THEN '14'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '0F'XB
              THEN '15'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '10'XB
              THEN '16'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '11'XB
              THEN '17'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '12'XB
              THEN '18'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '13'XB
              THEN '19'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '14'XB
              THEN '20'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '15'XB
              THEN '21'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '16'XB
              THEN '22'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '17'XB
              THEN '23'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '18'XB
              THEN '24'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '19'XB
              THEN '25'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '1A'XB
              THEN '26'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '1B'XB
              THEN '27'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '1C'XB
              THEN '28'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '1D'XB
              THEN '29'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '1E'XB
              THEN '30'
              WHEN SUBSTR(FIELDSTATISTICS,4,1)= '1F'XB
              THEN '31'
              ELSE 'XX'
              END)AS DATE) AS COLLECTIONDATE
      ,CAST(SUBSTR(CAST(A.LASTALTERTIMESTAMP AS CHAR(32)),1,10) AS DATE) AS LASTALTER
      ,DATE - COLLECTIONDATE AS FROMCURRENT
      ,LASTALTER - COLLECTIONDATE AS FROMALTER

 FROM DBC.TVFIELDS A
    , DBC.TVM B
    , DBC.DBASE C

 WHERE  A.TABLEID = B.TVMID
 AND    B.TABLEKIND = 'T'
 AND    B.DATABASEID = C.DATABASEID
 AND    A.FIELDSTATISTICS IS NOT NULL
        ) AS DERIVED_TAB
       (
        DBNAME
       ,TBNAME
       ,COLNM
       ,COLDATE
       ,ALTDATE
       ,FROMCURR
       ,FROMALT
        )
 WHERE COLDATE < 1020501 OR COLDATE IS NULL
 ORDER BY 1, 2
;

	<Prev	Next>		<<First	<Prev	Next>	Last>>