How long has it been since last card top-up? We have a table for our stored-value cards:

CardNo   TransactionType   Amount    TransactionDate(datetime2)
         (1 for Top-up, 0 for purchasing products)
=========================================================
0001        1              1000.00    20170809(only date is displayed for brevity)
0001        0              200.00     20170810
0001        0              300.00     20170811
0001        1              200.00     20170811
0001        0              200.00     20170812


We would like to know for each card (customer), the time between each purchase and the most recent top-up at that time. So the intended result is:

RowID   GapDays
================
1       Null (or 0)
2       1
3       2
4       Null
5       1



  Why is there a gap of 2 days for a row id of 3?


The purchase happens before the top-up.

The date column is of type datetime2, has a "time" portion (omitted here for brevity), and the data is ordered by that. You may utilize any index. Non-existent index will be added if necessary.
I've added time part to the TransactionDate just to get the correct order for the transactions:




create table cards(CardNo varchar(10), TransactionType int, Amount money, TransactionDate datetime2);
create index ix_cards on cards(CardNo, TransactionType, TransactionDate);
insert into cards values
('0001',        1,              1000.00,    '20170809 00:00:00'),
('0001',        0,              200.00,     '20170810 00:00:00'),
('0001',        0,              300.00,     '20170811 00:00:00'),
('0001',        1,              200.00,     '20170811 00:00:01'),
('0001',        0,              200.00,     '20170812 00:00:00');

GO



5 rows affected



Then you can get it by using an APPLY operator with all records where TransactionType = 1. Note you must limit to one record the rows returned by the subquery.




set statistics xml on;

SELECT t1.CardNo, t1.TransactionType, t1.Amount, t1.TransactionDate, t3.TransactionDate,
       DATEDIFF(DAY, t3.TransactionDate, t1.TransactionDate) GapDays
FROM   cards t1
OUTER APPLY (SELECT TOP 1 t2.TransactionDate
             FROM   cards t2
             WHERE  t2.CardNo = t1.CardNo
             AND    t2.TransactionType = 1
             AND    t2.TransactionDate &lt;= t1.TransactionDate
             ORDER BY t2.CardNo, t2.TransactionDate DESC) t3
ORDER BY t1.CardNo, t1.TransactionType, t1.TransactionDate
set statistics xml off;
GO



CardNo | TransactionType | Amount    | TransactionDate     | TransactionDate     | GapDays
:----- | --------------: | :-------- | :------------------ | :------------------ | ------:
0001   |               0 | 200.0000  | 10/08/2017 00:00:00 | 09/08/2017 00:00:00 |       1
0001   |               0 | 300.0000  | 11/08/2017 00:00:00 | 09/08/2017 00:00:00 |       2
0001   |               0 | 200.0000  | 12/08/2017 00:00:00 | 11/08/2017 00:00:01 |       1
0001   |               1 | 1000.0000 | 09/08/2017 00:00:00 | 09/08/2017 00:00:00 |       0
0001   |               1 | 200.0000  | 11/08/2017 00:00:01 | 11/08/2017 00:00:01 |       0






dbfiddle here