I have the following table
The first column are transaction dates order by Date DESC. The second column is binary state for each transaction, could be either 0 or 1. The third column is the datediff of the minimum date compared to each date. the code that produces this table is something like that
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
My problem is that when is_Deposit=1 i need to reset my seed for DATEDIFF and the date of IS_Deposit =1 become my new MIN Date and so on for each IS_Deposit=1 i find in the table. For example from 2015-12-04 until 2015-12-16 everything is ok. But in line 746 2015-12-17 i would like this to be 1 again and not 13 as we have reached the next IS_Deposit=1 and we have to reset.
Resultset now:
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
Resultset Required:
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
You can do this with a couple of window functions. First a running total to build a cumulative sequence ID then pick out the minimum within each sequence:
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