Time Difference in Hours in Teradata

In Tearadata time difference calculation is bit tricky compared to SQL Command DateDiff(HH, Date1, Date2)

So here is Syntax/ Code for calculating Time difference in Hours between Today's date and EventDate

Syntax:

SELECT

CAST( CURRENT_DATE  AS TIMESTAMP(6))   -- Today's Date

, EventDate -- Date you want to check with

,(CAST(EventDate AS TIMESTAMP(6) )- CAST(CURRENT_DATE AS TIMESTAMP(6) )) DAY(4) TO MINUTE AS DHM -- Time difference in Days Hours:Minutes

,24* EXTRACT(DAY FROM (DHM))   -- Hours calculation by Days

,EXTRACT(HOUR FROM (DHM)) -- extracting extra hours from  apart from complete day

,24* EXTRACT(DAY FROM (DHM))   + EXTRACT(HOUR FROM (DHM)) AS HoursDiff -- Exact Time difference in Hours

FROM Database.TableName

Usage of NULL and NOT Operator

The word NULL is a reserved word in any SQL. As you know NULL represents nothing, more accurately missing data or empty cell values. A comparison with NULL always returns NULL. To find out NULL values we can either have a condition with a logical operator with two NOT operators or we can use COALESE function.

Usage of NOT operator

It is a wondering truth that sometimes in life, we do NOT want something. And to satisfy this need in SQL, there is a NOT operator provided. Just prefix this before any comparison operator and you will get what you do NOT want. Here is the syntax.

SELECT {*|<col1>}, <col2>, <col3>…

FROM <TABLE name>

WHERE <COLUMN name> NOT <OPERATOR> <another COLUMN name OR constant VALUE> 

IN, ALL, ANY Oerator in Teradata

IN operator to have multiple values in the ‘WHERE’ clause. This is an alternative use of having multiple OR. Here is the syntax.

SELECT {*|<col1>}, <col2>, <col3>…

FROM <TABLE name>

WHERE <COLUMN name> [NOT] IN (<list OF VALUES>)

For example, to select employees from a list of department numbers, use the following SQL.

SELECT first_name, last_name

FROM employee

WHERE dept_no IN (100,250,368)

Remember that a result set would be returned even if there is only on matching dept_no found. As mentioned before if you have a NULL operator in the list then there would be no rows returned. To find out rows not matching the list of values, prefix the NOT operator before IN as follows;

SELECT first_name, last_name

FROM employee

WHERE dept_no NOT IN (210,652,555,871)

If you want to use another alternative to this, you can go for ANY, but this has to be used with a comparison operator as follows.

SELECT {*|<col1>}, <col2>, <col3>…

FROM <TABLE name>

WHERE <COLUMN name> = ANY (<list OF VALUES>)

To negate you have to swap the word with ALL and prefix with the NOT operator before the equal sign,

 i.e. NOT = ALL <list of values>

Rank vs Row_Number in Teradata

Rank vs Row_Number in Teradata

Anyone working on Teradata would be aware of the two most common OLAP functions in Teradata which are used for common use i.e.

1.      Rank()

2.      Row_Number()

  In one of my posts I have mentioned about how to generate sequence number (ex. Surrogate Key) using Row_Number.   Today let us find out the difference in usage of rank and row number functions by taking following scenario on tables (ProductSales)

SELECT    Product, DateOfSale, SalesAmount,

RANK() OVER (ORDER  BY SalesAmount DESC)  ,  -- RANK function

ROW_NUMBER() OVER (ORDER     BY SalesAmount DESC) -- ROW_NUMBER function

FROM   ProductSales

The result set is as follows:

In the result set, we can observe the following,  

1.       Rows 1 and 2 have same rank assigned  since that have same SalesAmount value

2.       Rows 1 and 2 have different row numbers assigned even though same SalesAmount value

3.       Row 3 has rank “3” assigned as ranking positions

Hence , rows are always assigned an incremented sequence number with ROW_NUMBER.

Rows are always assigned the same number with RANK function.

List of tables and columns in Teradata database

How to extract list of columns, Tables in database (using Teradata SQL)
Syntax:

SELECT DatabaseName, TableName, ColumnName, ColumnFormat, ColumnType, ColumnLength, Nullable             

,(CASE

WHEN columntype='CV' THEN 'VARCHAR'

WHEN columntype='DA' THEN 'DATE'

WHEN columntype='I' THEN 'INTEGER'

WHEN columntype='CF' THEN 'CHAR'

WHEN columntype='F' THEN 'FLOAT'

WHEN columntype='I1' THEN 'BYTEINT'

WHEN columntype='I2' THEN 'SMALLINT'

WHEN columntype='I8' THEN 'BIGINT'

WHEN columntype='BO' THEN 'BLOB'

WHEN columntype='CO' THEN 'CLOB'

WHEN columntype='TS' THEN 'TIMESTAMP'

WHEN columntype='BF' THEN 'BYTE'

WHEN columntype='SZ' THEN 'TIMESTAMP W/ZONE'

WHEN columntype='BV' THEN 'VARBYTE'

WHEN columntype='DM' THEN 'INVERVAL DAY TO MINUTE'

WHEN columntype='D' THEN 'DECIMAL'

ELSE columntype END) 

AS DataType

FROM DBC.COLUMNS

WHERE databasename = 'DatabaseName'