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.

Possible duplicate of [Compute lag difference for different days](https://stackoverflow.com/questions/47720899/compute-lag-difference-for-different-days) — tarheel, Dec 11 '17 at 04:53

Vamsi Prabhala · Answer 1 · 2017-12-09T23:28:11.487

2

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

edited Dec 09 '17 at 23:28

answered Dec 09 '17 at 22:55

Vamsi Prabhala

48,685
4
36
58

1

Is it really needed to partition by dense_rank for sum(diff)? Wouldn't it work too if to partition just by date instead? – Alexey Dec 09 '17 at 23:20
@Alexey .. you are right..edited it into the answer. – Vamsi Prabhala Dec 09 '17 at 23:28
Thanks Vamsi and Alexey for an interesting solution. Can the calculation be done inline, i.e. directly from `#t` with some `OVER` clause and without subqueries or joins? – Oleg Melnikov Dec 10 '17 at 02:03

t-SQL: calculate date difference with dynamic lag

1 Answers1