cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
When pulling together data from multiple sources, you’ll have a number of different dates. For instance, the marketing department has a Campaign Date, Sales department may have an Opportunity Date and Finance has a GL Date.  
Modeling Challenge
This type of data leaves us with three sets of dates. Modeling it properly will allow you to select from one common date field while still leaving you the ability to choose from one of the three date fields individually.
Solution
We need to come up with a Custom SQL Expression that retrieves a unique list of the dates used between the three tables.
alt
We create a Custom SQL Expression with the following Syntax:
Select Distinct x.[Common Date] from (

Select [GL Date] AS [Common Date] 
FROM [GL Entries] 

Union All

Select [Marketing Campaign Date] AS [Common Date] 
FROM [Marketing Campaigns] 

Union All

Select [Opportunity Date] AS [Common Date] 
FROM [Sales Opportunities]

) as x
Lastly, we link the four tables together:
alt
Results
This gives us the common date field to use.  This will allow us, for example, to choose a month and it will narrow down the selections across the three tables.
Rate this article:
Version history
Last update:
‎03-02-2023 08:28 AM
Updated by:
Contributors