Defining the Translation Table

The first step is to define a Dataset that returns the Start and End dates for each desired Date Range and configure that Dataset to refresh every 24 hours and to store the query result in the Nathean Analytics Datamart as a table called TBL_DATE_RANGE.

If using this approach, it is highly recommended to replace the LOOKUP Dataset query (described in the previous article) with a simple “SELECT DISTINCT DATE_RANGE FROM TBL_DATE_RANGE”. This allows any changes to the translation table (e.g. the addition of new date ranges) to be picked up by any Dataset using the translation table without any further configuration)

The example below uses the SQL Server DateTime datatype for these dates, a simpler version could be used if the data being queried is held in Date fields which exclude time values.

SELECT 
  'Today' AS DATE_RANGE,
  dateadd(dd, datediff(dd, 0, getdate()), 0) AS START_DATE,
  dateadd(ms, -3, dateadd(DAY,1,CAST(CAST(getdate() AS DATE) AS DATETIME))) AS END_DATE
UNION ALL
SELECT 
  'Yesterday',
  dateadd(dd, datediff(dd, 1, getdate()), 0),
  dateadd(ms, -3, dateadd(DAY,0,CAST(CAST(getdate() AS DATE) AS DATETIME)))
UNION ALL
SELECT
  'This Week',
  dateadd(wk, datediff(wk, 0, getdate()), 0) As START_DATE,
  dateadd(ms, -3, dateadd(wk, datediff(wk, 0, getdate()) + 1, 0))
UNION ALL
SELECT
  'Last Week',
  dateadd(wk, -1, dateadd(wk, datediff(wk, 0, getdate()), 0)),
  dateadd(wk, -1, dateadd(ms, -3, dateadd(wk, datediff(wk, 0, getdate()) + 1, 0)))
UNION ALL
SELECT
  'This Month',
  dateadd(mm, datediff(mm, 0, getdate()), 0),
  dateadd(ms, -3, dateadd(mm, datediff(mm, 0, getdate()) + 1, 0))
UNION ALL
SELECT
  'Last Month',
  dateadd(mm,-1,dateadd(mm, datediff(mm, 0, getdate()), 0)),
  dateadd(mm,-1,dateadd(ms, -3, dateadd(mm, datediff(mm, 0, getdate()) + 1, 0)))
UNION ALL
SELECT 
  'This Year',
  dateadd(yy, datediff(yy, 0, getdate()), 0),
  dateadd(ms, -3, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0))
UNION ALL
SELECT
  'Last Year',
  dateadd(yy, datediff(yy, 0, getdate()) - 1, 0),
  dateadd(ms, -3, dateadd(yy, datediff(yy, 0, getdate()), 0))
UNION ALL
SELECT 
  'Year to Date',
  dateadd(yy, datediff(yy, 0, getdate()), 0),
  dateadd(ms, -3, dateadd(DAY,1,CAST(CAST(getdate() AS DATE) AS DATETIME)))
UNION ALL
SELECT
  'This Quarter',
  dateadd(qq, datediff(qq, 0, getdate()), 0),
  dateadd(ms, -3, dateadd(qq, datediff(qq, 0, getdate()) + 1, 0))
UNION ALL
SELECT 
  'Custom Range',
  NULL,
  NULL

Using the Translation Table to limit results in a Dataset query

Once this table has been created in the Datamart, it can then be referenced in any query that runs against that datamart as so:

SELECT *
FROM datamart.[TABLE_NAME]
WHERE
  [DATE_FIELD] >= (SELECT ISNULL(START_DATE, :START_DATE) 
                   FROM TBL_DATE_RANGE WHERE DATE_RANGE = :DATE_RANGE)
  AND [DATE_FIELD] <= (SELECT ISNULL(END_DATE, :END_DATE)
                       FROM TBL_DATE_RANGE WHERE DATE_RANGE = :DATE_RANGE)

You’ll notice that this query contains 3 parameters: DATE_RANGE, START_DATE and END_DATE. This, combined with the use of the ISNULL in the SQL above allows end users to choose a value of “Custom Range” from the drop-down provided by the LOOKUP dataset for the DATE_RANGE parameter and to then specify the Start and End Dates where these may fall outside of any of the defined period ranges.

Revision: 7
Last modified: Oct 22, 2019

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment