This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this.
SELECT SomeId, MyDate,
dbo.udfLastHitRecursive(param1, param2, MyDate) as 'Qualifying'
FROM T
How should it work
I send those three params into a UDF.
The UDF internally use params to fetch related <= 90 days older rows, from a view.
The UDF traverse 'MyDate' and return 1 if it should be included in a total calculation.
If it should not, then it return 0.
Named here as "qualifying".
What the udf will do
List the rows in date order.
Calculate the days between rows.
First row in resultset defaults to Hit = 1.
If the difference is up to 90,
– then pass to next row until the sum of gaps is 90 days (90th day must pass)
When reached, set Hit to 1 and reset gap to 0.
It would also work to instead omit the row from result.
|(column by udf, which not work yet)
Date Calc_date MaxDiff | Qualifying
2014-01-01 11:00 2014-01-01 0 | 1
2014-01-03 10:00 2014-01-01 2 | 0
2014-01-04 09:30 2014-01-03 1 | 0
2014-04-01 10:00 2014-01-04 87 | 0
2014-05-01 11:00 2014-04-01 30 | 1
In the table above, MaxDiff column is the gap from date in previous line. The problem with my attempts so far is that I can't ignore second last row in the sample above.
[EDIT]
As per comment I add a tag and also paste the udf I have compiled just now. Though, is just a placeholder and won't give useful result.
;WITH cte (someid, otherkey, mydate, cost) AS
(
SELECT someid, otherkey, mydate, cost
FROM dbo.vGetVisits
WHERE someid = @someid AND VisitCode = 3 AND otherkey = @otherkey
AND CONVERT(Date,mydate) = @VisitDate
UNION ALL
SELECT top 1 e.someid, e.otherkey, e.mydate, e.cost
FROM dbo.vGetVisits AS E
WHERE CONVERT(date, e.mydate)
BETWEEN DateAdd(dd,-90,CONVERT(Date,@VisitDate)) AND CONVERT(Date,@VisitDate)
AND e.someid = @someid AND e.VisitCode = 3 AND e.otherkey = @otherkey
AND CONVERT(Date,e.mydate) = @VisitDate
order by e.mydate
)
I have another query which I define separately which is more close to what I need, but blocked with the fact I can't calculate on windowed columns. I also tried one similiar which give more or less same output just with a LAG() over MyDate, surrounded with a datediff.
SELECT
t.Mydate, t.VisitCode, t.Cost, t.SomeId, t.otherkey, t.MaxDiff, t.DateDiff
FROM
(
SELECT *,
MaxDiff = LAST_VALUE(Diff.Diff) OVER (
ORDER BY Diff.Mydate ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
(
SELECT *,
Diff = ISNULL(DATEDIFF(DAY, LAST_VALUE(r.Mydate) OVER (
ORDER BY r.Mydate ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
r.Mydate),0),
DateDiff = ISNULL(LAST_VALUE(r.Mydate) OVER (
ORDER BY r.Mydate ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
r.Mydate)
FROM dbo.vGetVisits AS r
WHERE r.VisitCode = 3 AND r.SomeId = @SomeID AND r.otherkey = @otherkey
) AS Diff
) AS t
WHERE t.VisitCode = 3 AND t.SomeId = @SomeId AND t.otherkey = @otherkey
AND t.Diff <= 90
ORDER BY
t.Mydate ASC;
Best Answer
As I read the question, the basic recursive algorithm required is:
This is relatively easy to implement with a recursive common table expression.
For example, using the following sample data (based on the question):
The recursive code is:
The results are:
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.