TSQL find coincidence cell by cell in a certain order-CodePudding

There are two tables (T-SQL):

declare @init_table table (line_id int, date_from datetime, date_to datetime, name varchar(200), room int)

insert into @init_table values
(1,'2021-09-25', '2021-09-30', 'Tom', 12),
(2,'2021-09-25', '2021-10-05', 'Tom', 12),
(3,'2021-09-25', '2021-09-27', 'Tom', 12),
(4,'2021-09-28', '2021-09-30', 'Tom', 12),
(5,'2021-09-22', '2021-09-30', 'Tom', 12),
(6,'2021-09-26', '2021-09-27', 'Tom', 12),
(7,'2021-09-20', '2021-10-02', 'Tom', 12),
(8,'2021-09-27', '2021-10-10', 'Tom', 12),
(9,'2021-09-01', '2021-09-25', 'Tom', 12), 
(10,'2020-09-25', '2020-09-30', 'Tom', 12),
(11,'2020-09-25', '2020-09-30', 'Tom', 12),
(12,'2020-09-25', '2020-10-05', 'Tom', 12),
(13,'2020-09-25', '2020-09-27', 'Tom', 12),
(14,'2020-09-28', '2020-09-30', 'Tom', 12),
(15,'2020-09-22', '2020-09-30', 'Tom', 12),
(16,'2020-09-26', '2020-09-27', 'Tom', 12),
(17,'2020-09-20', '2020-10-02', 'Tom', 12),
(18,'2020-09-27', '2020-10-10', 'Tom', 12),
(19,'2020-09-01', '2020-09-25', 'Tom', 12),
(20,'2021-09-25', '2021-09-30', 'Bob', 34),
(21,'2021-09-25', '2021-10-05', 'Ron', 56),
(22,'2021-09-25', '2021-09-27', 'Ann', 2),
(23,'2021-09-28', '2021-09-30', 'Jack', 34),
(24,'2021-09-22', '2021-09-30', 'Jacob', 22),
(25,'2021-09-26', '2021-09-27', 'Frank', 1),
(26,'2021-09-20', '2021-10-02', 'Elene', 89),
(27,'2021-09-27', '2021-10-10', 'Mark', 123),
(28,'2021-09-01', '2021-09-25', 'Sam', 124)

declare @check_table table (line_id int, date_from datetime, date_to datetime, name varchar(200), room int)
insert into @check_table values
(1,'2021-09-25', '2021-09-30', 'Tom', 12)

--select * from @init_table
--select * from @check_table

I want to find out if a row from @check_table has coincidence in @init_table< following steps:

Find if there is same or "crossing" periods - same date_from and date_to, same date_from but different date_to and vice versa, period from @check_table includes period from @init_table and vice versa.
If step one meets the condition check if name and room from @check_table and @init_table are same.

I tried to do it with nested iif or if, but the code becomes extremely hard to read. As a result I miss proper check of period. Is there readable SQL query to complete the task (or just one that checks all period coincidences properly)?

SQL query should return "1" or "true". It must indicate if row from @check_table has any possible coincidences of period(@date_from and @date_to) and same name and room.

Thank you.

CodePudding user response：

It would be helpful to clarify your expectations with desired results, however I believe the following gives you what you need - or at least is easily amendable depending on if you need date matches or ranges.

select *, 
    case when exists (
        select * from check_table c
        where i.[name] = c.[name] and i.room = c.room 
        and (i.date_from = c.date_from or i.date_to = c.date_to)
    ) then 1 else 0 end as Matched
from init_table i