Dropping CLIENT From Query when new appointment is booked-CodePudding

I'm creating a query in SQL Server that pulls in clients who have not had an appointment in 6 months. But need a way to drop the client from the query once they have 'BOOKED' a new appointment.

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'

Any help is appreciated

I've tried using DELETE but I do not want to delete the client entirely, just from the query result to the table in the event that the client has booked a new appointment.

CodePudding user response：

It's really helpful if you provide example DDL and DML when asking questions like this:

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')

Using this we can approach the query as find me clients which don't have any appointments from 6 months ago:

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

Now, if we create an appointment for Jim:

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

He no longer appears in query.

CodePudding user response：

This would work:

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'