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:
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.
Your issue relates to the way you are building your dynamic SQL. If you look at the output from your PRINT statement, there are no quotes around your @datetime. Additionally, selecting from a #temp table within dynamic SQL isn't going to do what you want it to do. #dynonuggets is going to be out-of-scope for your dynamic SQL. If you really do need to work with a dynamic table, you'll need to make a global temporary table, named ##dynonuggets.
I'll assume, DynoNuggets is a real table, and you have to use dynamic SQL, and your example is just simplified for your question.
You could add quotes, but the more appropriate solution is to use sp_executesql to parameterize your dynamic SQL.
Try this:
DECLARE @datetime datetime = GETDATE(),
@sql nvarchar(max),
@params nvarchar(1000)
CREATE TABLE DinoNuggets
(
saledate datetime
,item varchar(500)
)
INSERT INTO DinoNuggets VALUES
('2016-03-30 12:02:00.000','chair')
,('2016-03-04 08:12:48.000','stove')
,('2016-03-07 09:37:39.000','washer')
SET @sql = N'SELECT * FROM DinoNuggets WHERE saledate >= @ParamValue';
SET @Params = N'@ParamValue datetime';
PRINT @sql;
EXEC sp_executesql @sql, @params, @ParamValue = @datetime
Best Answer
As you are working with SQL Server 2014 you could cast/convert as a time (started in SQL 2008)