CODEWITHSUNDEEP
sqlsql-servert-sql
JP Jack
JP Jack

Reputation: 719

Lead and partition function

This is my input table

create table #table1 (id int, FN varchar(20), startdate varchar(20), id1  varchar)

insert #table1
  select 1, 'Joe', '2019-01-01', 'A'
  union select 1, 'Joe', '2019-01-01', 'B'
  union select 1, 'Joe', '2019-01-05', 'C'
  union select 1, 'Joe', '2019-01-05', 'D'
  union select 1, 'Joe', '2019-01-06', 'E'
  union select 2, 'john', '2019-01-05', 'F'
  union select 2, 'john', '2019-01-06', 'G'
  union select 2, 'john', '2019-01-06', 'H'
  union select 2, 'john', '2019-01-07', 'I'

I tried the following code

 select *
   , dense_rank() OVER (partition by id, fn  order by startdate) 
   , lead(startdate,1) OVER (partition by id, fn order by startdate) 
 from #table1
 order by id

output

But I require the following output:

Expected output

Upvotes: 1

Views: 66

Answers (1)

Hadi
Hadi

Reputation: 37348

I know that there might be a better approach but a least this is a working solution:

select *, 
      (select MIN(startdate) 
       from #table1 t1 
       where t1.id = #table1.id and 
             t1.fn = #table1.fn and 
             t1.startdate > #table1.startdate) enddate
from #table1

Result

enter image description here

Upvotes: 1

Related Questions