SQL Queries: Range Between Dates – OSKAR

This requires Builder privileges for utilization.

Finding a Date Range

Mathematically the range is the difference between the highest and lowest values in a set of numbers, so a date range would be the difference between two dates. If you want a to find a range between two dates we have provided a SQL Server query which will help you obtain this date range and how to implement it within iDashboards.

Copy Code Below to Paste into SQL Server:

--this would be replaced with iDashboards parameter

DECLARE @startdate DATETIME = getdate()-30;

--this would be replaced with iDashboards parameter
DECLARE @enddate DATETIME = getdate();

SELECT [dayrange].[DayDate],[yourquery].*

FROM(SELECT CAST([t1].[DayDate] AS smalldatetime) AS [DayDate]
FROM(SELECT DATEADD([dd], ROW_NUMBER()OVER(ORDER BY (SELECT NULL)), DATEADD([dd],                       DATEDIFF([dd], 0, @startdate), 0)) AS [DayDate]
     FROM   sys.objects s1 CROSS JOIN sys.objects s2) [t1]
     WHERE [DayDate] <= @enddate) [dayrange]

LEFT JOIN

/* Place your query below. Make sure to join on the column that has the date. Date must be: cast as DATE or truncated to midnight*/

(SELECT CAST(getdate()-15 AS DATE) AS [thedate], 700 AS [YourMeasure]) [yourquery]
ON [dayrange].[DayDate] = [yourquery].[thedate]

Date Range using Common Table Expressions

A temporary named result set, known as a Common Table Expression (CTE) is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A Common Table Expression can include references to itself, known as a recursive common table expression. Common Table Expressions are defined within the statement using the WITH operator and are a convenient way to manage complex queries. They were introduced in SQL Server 2005 which is no longer supported by Microsoft.

Copy Code Below to Paste into SQL Server:

WITH Dates AS

(SELECT[Date] = DATEADD(WEEK,

DATEDIFF(WEEK, 0, DATEADD([year],
DATEDIFF([year], 0,
GETDATE()), 0)), 0) - 1
UNION ALL
SELECT [Date] = DATEADD(DAY, 7, [Date])

FROM Dates
WHERE DATE < GETDATE() - 7)

SELECT caldate.[date] AS Weeks

FROM (SELECT[Date] FROM Dates) AS caldate

For More Information:

Disclaimer: iDashboards Technical Support Engineers are not Data Analysts who know in depth SQL Queries or JavaScript Expressions. We often learn these skills on the job and have limited knowledge. We do our best to help you with your software in determining if: you are not leveraging the software in the best way for your data or you found a bug in the software, because we want to assist you in your success. If you have issues with in depth queries or expressions please contact iDashboards Support and know that it will take a bit of time to figure out what is necessary for this issue, and if necessary we will refer you to your Client Success Manager to schedule time with a Principal Product Engineer.

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.

Finding a Date Range

Date Range using Common Table Expressions

For More Information:

Related articles