Exclude results that meet different criteria

I need to calculate the number of follow-up face-to-face contacts - these are all contacts except those which are first contacts.

I have a query that gives me the first contacts, but what is the syntax to exclude these from my overall results set?

Something like the following in your where clause

AND contact_id NOT IN
(
   -- Your query that gives the first contacts here
)

There might be simpler and more efficient ways of accomplishing the same, but can't tell without seeing your query and some sample data.

Thanks, here is my attempt at using the syntax you suggested. I get this error, which relates to the very last ")" (after my query that gives the first contacts).

Msg 116, Level 16, State 1, Line 201
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I am not sure how to give you sample data?

select
distinct 
pd.NHI_NUMBER as NHI
,ppr.SEQNO as [Referral ID]
,convert(Char(10),ppr.ReferralStartDate,103) as [ReferralStartDate]
,convert(Char(10),ppr.ReferralEndDate,103) as [ReferralEndDate]
,ppa.SEQNO as [Activity ID]
,convert(Char(10),ppa.ActivityStartDate,103) as [Activity Start Date]
,convert(Char(10),ppa.ActivityEndDate,103) as [Activity End Date]
,mha.LocalShortCode as [Code Month1]
FROM PATIENT_PRIMHD_ACTIVITY as ppa
	join PATIENT_PRIMHD_REFERRAL as ppr
	on ppa.REFERRAL_SEQNO=ppr.SEQNO
	join patient_details as pd
	on pd.PATIENT_ID=ppa.PATIENT_ID
	JOIN PATIENT_PRIMHD_MHA as mha
	on ppa.ActivityTypeCodeID=mha.SEQNO
WHERE
	ppa.ActivityStartDate between @StartDate1 and @EndDate1
	and mha.LocalShortCode in (@T32,@T36,@T38,@T39,@T42,@T46)
	and ppr.ReferralTeamID=@TeamID
	and ppa.ReferralTeamID=@TeamID
	and ppa.SEQNO not in
	(
	SELECT 
NHI as [First Face to Face Contacts Month 1]
,[Referral ID]
,[ReferralStartDate]
,[ReferralEndDate]
,[First Activity]
,[Activity ID]
,[Activity Start Date]
,[Activity End Date]
from
(
select
distinct pd.NHI_NUMBER as NHI
,ppr.SEQNO as [Referral ID]
,convert(Char(10),ppr.ReferralStartDate,103) as [ReferralStartDate]
,convert(Char(10),ppr.ReferralEndDate,103) as [ReferralEndDate]
,RANK() over 
	(partition by ppr.seqno
	order by ppa.seqno
	)as [First Activity]
,ppa.SEQNO as [Activity ID]
,convert(Char(10),ppa.ActivityStartDate,103) as [Activity Start Date]
,convert(Char(10),ppa.ActivityEndDate,103) as [Activity End Date]
FROM PATIENT_PRIMHD_ACTIVITY as ppa
	join PATIENT_PRIMHD_REFERRAL as ppr
	on ppa.REFERRAL_SEQNO=ppr.SEQNO
	join patient_details as pd
	on pd.PATIENT_ID=ppa.PATIENT_ID
	JOIN PATIENT_PRIMHD_MHA as mha
	on ppa.ActivityTypeCodeID=mha.SEQNO
WHERE
	ppr.ReferralStartDate between @StartDate1 and @EndDate1
	and ppa.ActivityStartDate between @StartDate1 and @EndDate1
	AND mha.LocalShortCode in (@T32,@T36,@T38,@T39,@t42,@t46)
	and ppr.ReferralTeamID=@TeamID
	and ppa.ReferralTeamID=@TeamID
) as r
where r.[First Activity] = 1

	)

In the inner query, you have to specify only one column - the column which matches the column before the NOT IN keyword. So for example, it might be something like this

....
        AND ppa.SEQNO NOT IN 
        (
                SELECT 
                      [Activity ID]
                FROM    (....)
,,,,,,

In the above, I am assuming that SEQNO values that you want to eliminate from the results of the query match the [Activity ID] in the inner query. That may not be the column you need to use; I am just showing an example.

Thank you so much, that worked!

As I don't want the list as my result (I just did that to check the data), I need a count for each month.

I have tried to write the query but it's giving me this error:

Msg 102, Level 15, State 1, Line 176
Incorrect syntax near ';'.

And there are red squiggly lines under [Activity ID for Count] in these lines:

SELECT datepart(month, [Activity ID for Count]) AS [Month No.]
	,datename(month, [Activity ID for Count]) AS [Month]

and

GROUP BY datepart(month, [Activity ID for Count])
	,datename(month, [Activity ID for Count])

I renamed the [Activity ID] in the first query as [Activity ID for Count] as I thought that was causing the problem (as there are sub-Selects with the same field name.

Here's the whole query now:

SELECT datepart(month, [Activity ID for Count]) AS [Month No.]
	,datename(month, [Activity ID for Count]) AS [Month]
	,Count(*) AS [Follow-up Face to Face Contacts - Month1]
FROM (
	SELECT DISTINCT pd.NHI_NUMBER AS NHI
		,ppr.SEQNO AS [Referral ID]
		,convert(CHAR(10), ppr.ReferralStartDate, 103) AS [ReferralStartDate]
		,convert(CHAR(10), ppr.ReferralEndDate, 103) AS [ReferralEndDate]
		,ppa.SEQNO AS [Activity ID for Count]
		,convert(CHAR(10), ppa.ActivityStartDate, 103) AS [Activity Start Date]
		,convert(CHAR(10), ppa.ActivityEndDate, 103) AS [Activity End Date]
		,mha.LocalShortCode AS [Code Month1]
	FROM PATIENT_PRIMHD_ACTIVITY AS ppa
	JOIN PATIENT_PRIMHD_REFERRAL AS ppr ON ppa.REFERRAL_SEQNO = ppr.SEQNO
	JOIN patient_details AS pd ON pd.PATIENT_ID = ppa.PATIENT_ID
	JOIN PATIENT_PRIMHD_MHA AS mha ON ppa.ActivityTypeCodeID = mha.SEQNO
	WHERE ppa.ActivityStartDate BETWEEN @StartDate1
			AND @EndDate1
		AND mha.LocalShortCode IN (
			@T32
			,@T36
			,@T38
			,@T39
			,@T42
			,@T46
			)
		AND ppr.ReferralTeamID = @TeamID
		AND ppa.ReferralTeamID = @TeamID
		AND ppa.SEQNO NOT IN (
			SELECT [Activity ID]
			FROM (
				SELECT DISTINCT pd.NHI_NUMBER AS NHI
					,ppr.SEQNO AS [Referral ID]
					,convert(CHAR(10), ppr.ReferralStartDate, 103) AS [ReferralStartDate]
					,convert(CHAR(10), ppr.ReferralEndDate, 103) AS [ReferralEndDate]
					,RANK() OVER (
						PARTITION BY ppr.seqno ORDER BY ppa.seqno
						) AS [First Activity]
					,ppa.SEQNO AS [Activity ID]
					,convert(CHAR(10), ppa.ActivityStartDate, 103) AS [Activity Start Date]
					,convert(CHAR(10), ppa.ActivityEndDate, 103) AS [Activity End Date]
				FROM PATIENT_PRIMHD_ACTIVITY AS ppa
				JOIN PATIENT_PRIMHD_REFERRAL AS ppr ON ppa.REFERRAL_SEQNO = ppr.SEQNO
				JOIN patient_details AS pd ON pd.PATIENT_ID = ppa.PATIENT_ID
				JOIN PATIENT_PRIMHD_MHA AS mha ON ppa.ActivityTypeCodeID = mha.SEQNO
				WHERE ppr.ReferralStartDate BETWEEN @StartDate1
						AND @EndDate1
					AND ppa.ActivityStartDate BETWEEN @StartDate1
						AND @EndDate1
					AND mha.LocalShortCode IN (
						@T32
						,@T36
						,@T38
						,@T39
						,@t42
						,@t46
						)
					AND ppr.ReferralTeamID = @TeamID
					AND ppa.ReferralTeamID = @TeamID
				) AS r
			WHERE r.[First Activity] = 1
			)
	GROUP BY datepart(month, [Activity ID for Count])
		,datename(month, [Activity ID for Count])
	;

You have too few right brackets. Add a bracket as shown for closing out the subquery. Also, when you use a subquery like the way you are doing, you have to alias it to something. I aliased to S.

		......
		WHERE r.[First Activity] = 1
			)
	) AS S -- < Add this line
	GROUP BY datepart(month, [Activity ID for Count])
		,datename(month, [Activity ID for Count])
	;
1 Like

Thanks James, that worked :smile:
I even managed to convert this into a count query by myself, :grinning:

I appreciate your help.