monthname( Date )

SQL function that Cognos likes but Hana does not.  Do not use with Activity Hubs.

Set null dates to null

cast (NULL, date)

Use this to force cognos to treat Null values as dates

Remove null values

Filter for [Field Name] is not null

This performs better than using [Field Name] <> ''

Return not null value

Don't use isnull, use coalesce

COALESCE([EAH Employee ID], [RAH Employee ID])

COALESCE([Measure Name Amount], 0)

must have the same data type

Days Open Group (days)

"if ( [Days Open] <=1) then ('1 day open') else if ([Days Open] between 1 and 2) then ('2 days open') else if ([Days Open] between 2 and 7) then ('2-7 days open') else if ([Days Open] between 7 and 14) then ('7-14 days open') else if ([Days Open] between 14 and 30) then ('14-30 days open') else if ([Days Open] between 30 and 60) then ('30-60 days open') else if ([Days Open] between 60 and 90) then ('60-90 days open') else ('90+ days open')"

Formatted Date Range

numtodsinterval(if ([End Date] is null) then ([Open Date]) else ([End Date] - [Open Date]), 'day')

Format Date

numtodsinterval([item]), 'interval type')

Format Date

DateDiff()

Format HH:MM time

to_char([Time], 'HH:MI AM')

Open Time Group (date time)

"CASE WHEN [Open Time] <= 001 00:00:00.000 THEN '24 Hours' WHEN [Open Time] > 001 00:00:00.000 AND [Open Time] <= 002 00:00:00.000 THEN '48 Hours' WHEN [Open Time] > 002 00:00:00.000 AND [Open Time] <= 007 00:00:00.000 THEN '1 week' WHEN [Open Time] > 007 00:00:00.000 AND [Open Time] <= 014 00:00:00.000 THEN '2 weeks' WHEN [Open Time] > 014 00:00:00.000 AND [Open Time] <= 028 00:00:00.000 THEN '4 weeks' ELSE '> 4 weeks' END"

End of Day

11:59:59

Start of Day

0:00:00

Yesterday

[Date] = current_date - 1 00:00:00.000

Last Week Starting Sunday

[Week - Sunday Start] between _add_days(current_date, -13) and _add_days(current_date, -7)

Last Week

between _add_days(current_date, -7) and _add_days(current_date, -1)

Last Month

between _first_of_month(_add_months(current_date, -1)) and _last_of_month(_add_months(current_date, -1))

Current Year Filter

extract (year, [Close Date]) = extract(year, (_last_of_month(_add_months(current_date, -1))))

Fiscal YTD

"[start year] = if (extract(month from current_date) <= 10) then (extract(year from current_date)-1) else (extract(year from current_date)) [start char yr] = cast([start year], varchar(4)) [start date] = cast(([start char yr] || '-10-01'), date) filter = [Date] between [start date] and _last_of_month(_add_months(current_date, -1))"

YTD Change

Current Year - Prior Year

YTD % Growth

(Current Year - Prior Year) / absolute values (Prior Year)

Month Names

"CASE [MonthNum] WHEN 1 then 'Jan' WHEN 2 then 'Feb' WHEN 3 then 'Mar' WHEN 4 then 'Apl' WHEN 5 then 'May' WHEN 6 then 'Jun' WHEN 7 then 'Jul' WHEN 8 then 'Aug' WHEN 9 then 'Sep' WHEN 10 then 'Oct' WHEN 11 then 'Nov' ELSE 'Dec' END"

Previous 4 months

"[Open Date] between _first_of_month(_add_months(current_date, -4)) and _last_of_month(_add_months(current_date, -1))"

First letter for first word

substr([item],1,1)

Returns 1st word

substr(UPPER([Short Description]), 1, (instr([Short Description],' ',1,1)))

Returns 4th word

"trim(substr(' '||[Event Service Description]||' ', instr(' '||[Event Service Description]||' ',' ',1,4)+1, instr(' '||[Event Service Description]||' ',' ',1,4+1)- instr(' '||[Event Service Description]||' ',' ',1,4)-1))"

Group by AH

CASE

when Package  like 'EAH%' then 'EAH'

when Package  like 'FINAH%' then 'FINAH'

when Package  like 'PAH%' then 'PAH'

when Package  like 'RAH%' then 'RAH'

when Package  like 'SAH%' then 'SAH'

else 'Other'

END

Seconds to time interval

cast(cast([Total Avail Time] as interval second) as interval hour to second)

Number to time interval

numtodsinterval([x])

Zero Time

cast(0, time)

Day from time interval

extract(day, [x])

Display last 2 years

tail ([Year], 2)

Change 4 to 04

substring (([x]+100), 2, 2)

Convert to number

to_num([x])

html open in new tab

<a href="http://myURL" target="_blank">text</a>

replaces 'test' with null

replace([X], 'test')

Convert number to string

to_char([x])

Approvals

"if ([Task Approval] = 'rejected') then ('Rejected') else if ([Approval] <> 'rejected' AND [Request Item Stage] = 'Request Cancelled') then ('Cancelled') else ('Approved')"

Month Format

to_char(date_trunc('Month',[Ticket Received Date]),'MON-YYYY')

Month Format

to_char([Date],'MMYYYY')

Create Null Dates

"if ([Date] = 9999-12-31T00:00:00.000000000 ) then (null) else ([Date])"

n indicates max size of field - may work without (n)

cast(field, character (n))

Cast as Date

cast( '1970-01-01', date)

Field converted from seconds to time interval

nvl(field,0) * cast ('000 00:00:01.000', interval day to second)

n is number of digits - p is number of decimals

cast(field, numeric (n,p))

Yesterday

_add_days (current_date, - 1)

8 days ago

_add_hours (current_date, -8)

Replaces value 'x' with value 'y'. Translate only works for single value text items

Translate([FIELD], 'x', 'y')

Displays all values in string up to 'abcd'. IF string 'abcd' is not in the field, will display blank.

SUBSTR_BEFORE ([Field],'abcd')

Capitalizes the first letter of the string in FIELD.

upper(substr([FIELD], 1, 1)) || lower(substr([FIELD], 2))

Capitalizes the first letter each word.

initcap([FIELD])

Concatenates 2 string fields. Verify results when null involved.

[FIELD1] || [FIELD2]

Cast as Date

cast(1900-01-01,date)

Changes <BR> to a blank.

Replace([FIELD], '<BR>', ' ')

Replace a value

if (instr([FIELD], '<BR>') = 0) then ([FIELD]) else (substr([FIELD], 1, instr([FIELD], '<BR>')-1))

Field converted from seconds to time interval

nvl([FIELD], 0) * cast('000 00:00:01.000', interval day to second)

Field converted from seconds to time interval

coalesce([FIELD], 0) * cast('000 00:00:01.000', interval day to second)

time interval between two timestamps

select extract(epoch from '2014-08-05 08:00:28'::timestamp - '2014-08-04 08:00:23'::timestamp)

time interval between two timestamps

age([time1], [time2])

remove last 5 characters of string

substring([Term], 1, (char_length ([Term])-5))

group values of field output and return one of the groups

^{SUBSTR[ING]_REGEXPR( <pattern> [ FLAG <flag> ] IN <regex_subject_string> [ FROM <start_position> ] [ OCCURRENCE <regex_occurrence> ] [ GROUP <regex_capture_group> ] )}

<sup>Example:
SELECT SUBSTR_REGEXPR('([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})' IN '20140401' GROUP 3) "substring_regexpr" FROM DUMMY;

String value = '20140401'
Requested output is GROUP3 --- which is the last 2 digits group (the '01' at the end of the string)</sup>

Pattern ---------- '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})'

     Group (section) 1 is 4 digits ------ ([[:digit:]]{4})

     Group (section) 2 is 2 digits  ------ ([[:digit:]]{2})

     Group (section) 3 is 2 digits  ------ ([[:digit:]]{2})