sql - 带有可变种子的 DATEDIFF

Question

我有下表

第一列是按日期降序排列的交易日期。第二列是每笔交易的二进制状态，可以是 0 或 1。第三列是最小日期与每个日期相比的 datediff。生成这个表的代码是这样的

DECLARE @date DATE 
SELECT @Date = MIN(CONVERT(DATE,Transaction_Created)) FROM #dates
SELECT      CONVERT(DATE,Transaction_Created)                       AS Date
    ,       MAX(Is_Deposit)                                         AS Is_Deposit
    ,       DATEDIFF(dd,@Date,CONVERT(DATE,Transaction_Created))    AS Datedif  

FROM        #DATES
GROUP BY    CONVERT(DATE,Transaction_Created)
order by 1 desc

我的问题是，当 is_Deposit=1 时，我需要为 DATEDIFF 重置我的种子，并且 IS_Deposit =1 的日期成为我的新 MIN 日期，对于我在表中找到的每个 IS_Deposit=1 依此类推。例如从 2015-12-04 到 2015-12-16 一切正常。但是在第 746 行 2015-12-17 我希望它再次成为 1 而不是 13 因为我们已经达到下一个 IS_Deposit=1 并且我们必须重置。

现在的结果集:

Date    Is_Deposit  Datedif

2016-02-12  0   70
2016-02-11  0   69
2016-02-10  0   68
2016-02-09  0   67
2016-02-08  0   66
2016-02-07  0   65
2016-02-06  0   64
2016-02-05  0   63
2016-02-04  0   62
2016-02-03  0   61
2016-02-02  0   60
2016-02-01  0   59
2016-01-31  0   58
2016-01-30  0   57
2016-01-29  0   56
2016-01-28  0   55
2016-01-27  0   54
2016-01-26  0   53
2016-01-25  0   52
2016-01-24  0   51
2016-01-23  0   50
2016-01-22  0   49
2016-01-21  0   48
2016-01-20  1   47
2016-01-17  0   44
2016-01-16  0   43
2016-01-15  0   42
2016-01-14  0   41
2016-01-13  0   40
2016-01-12  0   39
2016-01-11  0   38
2016-01-10  0   37
2016-01-09  0   36
2016-01-08  0   35
2016-01-07  0   34
2016-01-06  0   33
2016-01-05  0   32
2016-01-04  0   31
2016-01-03  0   30
2016-01-02  0   29
2016-01-01  0   28
2015-12-31  0   27
2015-12-30  0   26
2015-12-29  0   25
2015-12-28  0   24
2015-12-27  0   23
2015-12-26  0   22
2015-12-25  1   21
2015-12-20  0   16
2015-12-19  0   15
2015-12-18  0   14
2015-12-17  0   13
2015-12-16  1   12
2015-12-14  0   10
2015-12-13  0   9
2015-12-12  0   8
2015-12-11  0   7
2015-12-10  0   6
2015-12-09  0   5
2015-12-08  0   4
2015-12-07  0   3
2015-12-05  0   1
2015-12-04  1   0

需要结果集:

2016-02-12  0   23

2016-02-11  0   22
2016-02-10  0   21
2016-02-09  0   20
2016-02-08  0   19
2016-02-07  0   18
2016-02-06  0   17
2016-02-05  0   16
2016-02-04  0   15
2016-02-03  0   14
2016-02-02  0   13
2016-02-01  0   12
2016-01-31  0   11
2016-01-30  0   10
2016-01-29  0   9
2016-01-28  0   8
2016-01-27  0   7
2016-01-26  0   6
2016-01-25  0   5
2016-01-24  0   4
2016-01-23  0   3
2016-01-22  0   2
2016-01-21  0   1
2016-01-20  1   26
2016-01-17  0   23
2016-01-16  0   22
2016-01-15  0   21
2016-01-14  0   20
2016-01-13  0   19
2016-01-12  0   18
2016-01-11  0   17
2016-01-10  0   16
2016-01-09  0   15
2016-01-08  0   14
2016-01-07  0   13
2016-01-06  0   12
2016-01-05  0   11
2016-01-04  0   10
2016-01-03  0   9
2016-01-02  0   8
2016-01-01  0   7
2015-12-31  0   6
2015-12-30  0   5
2015-12-29  0   4
2015-12-28  0   3
2015-12-27  0   2
2015-12-26  0   1
2015-12-25  1   9
2015-12-20  0   4
2015-12-19  0   3
2015-12-18  0   2
2015-12-17  0   1
2015-12-16  1   12
2015-12-14  0   10
2015-12-13  0   9
2015-12-12  0   8
2015-12-11  0   7
2015-12-10  0   6
2015-12-09  0   5
2015-12-08  0   4
2015-12-07  0   3
2015-12-05  0   1
2015-12-04  1   0

Answer 1

您可以使用几个窗口函数来完成此操作。首先是运行总计以构建累积序列 ID，然后在每个序列中选择最小值:

With 
DatesSequence AS
(
SELECT Date,Is_Deposit
, SUM(Is_Deposit) OVER(ORDER BY Date DESC) AS Sequence
FROM #Dates
)
SELECT 
Date
,Is_Deposit
, DATEDIFF(day,
            MIN(Date) OVER (PARTITION BY Sequence),
            Date) + 1 AS DateDif
FROM DatesSequence