CODEWITHSUNDEEP
sqlsql-servert-sql
Touchy Vivace
Touchy Vivace

Reputation: 103

Query to return date range between date row

I have a data structure like this

tid     dt
T004    2021-01-05
T004    2021-01-06
T004    2021-01-08
T004    2021-01-10
T004    2021-01-15
T004    2021-01-20
T005    2021-01-01
T005    2021-01-05

I want to return a range between row that nearest date like this

tid    startDate    endDate
T004    2021-01-05   2021-01-06
T004    2021-01-06   2021-01-08
T004    2021-01-08   2021-01-10
T004    2021-01-10   2021-01-15
T004    2021-01-15   2021-01-20
T005    2021-01-01   2021-01-05

I've tried with this approach but this not do a trick

SQL Server - Querying for Closest Date Range

Fiddle Example http://sqlfiddle.com/#!9/682dc7f/1

example query

INSERT INTO dtterm
    ([tid], [dt])
VALUES
    ('T004', '2021-01-05 00:00:00'),
    ('T004', '2021-01-06 00:00:00'),
    ('T004', '2021-01-08 00:00:00'),
    ('T004', '2021-01-10 00:00:00'),
    ('T004', '2021-01-15 00:00:00'),
    ('T004', '2021-01-20 00:00:00'),
    ('T005', '2021-01-01 00:00:00'),
    ('T005', '2021-01-05 00:00:00')
;

Upvotes: 1

Views: 81

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Use LEAD:

select *
from
(
  select
    tid,
    dt as start_date,
    lead(dt) over (partition by tid order by dt) as end_date
  from mytable
) t
where end_date is not null
order by tid, start_date;

Upvotes: 3

Related Questions