t-SQL: calculate date difference with dynamic lag

Question

Is there a way to compute the duration between consequent dates that are not the same, using SQL Server 2017's OVER clause and without joins or subqueries? Could this possibly be done with a LAG function using some dynamically computed lag argument?

For example, trx 2 & 3 are on the same day, so we compute the duration from 1 to 2 and from 1 to 3. Since 4 occurred on a different day, its duration is from 3 to 4. Since trx 5 is on the same day as 4, we compute its duration from 3 to 5 and so on.

CREATE TABLE #t(Trx TINYINT, DT DATE);
INSERT INTO #t SELECT 1, '1/1/17';
INSERT INTO #t SELECT 2, '1/5/17';
INSERT INTO #t SELECT 3, '1/5/17';
INSERT INTO #t SELECT 4, '1/15/17';
INSERT INTO #t SELECT 5, '1/15/17';
INSERT INTO #t SELECT 6, '1/20/17';

Below is an easy implementation with a join, but can this be done inline with some OVER clause function (no join or subqueries)?

SELECT c.Trx, c.DT, 
DurO=DATEDIFF(DAY, LAG(c.DT,1) OVER(ORDER BY c.DT), c.DT), -- does not use join
DurJ=DATEDIFF(DAY, MAX(p.DT), c.DT) -- uses join
FROM #t c LEFT JOIN #t p ON c.DT > p.DT
GROUP BY c.Trx, c.DT
ORDER BY c.DT

Note that DurJ is computed correctly, but DurO is not:

Trx DT          DurO    DurJ
1   2017-01-01  NULL    NULL
2   2017-01-05  4       4
3   2017-01-05  0       4
4   2017-01-15  10      10
5   2017-01-15  0       10
6   2017-01-20  5       5

I'll clarify further any specifics, if needed.

NOTE: Not a dup question. This question is concerned with one date column only and no project grouping. Btw, neither question has a satisfiable solution just yet.

Answer 1

Use dense_rank to treat same dates as one group and use it to get the same difference.

select trx,beg,sum(diff) over(partition by grp) as diff
from (select trx,beg,datediff(day,lag(beg) over(order by beg),beg) as diff,
      dense_rank() over(order by beg) as grp
      from #t
     ) t

Per @Alexey's comment's, dense_rank isn't actually needed. You can just use beg date for grouping.

select trx,beg,sum(diff) over(partition by beg) as diff
from (select trx,beg,datediff(day,lag(beg) over(order by beg),beg) as diff
      from #t 
     ) t