First, a table and some sample data to play with:
USE tempdb;
GO
CREATE TABLE dbo.SomeTable(dt DATETIME);
GO
SET NOCOUNT ON;
GO
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -22, GETDATE());
GO 45
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -19, GETDATE());
GO 32
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -17, GETDATE());
GO 21
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -12, GETDATE());
GO 16
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -5, GETDATE());
GO 55
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -2, GETDATE());
GO 26
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -1, GETDATE());
GO 71
INSERT dbo.SomeTable(dt) SELECT GETDATE();
GO 14
(I'd do this in sqlfiddle but I'm not sure it supports GO <int>
to have many rows, and it chokes on INSERT
> 8000 characters.)
Now a stored procedure:
CREATE PROCEDURE dbo.GetGroupedIntervals
@MinuteInterval TINYINT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@IntervalCount INT, @StartDate SMALLDATETIME;
SELECT
@StartDate = DATEADD(MINUTE, -1, MIN(dt)),
@IntervalCount = (DATEDIFF(MINUTE, MIN(dt), MAX(dt))
+ @MinuteInterval) / @MinuteInterval
FROM dbo.SomeTable -- WHERE ...;
;WITH dates(s,e) AS
(
SELECT
DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
DATEADD(MINUTE, @MinuteInterval*(n.n), @StartDate)
FROM
(
SELECT
TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
FROM sys.all_objects AS o CROSS JOIN sys.all_columns AS c
ORDER BY o.[object_id]
) AS n(n)
)
SELECT StartDate = d.s, c = COUNT(s.dt)
FROM dates AS d
LEFT OUTER JOIN dbo.SomeTable AS s
ON s.dt >= d.s AND s.dt < d.e
-- AND any filter criteria for dbo.SomeTable?
GROUP BY d.s
ORDER BY d.s;
END
GO
And some sample usage:
EXEC dbo.GetGroupedIntervals @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 5;
For brevity, I'll show the results for the last call, but you can play with the others.
StartDate c
------------------- ----
2012-05-16 12:51:00 77
2012-05-16 12:56:00 21
2012-05-16 13:01:00 16
2012-05-16 13:06:00 55
2012-05-16 13:11:00 111
Some notes:
- The join using s.dt is likely to perform better than any extraction methods using datepart if the datetime column in your base table has an index (or might in the future).
- I assumed you wanted to show all the intervals within the range. If you don't want to show the intervals with 0 counts, just change the left outer join to an inner join.
- I go down a minute in case the start date was rounded up when converted to SMALLDATETIME. For the 1 minute interval and possibly others, this can lead to 0 counts for the first interval. You can adjust how this rounding happens (e.g. you can use FLOOR() to make sure it always rounds down). All depends on how accurate you need to be.
- I didn't include any WHERE clause but you may need to have those to filter. For example your query may want all the intervals for a given day. You may want to change the calculation of
dates
to produce all the intervals for the day, rather than all the intervals between the min and max time found in dbo.SomeTable
on that day. The following variation takes care of this, by presenting the data for a single day starting from midnight and incrementing by @MinutInterval:
...
CREATE PROCEDURE dbo.GetGroupedIntervalsByDay
@Date DATE,
@MinuteInterval TINYINT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@IntervalCount INT,
@StartDate SMALLDATETIME = @Date;
SELECT
@IntervalCount = 1440 / @MinuteInterval;
;WITH dates(s,e) AS
(
SELECT
DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
DATEADD(MINUTE, @MinuteInterval*(n.n), @StartDate)
FROM
(
SELECT
TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
FROM sys.all_columns AS o
ORDER BY o.[object_id]
) AS n(n)
)
SELECT StartDate = d.s, c = COUNT(s.dt)
FROM dates AS d
LEFT OUTER JOIN dbo.SomeTable AS s
ON s.dt >= d.s AND s.dt < d.e
-- AND any filter criteria for dbo.SomeTable?
GROUP BY d.s
ORDER BY d.s;
END
GO
Sample calls:
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 5;
Abridged results from the last call:
StartDate c
------------------- ----
2012-05-16 00:00:00 0
2012-05-16 00:05:00 0
2012-05-16 00:10:00 0
...
2012-05-16 12:40:00 0
2012-05-16 12:45:00 0
2012-05-16 12:50:00 45
2012-05-16 12:55:00 53
2012-05-16 13:00:00 16
2012-05-16 13:05:00 55
2012-05-16 13:10:00 111
2012-05-16 13:15:00 0
2012-05-16 13:20:00 0
...
2012-05-16 23:45:00 0
2012-05-16 23:50:00 0
2012-05-16 23:55:00 0
(Again if you don't want to include the intervals with no counts, change the left outer join to an inner join. You also might find some odd results if you choose an interval that doesn't fit nicely inside of 1440. I'll leave that case as an exercise to the reader.)
As I read the question, the basic recursive algorithm required is:
- Return the row with the earliest date in the set
- Set that date as "current"
- Find the row with the earliest date more than 90 days after the current date
- Repeat from step 2 until no more rows are found
This is relatively easy to implement with a recursive common table expression.
For example, using the following sample data (based on the question):
DECLARE @T AS table (TheDate datetime PRIMARY KEY);
INSERT @T (TheDate)
VALUES
('2014-01-01 11:00'),
('2014-01-03 10:00'),
('2014-01-04 09:30'),
('2014-04-01 10:00'),
('2014-05-01 11:00'),
('2014-07-01 09:00'),
('2014-07-31 08:00');
The recursive code is:
WITH CTE AS
(
-- Anchor:
-- Start with the earliest date in the table
SELECT TOP (1)
T.TheDate
FROM @T AS T
ORDER BY
T.TheDate
UNION ALL
-- Recursive part
SELECT
SQ1.TheDate
FROM
(
-- Recursively find the earliest date that is
-- more than 90 days after the "current" date
-- and set the new date as "current".
-- ROW_NUMBER + rn = 1 is a trick to get
-- TOP in the recursive part of the CTE
SELECT
T.TheDate,
rn = ROW_NUMBER() OVER (
ORDER BY T.TheDate)
FROM CTE
JOIN @T AS T
ON T.TheDate > DATEADD(DAY, 90, CTE.TheDate)
) AS SQ1
WHERE
SQ1.rn = 1
)
SELECT
CTE.TheDate
FROM CTE
OPTION (MAXRECURSION 0);
The results are:
╔═════════════════════════╗
║ TheDate ║
╠═════════════════════════╣
║ 2014-01-01 11:00:00.000 ║
║ 2014-05-01 11:00:00.000 ║
║ 2014-07-31 08:00:00.000 ║
╚═════════════════════════╝
With an index having TheDate
as a leading key, the execution plan is very efficient:
![Execution plan](https://i.stack.imgur.com/yZulu.png)
You could choose to wrap this in a function and execute it directly against the view mentioned in the question, but my instincts are against it. Usually, performance is better when you select rows from a view into a temporary table, provide the appropriate index on the temporary table, then apply the logic above. The details depend on the details of the view, but this is my general experience.
For completeness (and prompted by ypercube's answer) I should mention that my other go-to solution for this type of problem (until T-SQL gets proper ordered set functions) is a SQLCLR cursor (see my answer here for an example of the technique). This performs much better than a T-SQL cursor, and is convenient for those with skills in .NET languages and the ability to run SQLCLR in their production environment. It may not offer much in this scenario over the recursive solution because the majority of the cost is the sort, but it is worth mentioning.
Best Answer
CURRENT_TIMESTAMP returns the current datetime something like
2017-03-09 15:19:53.770
.CONVERT(VARCHAR(10), CreatedDate, 108)
returns a string with only time15:19:53
.The second parameter of DATEDIFF takes a datetime so your string is implicitly converted to a datetime. In lack of something better to use for a date SQL Server uses
1900-01-01
so your second parameter ends up to be1900-01-01 15:19:53.000
and the function returns the number of minutes since the beginning of the previous century.Remove the convert in the second parameter and it should work just fine for you.