Logic problem with 'Select if not all days next week'

Transact-SQL
1

Afternoon all,

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.

Thanks Dave.

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
2

Dave, What is your requirement in plain English with 0 code involved

3

Hey, in plain english i'd have to say, I'm trying to get a list of clients that have got free days next week.

Hope that helps. thanks.

4

Rather than a correlated NOT EXISTS, I'd summarize the Availability table separately and then join the results, like this:

SELECT C_Name, A.A_CID --, A.Days_Available
FROM dbo.Clients C
INNER JOIN (
    SELECT A_CID, 5 - COUNT(*) AS Days_Available
    FROM dbo.Availability
    WHERE                             --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
) AS A ON A.A_CID = C.C_CID
5

Hey,

thanks for the replys, that works perfectly, thank you!