I've been staring at this so long now I've confused my self. I'm trying to get a list of clients that have availability next week. Availability is stored as a client ID and date. ie if client 1001 has 05/03/2018, 06/03/2018, 07/03/2018, 08/03/2018, 09/03/2018 then I don't want the name output. however if client 1002 has 05/03/2018, 06/03/2018, 07/03/2018, 08/03/2018 then I do want the name output.
Current code I have is as follows, could some one cast an eye over it and see if im overlooking something please.
SELECT C_Name FROM Clients WHERE NOT EXISTS ( SELECT A_CID FROM Availability WHERE A_CID = C_CID AND ( --Get date for friday next week A_AvailDate <= (DATEADD(DAY, (DATEDIFF(DAY, 4, GETDATE()) / 7) * 7 + 14, 4)) AND --Get date for monday next week A_AvailDate >= (DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 7, 0)) ) GROUP BY A_CID HAVING COUNT(*) <= 4 ) ORDER BY C_Name