When Nathean Analytics is connecting to data held in a different server environment, or where a large number of Datasets are using the same source environment, it can often make sense to create a stored procedure within the source database to translate Date Ranges into Start and End dates.

Defining the Stored Procedure

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.

CREATE PROCEDURE [dbo].[lgx_DateRange] (@DATE_RANGE varchar(20),
					@CUSTOM_START datetime,
					@CUSTOM_END datetime,
					@START_DATE datetime OUTPUT,
					@END_DATE datetime OUTPUT)

AS

	IF @DATE_RANGE = 'Today'
	  BEGIN
		SET @START_DATE = dateadd(dd, datediff(dd, 0, getdate()), 0)
		SET @END_DATE   = dateadd(ms, -3, dateadd(DAY,1,CAST(CAST(getdate() AS DATE) AS DATETIME)))
	  END

	IF @DATE_RANGE = 'Yesterday'
	  BEGIN
		SET @START_DATE = dateadd(dd, datediff(dd, 1, getdate()), 0)
		SET @END_DATE   = dateadd(ms, -3, dateadd(DAY,0,CAST(CAST(getdate() AS DATE) AS DATETIME)))
	  END 

	IF @DATE_RANGE = 'This Week'
	  BEGIN
		SET @START_DATE = dateadd(wk, datediff(wk, 0, getdate()), 0)
		SET @END_DATE   = dateadd(ms, -3, dateadd(wk, datediff(wk, 0, getdate()) + 1, 0))
	  END

	IF @DATE_RANGE = 'Last Week'
	  BEGIN
		SET @START_DATE = dateadd(wk, -1, dateadd(wk, datediff(wk, 0, getdate()), 0))
		SET @END_DATE   = dateadd(wk, -1, dateadd(ms, -3, dateadd(wk, datediff(wk, 0, getdate()) + 1, 0)))
	  END

	IF @DATE_RANGE = 'This Month'
	  BEGIN
		SET @START_DATE = dateadd(mm, datediff(mm, 0, getdate()), 0)
		SET @END_DATE   = dateadd(ms, -3, dateadd(mm, datediff(mm, 0, getdate()) + 1, 0))
	  END

	IF @DATE_RANGE = 'Last Month'
	  BEGIN
		SET @START_DATE = dateadd(mm,-1,dateadd(mm, datediff(mm, 0, getdate()), 0))
		SET @END_DATE   = dateadd(mm,-1,dateadd(ms, -3, dateadd(mm, datediff(mm, 0, getdate()) + 1, 0)))
	  END

	IF @DATE_RANGE =  'This Year'
	  BEGIN
	  	SET @START_DATE = dateadd(yy, datediff(yy, 0, getdate()), 0)
	  	SET @END_DATE   = dateadd(ms, -3, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0))
	  END

	IF @DATE_RANGE = 'Last Year'
	  BEGIN
		SET @START_DATE = dateadd(yy, datediff(yy, 0, getdate()) - 1, 0)
		SET @END_DATE   = dateadd(ms, -3, dateadd(yy, datediff(yy, 0, getdate()), 0))
	  END

	IF @DATE_RANGE = 'Year to Date'
	  BEGIN		
		SET @START_DATE = dateadd(yy, datediff(yy, 0, getdate()), 0)
		SET @END_DATE   = dateadd(ms, -3, dateadd(DAY,1,CAST(CAST(getdate() AS DATE) AS DATETIME)))
	  END

	IF @DATE_RANGE = 'This Quarter'
	  BEGIN
		SET @START_DATE = dateadd(qq, datediff(qq, 0, getdate()), 0)
		SET @END_DATE   = dateadd(ms, -3, dateadd(qq, datediff(qq, 0, getdate()) + 1, 0))
	  END

	IF @DATE_RANGE = 'Custom Range'
	  BEGIN
		SET @START_DATE = @CUSTOM_START
		SET @END_DATE   = @CUSTOM_END
	  END

GO

Using the Stored Procedure to limit results in a query

Once this stored procedure has been created in the source database , it can then be referenced in any query that runs against that database as so:

DECLARE
  @START_DATE datetime,
  @END_DATE datetime

EXECUTE lgx_DateRange :DATE_RANGE, :START_DATE, :END_DATE, @START_DATE OUTPUT, @END_DATE OUTPUT

SELECT *
FROM datamart.[TABLE_NAME]
WHERE
  [DATE_FIELD] BETWEEN @START_DATE AND @END_DATE

You’ll notice that this query contains 3 parameters: DATE_RANGE, START_DATE and END_DATE. This 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: 5
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