counting first visits 1

[p658484]

Hi
I am trying to count only the first visit on a report. If a patient has a first visit in April and a second visit in February, this patient should be counted only once and that should be in April, which will be the first visit. The visit in February should not be counted. The report needs to be grouped by month.
Would like a formula for this please.

January
Snow White
Jane Doe
X. Smith
Total 3

February
Snow White
123. Pumpkin
Mr. Socks
Total 2

Snow White should be counted only once - The first visit only


Thanks
RM

 

[lbass]

Because you are grouping by month, I think the best solution would be to limit the dataset to the first date only per patient. Please identify your CR version and paste in your current SQL query (database->show SQL query). Also clarify whether you are using any parameters in your record selection formula.

-LB

 

[p658484]

Hello,
CR version is Crystal X1.

Parameter is by RegistrationVisitDate and it is grouped by the same.

SQL query:
SELECT "I10_Amcare_And_Diagnosis_VR"."ChartNumber", "I10_Amcare_And_Diagnosis_VR"."RegistrationNumber", "I10_Amcare_And_Diagnosis_VR"."RegistrationVisitDate", "I10_Amcare_And_Diagnosis_VR"."DiagnosisCode", "I10_Amcare_And_Diagnosis_VR"."DiagnosisType", "I10_Amcare_And_Diagnosis_VR"."zzAbstractLink", "I10_Amcare_And_Diagnosis_VR"."HealthCareNumber"
FROM "Med2020"."dbo"."I10_Amcare_And_Diagnosis_VR" "I10_Amcare_And_Diagnosis_VR"
WHERE ("I10_Amcare_And_Diagnosis_VR"."RegistrationVisitDate">={ts '2011-04-01 00:00:00'} AND "I10_Amcare_And_Diagnosis_VR"."RegistrationVisitDate"<{ts '2012-02-01 00:00:00'}) AND "I10_Amcare_And_Diagnosis_VR"."DiagnosisCode"='Z51.1' AND "I10_Amcare_And_Diagnosis_VR"."DiagnosisType"='M'

 

[lbass]

Well, do you mean the first visit within the parameter dates or the first visit ever?

-LB

 

[p658484]

Hello,
It will be the first visit within the parameter dates. I am looking at data for a year grouped by month. Typically, I need to know how many new patients for each month.
Thanks
RM

 

[lbass]

Try using a command as your sole datasource as below, and be sure to create the date parameters on the right within the command screen.

SELECT
"I10_Amcare_And_Diagnosis_VR"."ChartNumber",
"I10_Amcare_And_Diagnosis_VR"."RegistrationNumber",
"I10_Amcare_And_Diagnosis_VR"."RegistrationVisitDate",
"I10_Amcare_And_Diagnosis_VR"."DiagnosisCode",
"I10_Amcare_And_Diagnosis_VR"."DiagnosisType",
"I10_Amcare_And_Diagnosis_VR"."zzAbstractLink",
"I10_Amcare_And_Diagnosis_VR"."HealthCareNumber",
(
select min(A."RegistrationVisitDate")
from "I10_Amcare_And_Diagnosis_VR" A
where A."RegistrationVisitDate">={?StartDate} and
A."RegistrationVisitDate"<{?EndDate}+1 and
A."DiagnosisCode"='Z51.1' and
A."DiagnosisType"='M'and
A."HealthCareNumber"="I10_Amcare_And_Diagnosis_VR"."HealthCareNumber"
) "MinDate"

FROM "Med2020"."dbo"."I10_Amcare_And_Diagnosis_VR" "I10_Amcare_And_Diagnosis_VR"

WHERE
(
"I10_Amcare_And_Diagnosis_VR"."RegistrationVisitDate">={ts '2011-04-01 00:00:00'} AND
"I10_Amcare_And_Diagnosis_VR"."RegistrationVisitDate"<{ts '2012-02-01 00:00:00'}
) AND
"I10_Amcare_And_Diagnosis_VR"."DiagnosisCode"='Z51.1' AND
"I10_Amcare_And_Diagnosis_VR"."DiagnosisType"='M' and
"I10_Amcare_And_Diagnosis_VR"."RegistrationVisitDate"=
(
select min(A."RegistrationVisitDate")
from "I10_Amcare_And_Diagnosis_VR" A
where A."RegistrationVisitDate">={?StartDate} and
A."RegistrationVisitDate"<{?EndDate}+1 and
A."DiagnosisCode"='Z51.1' and
A."DiagnosisType"='M'and
A."HealthCareNumber"="I10_Amcare_And_Diagnosis_VR"."HealthCareNumber"
)

This assumes that HealthCareNumber is the Patient ID.

-LB