SQL Server Generate query based on date range

mmvthczy 于 2023-03-22 发布在其他

关注(0)|答案(1)|浏览(156)

Good morning,

I have two tables such as:

Person  Assignation StartDate   EndDate
usera   BAT A       2016-03-11  2017-02-21
usera   BAT B       2017-02-22  2017-03-28
usera   BAT C       2017-04-01  2017-09-30
usera   BAT C       2017-10-01  2019-12-31
usera   BAT D       2020-01-01  2020-03-31
usera   BAT D       2020-04-01  2021-11-30
usera   BAT E       2021-12-01  2022-03-31
usera   BAT F       2022-04-01  2027-03-31

and

Person  StartDate   Integration
usera   2017-02-15  R0
usera   2017-09-11  R1
usera   2020-05-20  R2
usera   2020-09-03  R3
usera   2021-12-09  R4

My goal is to mix data from TableA and TableB, based on ranged date. desired results is:

Person  Assignation Integration StartDate   EndDate
usera   BAT A       R0          2016-03-11  2017-02-21
usera   BAT B       R0          2017-02-22  2017-03-28
usera   BAT C       R0          2017-04-01  2017-09-10
usera   BAT C       R0          2017-09-11  2017-09-30
usera   BAT C       R1          2017-10-01  2019-12-31
usera   BAT D       R1          2020-01-01  2020-05-19
usera   BAT D       R2          2020-05-20  2020-09-02
usera   BAT D       R3          2020-09-03  2021-11-30
usera   BAT E       R3          2021-12-01  2021-12-08
usera   BAT E       R4          2021-12-09  2022-03-31
usera   BAT F       R4          2022-04-01  2027-03-31

As for usera / bat A & B the integration date is between TableA range date, so records are the same for usera / BAT C, there is an integration and so this should create a new records.

I spend time with using some lead / lag and left join, but not sure my approach is correct

How I can proceed ? thank you very much

sql-server

来源：https://stackoverflow.com/questions/75765802/generate-query-based-on-date-range