Este é um tipo de tarefa trivial em meu mundo natal C #, mas ainda não o faço em SQL e prefiro resolvê-lo com base em conjuntos (sem cursores). Um conjunto de resultados deve vir de uma consulta como esta.
SELECT SomeId, MyDate,
dbo.udfLastHitRecursive(param1, param2, MyDate) as 'Qualifying'
FROM T
Como deve funcionar
Envio esses três parâmetros para um UDF.
A UDF usa internamente parâmetros para buscar linhas relacionadas <= 90 dias anteriores, de uma exibição.
O UDF percorre 'MyDate' e retorna 1 se for incluído em um cálculo total.
Caso contrário, retornará 0. Nomeado aqui como "qualificação".
o que a udf vai fazer
Liste as linhas em ordem de data. Calcule os dias entre as linhas. O padrão da primeira linha no conjunto de resultados é Hit = 1. Se a diferença for de até 90, então passe para a próxima linha até que a soma das lacunas seja de 90 dias (o 90º dia deve passar) Quando atingido, defina Hit como 1 e redefina a lacuna como 0 Também funcionaria omitir a linha do resultado.
|(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
Na tabela acima, a coluna MaxDiff é a diferença da data na linha anterior. O problema com minhas tentativas até agora é que não posso ignorar a penúltima linha no exemplo acima.
[EDIT]
De acordo com o comentário, adiciono uma tag e também colo o udf que compilei agora. No entanto, é apenas um espaço reservado e não fornecerá resultados úteis.
;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
)
Tenho outra consulta que defino separadamente que está mais próxima do que preciso, mas bloqueada pelo fato de não poder calcular em colunas com janelas. Eu também tentei um similar que dá mais ou menos a mesma saída apenas com um LAG () sobre MyDate, cercado por um 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;