COUNT using RANK

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.

Try this:

select year([referral start date]) as [year]
      ,month([referral start date]) as [month]
      ,count(*) as firstcontacts
  from (
        /* your select here */
       )
    as a
 group by year([referral start date]) as [year]
         ,month([referral start date]) as [month]

or this:

select sum(case
              when year([referral start date])=2015
               and month([referral start date])=1
              then 1
              else 0
           end
          ) as [jan]
      ,sum(case
              when year([referral start date])=2015
               and month([referral start date])=2
              then 1
              else 0
           end
          ) as [feb]
       ...
  from (
        /* your select here */
       )
    as a

Thank you - I am learning a lot from you :smiley:

So my code now reads:

--Number of First Face To Face Contacts - Month1
--This gives a list, I just want a count
--Needs to be copied for all three months
SELECT 
	year([Referral Start Date]) as [year]
	,month([Referral Start Date]) as [month]
	,Count(*) as firstcontacts
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
group by
	year([Referral Start Date])
	,month([Referral Start Date])
;

But now I get this error relating to the first SELECT line.

Msg 242, Level 16, State 3, Line 83
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

My mistake, if you remove the formatting of "Referral Start Date" and "Referral End Date", its much easier to extract year and month from, and it should work:

SELECT 
	year(ReferralStartDate) as [year]
	,month(ReferralStartDate) as [month]
	,Count(*) as firstcontacts
from
(
select
distinct pd.NHI_NUMBER as NHI
,ppr.SEQNO as [Referral ID]
,ppr.ReferralStartDate as [Referral Start Date]
,ppr.ReferralEndDate 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
group by
	year([Referral Start Date])
	,month([Referral Start Date])
;

OK I think I have changed the right bits, but I am still getting the same error on the same line

--Number of First Face To Face Contacts - Month1
--Needs to be copied for all three months
--Trial of Count - gives error
SELECT 
	year(ReferralStartDate) as [Year]
	,month(ReferralStartDate) as [Month]
	,Count(*) as [First 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]
,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.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
	year(ReferralStartDate)
	,month(ReferralStartDate)

on line 13 and 14, should be:

I don't understand what's going wrong here. I have changed it as you suggested, and also the [Activity Start Date] as I got an error on that, but I get the same error, on the first select line.

It now looks like:

--Number of First Face To Face Contacts
select 
	DATEpart(Month, [Activity Start Date]) as [#]
	,DATEname(Month, [Activity Start Date]) as [Month]
	,count(*) as [First Face to Face Contacts]
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]
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 Start Date])
	,datename(month,[Activity Start Date])
order by
	DATEpart(Month, [Activity Start Date])
;

You still convert ReferralStartDate, ReferralEndDate, ActivityStartDate and ActivityEndDate to char(10) in the subselect. Doing this, you can't perform date functions on them, and that is why you get the error.
Now, if you keep the date fields as they are, you can perform your date functions on them (datepart & datename).

Thank you for being so patient with me.

I am getting this error:

Msg 8120, Level 16, State 1, Line 50
Column 'r.ActivityStartDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I am sure I am missing a parentheses or put it in the wrong place, but I have tried placing it where I think it should go (either after

) as r

or after

where r.[First Activity] = 1

and I get the same error. So I tried putting it into the Group By clause, doesn't fix it - and I tried replacing the count(*) with count([First Activity] and I still get the same error.

At the moment it looks like:

--Number of First Face To Face Contacts
select 
	DATEpart(Month, [ActivityStartDate]) as [#]
	,DATEname(Month, [ActivityStartDate]) as [Month]
	,count(*) as [First Face to Face Contacts]
from
(
select
distinct pd.NHI_NUMBER as NHI
,ppr.SEQNO as [Referral ID]
,ppr.ReferralStartDate as [ReferralStartDate]
,ppr.ReferralEndDate as [ReferralEndDate]
,RANK() over 
	(partition by ppr.seqno
	order by ppa.seqno
	)as [First Activity]
,ppa.SEQNO as [Activity ID]
,ppa.ActivityStartDate as [ActivityStartDate]
,ppa.ActivityEndDate as [ActivityEndDate]
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,ReferralStartDate)
	,datename(month,ReferralStartDate)
;

You are selecting ActivityStartDate but grouping by ReferralStartdate. These must be the same in order for the count function to work.

Thank you so much, it works
Obviously a trap for young players :slight_smile: I will need to pay more attention.