Hi there, me again... So here's my query, I want to count how many first "face to face" contacts for each month.
This query works:
--Number of First Face To Face Contacts - Month1 SELECT * from ( select distinct pd.NHI_NUMBER as NHI ,ppr.SEQNO as [Referral ID] ,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date] ,convert(Char(10),ppr.ReferralEndDate,103) as [Referral End Date] ,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] ,mha.LocalShortCode as [Code Month1] FROM PATIENT_PRIMHD_ACTIVITY as ppa join PATIENT_PRIMHD_REFERRAL as ppr on ppa.PATIENT_ID=ppr.PATIENT_ID 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 ;
It gives me the following results, which are correct.
Now, I just want the count, not a list, but my syntax isn't working.