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?
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])
;
Thanks James, that worked
I even managed to convert this into a count query by myself,
I appreciate your help.