在预约新约会时从查询中删除CLIENT

Question

我正在SQL Server中创建一个查询，该查询将在6个月内没有预约的客户拉入。但是需要一种方法，一旦客户端'预订'了一个新的约会，就从查询中删除客户端。

SELECT CONCAT(CLIENT_FNAME, ' ' ,CLIENT_LNAME) AS 'Name', APPOINTMENT.APP_DATE AS 'Last Appointment' 
FROM CLIENT
LEFT JOIN APPOINTMENT ON CLIENT.CLIENT_ID = APPOINTMENT.CLIENT_ID 
WHERE APPOINTMENT.APP_DATE < DATEADD(MONTH, -6, GETDATE()) AND CLIENT.CLIENT_STATUS = 'ACTIVE'

感谢您的帮助

我已经尝试使用DELETE，但我不想完全删除客户端，只是在客户端预订新约会的情况下从查询结果到表。

Answer 1

如果你在问这样的问题时提供示例DDL和DML，这真的很有帮助:

DECLARE @Clients TABLE (ClientID INT IDENTITY, FirstName NVARCHAR(50), LastName NVARCHAR(50), DeleteDateTimeUTC DATETIME)
DECLARE @Appointments TABLE (AppointmentID INT IDENTITY, AppointeeID INT, BookedDateTimeUTC DATETIME)
INSERT INTO @Clients (FirstName, LastName) VALUES
('John','Smith'), ('Bobby','Tables'), ('Jim','Kirk')
INSERT INTO @Appointments (AppointeeID, BookedDateTimeUTC) VALUES
(1, '2022-11-01 18:00'), (2, '2022-12-25 18:00'), (3, '2021-08-01 13:00')

使用这个，我们可以将查询处理为查找我6个月前没有任何约会的客户:

SELECT *
FROM @Clients c
LEFT OUTER JOIN @Appointments a
ON c.ClientID = a.AppointeeID
AND a.BookedDateTimeUTC > DATEADD(MONTH,-6,GETUTCDATE()) 
WHERE a.AppointmentID IS NULL
AND c.DeleteDateTimeUTC IS NULL /* is the client active */

ClientID    FirstName   LastName    DeleteDateTimeUTC   AppointmentID   AppointeeID BookedDateTimeUTC
-----------------------------------------------------------------------------------------------------
3           Jim         Kirk        NULL                NULL            NULL        NULL

现在，如果我们为Jim创建一个约会:

INSERT INTO @Appointments (AppointeeID, BookedDateTimeUTC) VALUES 
(3, '2023-01-05 14:00')

他不再出现在查询中。

Answer 2

这样可以:

SELECT *
FROM Clients c
where not exists --There is no appointment newer than today minus 6months (or never)
(select 1
from Appointments a
where c.CLIENT_ID = a.CLIENT_ID
AND a.APP_DATE > DATEADD(MONTH,-6,getdate()) 
)
and c.CLIENT_STATUS='ACTIVE'