Discussions
Insight (OBIEE) Tips | Commonly used SQL expressions
This document outlines some useful Insight expressions that we find our clients using. These are just examples and can used in many different situations.
Date expression:
This helps users filter based on dynamic dates.
Example: If you want to see all Email Unsubscribes in the past 90 days, you would
- Create a filter on Email Unsubscribe Date
- Choose the operator “is greater than”
- From the “Add More Options” drop down, select SQL Expression
- A SQL Expression text box would appear
- Enter the expression TIMESTAMPADD(SQL_TSI_DAY, -90, CURRENT_DATE) then click OK
The syntax of this SQL expression is TIMESTAMPADD(interval, count, timestamp)
- TIMESTAMPADD: Returns the timestamp calculated by adding count intervals of type interval to timestamp
- Specified intervals can be:
- SQL_TSI_FRAC_SECOND
- SQL_TSI_SECOND
- SQL_TSI_MINUTE
- SQL_TSI_HOUR
- SQL_TSI_DAY
- SQL_TSI_WEEK
- SQL_TSI_MONTH
- SQL_TSI_QUARTER
- SQL_TSI_YEAR
- Count: An expression that adds up to an integer.
- Timestamp: Any expression that evaluates to a valid timestamp.
Another examples
First day of current month:
TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1 , CURRENT_DATE)
First day of month, 12 moths ago:
TIMESTAMPADD(SQL_TSI_MONTH, -12, TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) + 1 , CURRENT_DATE))
CAST function:
Returns the value of an expression converted to a specified data type.
Expression:
CAST(expression AS datatype)
Example:
Changing a date to a string:
CAST(Email Unsubscribe Date AS CHAR)
This converts the date “4/1/2018 10:00:00 PM” into text “Apr 1 2018 10:00PM”.
Changing Boolean to true of false:
CAST(Opt-In field AS CHAR)
Ensure that you are using compatible data types as not all are compatible, please refer to:
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj33562.html
RSUM function:
This function calculates the running sum based on records encountered so far.
Expression:
RSUM(numExpr)
NumExpr is any expression that evaluates to a numeric value.
Example/Use case:
If you are in the process of IP warming and want to keep track of bouncebacks on a daily basis. You would modify the report Email Bounceback Overview. Make sure that the Bounceback back date is sorted in an ascending fashion. Then add another Total Bouncebacks column and edit its formula:
Click the function button:
Then select Functions>Running Aggregate>RSUM.
Notice the expression now becomes:
RSUM("Metrics"."Total Bouncebacks")
This column will give you the running sum of your total bouncebacks day by day:
If you would like to see when you have had a certain number of bouncebacks in a campaign, this report using the RSUM function would be useful.
I hope this helps, Let me know if you have any questions in the comments.
SamE.
Comments
-
In Insight am looking for a way to convert a string returned from a CDO into a date. The string is formatted as 2019-09-09 06:30 PM. I have found that CAST might be my best option and have been successful in using something like CAST("Contact Custom Obj 3"."ColText13" AS TIMESTAMP)
The returned date is 9/9/2019 6:30:00 PM
What I would like to do is now format that date into just the Month so I can use a drop down filter to filter by month. I would like to use standard format options like mm or MMMM to alter how the data is displayed.
-
In Insight am looking for a way to convert a string returned from a CDO into a date. The string is formatted as 2019-09-09 06:30 PM. I have found that CAST might be my best option and have been successful in using something like CAST("Contact Custom Obj 3"."ColText13" AS TIMESTAMP)
The returned date is 9/9/2019 6:30:00 PM
What I would like to do is now format that date into just the Month so I can use a drop down filter to filter by month. I would like to use standard format options like mm or MMMM to alter how the data is displayed.
Sorry Brad, missed this. Can you use MONTH() or MONTHNAME()?
-
Is there a SQL expression I could use to grab the 'Date of Last Form Submission"? I'd like to build a report which displays any form that has not had a submission within the last X amount of days. The goal is to monitor this report and be alerted of any potential issues with active forms which have previously been submitted but stopped collecting data within a certain time frame.
-
Is there a SQL expression I could use to grab the 'Date of Last Form Submission"? I'd like to build a report which displays any form that has not had a submission within the last X amount of days. The goal is to monitor this report and be alerted of any potential issues with active forms which have previously been submitted but stopped collecting data within a certain time frame.
Use the Form Submission subject area as a start. You'll want to create a filter for Total Form Submissions IS NULL and the dynamic date range that you're interested in.
You'll also want to include something like Total Form Views in the report since Insight is activity based reporting - Null activities alone will return no results. By including the views you're getting forms that were viewed but no submissions.
The dynamic date range could use the Time Span (last 7, 14, 30 days) attribute or custom SQL.
Your agent then needs to be set up with a condition so that it only sends you results if there are results to show.
Good luck.
-
Is there a SQL expression that would allow me to run an Insight report and retrieve results for a specific period of time. For example. I'd like to show a report that has metrics after 7 days of the campaign start. After 7 days pass I would still want to see what it was at that point in time.
-
Hello, is there an SQL expression that will round up/down/place a timestamp to/within the nearest half hour? The goal is to build a dynamic data table that checks on a CDO date filed and illustrates the time slots with the highest volume of contacts.
-
Hi all like to share this, for example, some of our campaigns started to be created/activated by a third party.
So for me to check how many day's they are taking from creating the campaign to the activation, I'm using the following SQL:
TimestampDiff(SQL_TSI_DAY, "Campaign"."Campaign Created Date/Time", "Campaign"."Campaign Last Modified Date/Time")
That you would have the following "Age" that the campaign those it take to be activated:
Hope it helps someone or other cases related to dates.
Post edited by aappll on
