case statements,oracle functions

1)Casting To date format
To take only date from timestamp 2010-03-05 22:25:56  To 3/5/2010
--------------------------------------------------------
CAST ( CAST (colname AS TIMESTAMP ) AS DATE )
Eg:
CAST ( CAST ("Issues"."Date Identified" AS TIMESTAMP ) AS DATE )

2)TimestampDiff function
Days comparison Current date- future date/Past Date
2009-6-26 22:05:10   2009-6-22 11:05:10   = 4 days
---------------------
TimeStampDiff(SQL_TSI_DAY, CURRENT_DATE, CAST ( CAST (colname AS TIMESTAMP ) AS DATE ))
Eg:
TimeStampDiff(SQL_TSI_DAY, CURRENT_DATE, CAST ( CAST ("Issues"."Date Identified" AS TIMESTAMP ) AS DATE ))

3)TimestampDiff function
Days Comparison datecol1- datecol2
2009-6-26 22:05:10   2009-6-22 11:05:10   = 4 days
---------------------
TimeStampDiff(SQL_TSI_DAY,CAST(CAST(col1 AS TIMESTAMP)AS DATE),CAST(CAST(col2 AS TIMESTAMP) AS DATE))
Eg:
TimeStampDiff(SQL_TSI_DAY,CAST(CAST("Dates"."T2 Actual Date" AS TIMESTAMP) AS DATE),CAST(CAST("Dates"."T2 Planned Date" AS TIMESTAMP) AS DATE)) END





4)Using Substring and Position to evaluate only this 100018771
--------
4a)using in Terradata
sel substr ('Howse, Michael (100018771)',position('(' in 'Howse, Michael (100018771)' )+1,9)
4b)using in obiee
SUBSTRING("Stakeholders"."CIO Direct" ,position('(' in "Stakeholders"."CIO Direct")+1,9)



5)Percentage calculation
-----------------------
(100 * ("Financials"."Actual Amount"))/("Financials"."Planned Amount")

Alternate method
----------------
IFNULL("Financials"."Actual Amount",0)*100/("Financials"."Planned Amount")



6)Total Project Expense Current Year
--------------------------------
CASE
WHEN  "DB_TD_CIO_Dashboard".""."EDW_REP_SOURCE"."D_PA_PFM_PROJ_FIN_DTL"."YEAR_NBR" = Year( Current_Date )
THEN SUM (  "DB_TD_CIO_Dashboard".""."EDW_REP_SOURCE"."D_PA_PFM_PROJ_FIN_DTL"."PLAND_VAL" )
END



7) case statement 1
-------------------

case when "Dates"."T1 Actual Date" is not null and "Dates"."Days to T1 Date"  > -10  then 1
when "Dates"."T1 Actual Date" is null and "Dates"."Days to T1 Date"  > -10   and  "Dates"."Days to T1 Date" <14 then 2
when "Dates"."T1 Actual Date" is null and "Dates"."Days to T1 Date"  < -10   then 3 end

8) case statement 2
-----------------

CASE
WHEN "DB_TD_CIO_Dashboard".""."EDW_REP_SOURCE"."D_PA_PFM_PROJ_FIN_DTL"."EXPENS_TYP_CD" = 'CAPITAL'
THEN 'Investment'
WHEN "DB_TD_CIO_Dashboard".""."EDW_REP_SOURCE"."D_PA_PFM_PROJ_FIN_DTL"."EXPENS_TYP_CD" = 'OPERATING'
THEN 'Expense'
END


9)Case statement 3
-------------------

CASE 
when CAST(CAST("cio_teradata".."EDW_REP_SOURCE"."D_PA_PROJ_MULT_T2"."ACTL_T2_DT" AS TIMESTAMP) AS DATE) is NULL
THEN  TimeStampDiff(SQL_TSI_DAY, CURRENT_DATE,CAST(CAST("cio_teradata".."EDW_REP_SOURCE"."D_PA_PROJ_MULT_T2"."PLAND_T2_DT" AS TIMESTAMP) AS DATE))
ELSE
TimeStampDiff(SQL_TSI_DAY,CAST(CAST("cio_teradata".."EDW_REP_SOURCE"."D_PA_PROJ_MULT_T2"."ACTL_T2_DT" AS TIMESTAMP)AS DATE),CAST(CAST("cio_teradata".."EDW_REP_SOURCE"."D_PA_PROJ_MULT_T2"."PLAND_T2_DT" AS TIMESTAMP) AS DATE))
END


10 Case Statement 4
----------------------

(100 * (CASE when
"Scenario"."Gen2,Scenario"='MOR USD AMOUNT' then "Measure"."BIEXPNSE - measure" end) )/ (CASE when "Scenario"."Gen2,Scenario"='OP' then "Measure"."BIEXPNSE - measure" end)