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.
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
--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)
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).
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)
;