samedi 29 novembre 2014

How to recursively find gaps where 90 days passed, between rows


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;




Aucun commentaire:

Enregistrer un commentaire