如何根据日期（第二列）选择第一列和第三列？

我无法理解任务的逻辑。当顾客在特定日期购买食物时，会有一张桌子（在示例中有几个）。并且有一个在同一时间段内访问过或未访问过的商店的列表。

第一个条件是显示在一段时间内访问过和未访问该商店的所有人员。第二个条件是显示在一段时间内访问过或未访问过的商店。

我无法连接这些数据。问题是如何连接第 1 列和第 3 列。也就是说，第 3 列应该显示未访问过或访问过的商店，同时链接人员，反之亦然，如果该人没有访问该商店。根据条款，禁止使用运营商。

NOT IN, IN, NOT EXISTS, EXISTS, UNION, EXCEPT, INTERSECT

我是这样实现的，但是不知道如何选择。

有三个表，Buers 与表关联，Visit、Stores 与 Hanging 关联，Visit 是有客户 ID 和商店 ID 的地方。

买家

create table person
( id bigint primary key ,
  name varchar(MAX) not null,
  age integer not null default 10,
  gender varchar(6) default 'female' not null ,
  address varchar(MAX)
  );

alter table person add constraint ch_gender check ( gender in ('female','male') );

insert into person values (1, 'Anna', 16, 'female', 'Moscow');
insert into person values (2, 'Andrey', 21, 'male', 'Moscow');
insert into person values (3, 'Kate', 33, 'female', 'Kazan');
insert into person values (4, 'Denis', 13, 'male', 'Kazan');
insert into person values (5, 'Elvira', 45, 'female', 'Kazan');

商店

create table pizzeria
(id bigint primary key ,
 name varchar(50) not null ,
 rating numeric not null default 0);

alter table pizzeria add constraint ch_rating check ( rating between 0 and 5);

insert into pizzeria values (1,'Pizza Hut', 4.6);
insert into pizzeria values (2,'Dominos', 4.3);
insert into pizzeria values (3,'DoDo Pizza', 3.2);
insert into pizzeria values (4,'Papa Johns', 4.9);
insert into pizzeria values (5,'Best Pizza', 2.3);
insert into pizzeria values (6,'DinoPizza', 4.2);

买家来访

create table person_visits
(id bigint primary key ,
 person_id bigint not null ,
 pizzeria_id bigint not null ,
 visit_date date not null default GETDATE(),
 constraint uk_person_visits unique (person_id, pizzeria_id, visit_date),
 constraint fk_person_visits_person_id foreign key  (person_id) references person(id),
 constraint fk_person_visits_pizzeria_id foreign key  (pizzeria_id) references pizzeria(id)
 );

insert into person_visits values (1, 1, 1, '2022-01-01');
insert into person_visits values (2, 2, 2, '2022-01-01');
insert into person_visits values (3, 2, 1, '2022-01-02');
insert into person_visits values (4, 3, 5, '2022-01-03');
insert into person_visits values (5, 3, 6, '2022-01-04');
insert into person_visits values (6, 4, 5, '2022-01-07');
insert into person_visits values (7, 4, 6, '2022-01-08');
insert into person_visits values (8, 5, 2, '2022-01-08');
insert into person_visits values (9, 5, 6, '2022-01-09');
insert into person_visits values (10, 6, 2, '2022-01-09');
insert into person_visits values (11, 6, 4, '2022-01-01');
insert into person_visits values (12, 7, 1, '2022-01-03');
insert into person_visits values (13, 7, 2, '2022-01-05');

第一个请求，我选择在特定日期访问商店的员工

DECLARE @DataMin DATE = '2022-01-01';
DECLARE @DataMax DATE = '2022-01-03';

CREATE TABLE ##ResultVisitPersons
(namePerson NVARCHAR(100), resultVisit BIT, visitDuringPeriod NVARCHAR(100), pizzeriaName NVARCHAR(100), pizzeriaID INT)

INSERT INTO ##ResultVisitPersons
SELECT
    visit.name,
    visit.resultVisit,
    visit.visitDuringPeriod,
    visit.pizzeriaName,
    visit.pizzeria_id
FROM (
  SELECT 
    person.name,
    CASE 
        WHEN person_visits.visit_date BETWEEN @DataMin AND @DataMax THEN 1
        ELSE 0
    END AS resultVisit,
    CASE 
        WHEN person_visits.visit_date BETWEEN @DataMin AND @DataMax THEN LEFT(CONVERT(VARCHAR, person_visits.visit_date, 104), 10)
        ELSE 'NULL'
    END AS visitDuringPeriod,
    person_visits.pizzeria_id,
    pizzeria.name as pizzeriaName
FROM person_visits
JOIN person ON person.id = person_visits.person_id
JOIN pizzeria ON pizzeria.id = person_visits.pizzeria_id
) visit

第二个请求是同一时间段内商店访问的样本。

CREATE TABLE ##ResultVisitPizzeria
(visitDuringPeriod BIT, result NVARCHAR(100), namePizzeria NVARCHAR(100), id INT)

INSERT INTO ##ResultVisitPizzeria
SELECT 
    CASE 
        WHEN person_visits.visit_date BETWEEN @DataMin AND @DataMax THEN 1
        ELSE 0
    END AS visitDuringPeriod,
        CASE 
        WHEN person_visits.visit_date BETWEEN @DataMin AND @DataMax THEN N'Ресторан посещали'
        ELSE N'Ресторан не посещали'
    END AS isVisitDuringPeriod,
    pizzeria.name,
    pizzeria.id
FROM person_visits
JOIN pizzeria ON pizzeria.id = person_visits.pizzeria_id