sql server - SQL Complex Transformation -

i have bellow, want person move gp due change of address should have start date , end date in period. end date date less next start date. please how write query?

declare @tab table(local_patient_identifier varchar(70),         nhs_number varchar(70), gmp varchar(70), practice_code_gp varchar(70),  cds_date date) insert @tab values ('a111111111',  '8bfd000',  'g111111',  'n77777',   '2016-05-23'), ('a111111111',  '8bfd000',  'g222222',  'n77777',   '2016-06-13'), ('a111111111',  '8bfd000',  'g222222',  'n77777',   '2016-06-13'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-02-09'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-03-06'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-03-15'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-03-29'), ('a111111111',  '8bfd000',  'g3333333', 'zz44444',  '2017-05-10'), ('a111111112',  '8bfd002',  'g3333332', 'jj44444',  '2015-05-21'), ('a111111112',  '8bfd002',  'g3333332', 'kk44445',  '2016-05-02'), ('a111111112',  '8bfd002',  'g3333332', 'ww44444',  '2017-02-13') select*from @tab 

expected output

you can use row_number results:

;with cte (     select *,joinkey = row_number() over(partition local_patient_identifier order cds_date) (         select *, rown = row_number() over(partition local_patient_identifier, gmp, practice_code_gp order cds_date)              #tab         )     a.rown = 1 ) select c1.local_patient_identifier,c1.nhs_number, c1.gmp, c1.practice_code_gp, c1.cds_date startdate,      dateadd(day, -1 , c2.cds_date) enddate cte c1 left join cte c2 on c1.local_patient_identifier = c2.local_patient_identifier , c1.joinkey = c2.joinkey - 1 

output below:

+--------------------------+------------+----------+------------------+------------+------------+ | local_patient_identifier | nhs_number |   gmp    | practice_code_gp | startdate  |  enddate   | +--------------------------+------------+----------+------------------+------------+------------+ | a111111111               | 8bfd000    | g111111  | n77777           | 2016-05-23 | 2016-06-12 | | a111111111               | 8bfd000    | g222222  | n77777           | 2016-06-13 | 2017-02-08 | | a111111111               | 8bfd000    | g3333333 | zz44444          | 2017-02-09 | null       | | a111111112               | 8bfd002    | g3333332 | jj44444          | 2015-05-21 | 2016-05-01 | | a111111112               | 8bfd002    | g3333332 | kk44445          | 2016-05-02 | 2017-02-12 | | a111111112               | 8bfd002    | g3333332 | ww44444          | 2017-02-13 | null       | +--------------------------+------------+----------+------------------+------------+------------+ 

you can use windowing function lead if using sql server >= 2012