Chopp
asked on
Average Calculation Based on Distinct Month Records per Person - Crystal Reports
Hello Experts,
I am trying to calculate the average number of months (Jan, Feb, etc.) a person has in their records based on the following:
Each person has several records and an associated datetime field. I am interested in adding the DISTINCT number of MONTHS per person, and then dividing that number by the total number of people.
The various formulas and running totals I've tried so far are not correct. Can you suggest a method for getting the average number of months a person has in their records per all the people in the report?
Thank you,
Chopp
I am trying to calculate the average number of months (Jan, Feb, etc.) a person has in their records based on the following:
Each person has several records and an associated datetime field. I am interested in adding the DISTINCT number of MONTHS per person, and then dividing that number by the total number of people.
The various formulas and running totals I've tried so far are not correct. Can you suggest a method for getting the average number of months a person has in their records per all the people in the report?
Thank you,
Chopp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Terrific ! I never used a command before. Thank you for introducing me to the option.
Can I link a command into the data model of another report ? Or, link it to another report in a subreport?
I didn't see the command I created in the list of tables or stored procedures, etc.
~Chopp
Can I link a command into the data model of another report ? Or, link it to another report in a subreport?
I didn't see the command I created in the list of tables or stored procedures, etc.
~Chopp
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Experts,
Thank you for all those ideas and the introduction to the command option. I will try the @count accumulate idea in a future report. I tried the formula suggested by James, which is the same method I tried before sending you my question. I get the following error message using that formula: 'A field is required here'. Do you have a way to resolve that error? I will leave this question open for now, in case you do.
In the meantime, I ended up creating SQL views to handle this report. I pasted the two scripts I am using below in case they are of interest to anyone.
Thank you again for your help.
~Chopp
View #1 Script --
Create View [dbo].[Vw_ClinTranSessionC ountWithRe ferredByTe mplate]
As
Select Distinct
p.Patient_ID
, p.Episode_ID
, Sum(ClinTranCount_ClientsW ithinFY.Nu mberOfSess ions) As NumberOfSessions
, Max(ClinTranCount_ClientsW ithinFY.Ma xProcChron ) As MaxProcChron
, p.fname As FirstName
, SubString(p.fname, 1, 1) As FirstInitial
, p.lname As LastName
, p.dob As DOB
From dbo.Patient p (NoLock)
Join
(
Select ct.Patient_ID
, ct.Patient_Assignment_ID
, Count(*) As NumberOfSessions
, Max(ct.Proc_Chron) As MaxProcChron
From dbo.Patient_Clin_Tran ct (NoLock)
Join ( -- Only clients with at least one session in the FY
Select pct2.Patient_ID
, pct2.Patient_Assignment_ID
From PsychLIVE.dbo.Patient_Clin _Tran As pct2 With (Nolock)
Where pct2.proc_chron >= '7/1/2010'
And pct2.proc_chron < '7/1/2011'
Group By pct2.Patient_ID
, pct2.Patient_Assignment_ID
) As ClientsWithinFY
On ClientsWithinFY.Patient_ID = ct.Patient_ID
And ClientsWithinFY.Patient_As signment_I D = ct.Patient_Assignment_ID
Join dbo.Patient_Assignment_Cus tom As pac With (Nolock)
On pac.Patient_Assignment_ID = ClientsWithinFY.Patient_As signment_I D
Join dbo.Vw_DropDownCodes_Refer red As ref With (Nolock)
On ref.field_value = pac.referred_by
Where
ct.Status = 'CO'
And ct.proc_code In ('SCHLCONF','PARENTX','COL LATMTG','I NTAKE','CA SECONF',
'CASECLNMTG','CHILDTX','GR PTX','PROC ONSULT','S CHLVISIT',
'CLNWRKSHOP','CLNPHONE','F AMILYTX',' CRISIS','T EACHER',
'PARENTPH','MDT','ADULTX')
And ct.Service_ID = 'YTHSERVCTR' --'CASEMNGMT' –‘DELINQUENCYPREVENTION’
And (ct.program_id = '602PROB' Or ref.field_description Like '%probation%')
And ct.clinician_ID <> '00000007'
Group By ct.Patient_ID
, ct.Patient_Assignment_ID
) As ClinTranCount_ClientsWithi nFY
On p.Patient_ID = ClinTranCount_ClientsWithi nFY.Patien t_ID
Where 1=1
Group By p.Patient_ID
, p.Episode_ID
, p.fname
, SubString(p.fname, 1, 1)
, p.lname
, p.dob
View #2 Script --
ALTER View [dbo].[Vw_SessionDurationM onthsDaysW ithReferre dByTemplat e]
AS
Select pct.Patient_ID
, pct.Episode_ID
, Min(proc_chron) As MinAppt
, Max(proc_chron) As MaxAppt
, Case
When Min(proc_chron) = Max(proc_chron) Then 1
When DateDiff(Hour, Min(proc_chron), Max(proc_chron)) < 24 Then 1
Else DateDiff(Day, Min(proc_chron), Max(proc_chron))
End As DurationInDays
, Case
When Min(proc_chron) = Max(proc_chron) Then 1
When DatePart(Month,Min(proc_ch ron)) = DatePart(Month,Max(proc_ch ron))
And DatePart(Year,Min(proc_chr on)) = DatePart(Year,Max(proc_chr on)) Then 1
Else DateDiff(Month, Min(proc_chron), Max(proc_chron)) + 1 --added the plus one in order to include each unique month even if the difference is only one month
End As DurationInMonths
From dbo.Patient_Clin_Tran As pct With (Nolock)
Join ( -- Only clients with at least one session in the FY
Select pct2.Patient_ID
, pct2.Patient_Assignment_ID
From dbo.Patient_Clin_Tran As pct2 With (Nolock)
Where pct2.proc_chron >= '7/1/2010'
And pct2.proc_chron < '7/1/2011'
Group By pct2.Patient_ID
, pct2.Patient_Assignment_ID
) As ClientsWithinFY
On ClientsWithinFY.Patient_ID = pct.Patient_ID
And ClientsWithinFY.Patient_As signment_I D = pct.Patient_Assignment_ID
Join dbo.Patient_Assignment_Cus tom As pac With (Nolock)
On pac.Patient_Assignment_ID = ClientsWithinFY.Patient_As signment_I D
Join dbo.Vw_DropDownCodes_Refer red As ref With (Nolock)
On ref.field_value = pac.referred_by
Where pct.proc_code in ('SCHLCONF','PARENTX','COL LATMTG','I NTAKE','CA SECONF',
'CASECLNMTG','CHILDTX','GR PTX','PROC ONSULT','S CHLVISIT',
'CLNWRKSHOP','CLNPHONE','F AMILYTX',' CRISIS','T EACHER',
'PARENTPH','MDT','ADULTX')
And pct.[status] = 'CO'
And pct.service_id = 'YTHSERVCTR'
And (pct.program_id = '602PROB' Or ref.field_description Like '%probation%')
And pct.clinician_ID <> '00000007'
And pct.patient_id not in ('00000001', '00002208')
Group By pct.Patient_ID
, pct.Episode_ID
Thank you for all those ideas and the introduction to the command option. I will try the @count accumulate idea in a future report. I tried the formula suggested by James, which is the same method I tried before sending you my question. I get the following error message using that formula: 'A field is required here'. Do you have a way to resolve that error? I will leave this question open for now, in case you do.
In the meantime, I ended up creating SQL views to handle this report. I pasted the two scripts I am using below in case they are of interest to anyone.
Thank you again for your help.
~Chopp
View #1 Script --
Create View [dbo].[Vw_ClinTranSessionC
As
Select Distinct
p.Patient_ID
, p.Episode_ID
, Sum(ClinTranCount_ClientsW
, Max(ClinTranCount_ClientsW
, p.fname As FirstName
, SubString(p.fname, 1, 1) As FirstInitial
, p.lname As LastName
, p.dob As DOB
From dbo.Patient p (NoLock)
Join
(
Select ct.Patient_ID
, ct.Patient_Assignment_ID
, Count(*) As NumberOfSessions
, Max(ct.Proc_Chron) As MaxProcChron
From dbo.Patient_Clin_Tran ct (NoLock)
Join ( -- Only clients with at least one session in the FY
Select pct2.Patient_ID
, pct2.Patient_Assignment_ID
From PsychLIVE.dbo.Patient_Clin
Where pct2.proc_chron >= '7/1/2010'
And pct2.proc_chron < '7/1/2011'
Group By pct2.Patient_ID
, pct2.Patient_Assignment_ID
) As ClientsWithinFY
On ClientsWithinFY.Patient_ID
And ClientsWithinFY.Patient_As
Join dbo.Patient_Assignment_Cus
On pac.Patient_Assignment_ID = ClientsWithinFY.Patient_As
Join dbo.Vw_DropDownCodes_Refer
On ref.field_value = pac.referred_by
Where
ct.Status = 'CO'
And ct.proc_code In ('SCHLCONF','PARENTX','COL
'CASECLNMTG','CHILDTX','GR
'CLNWRKSHOP','CLNPHONE','F
'PARENTPH','MDT','ADULTX')
And ct.Service_ID = 'YTHSERVCTR' --'CASEMNGMT' –‘DELINQUENCYPREVENTION’
And (ct.program_id = '602PROB' Or ref.field_description Like '%probation%')
And ct.clinician_ID <> '00000007'
Group By ct.Patient_ID
, ct.Patient_Assignment_ID
) As ClinTranCount_ClientsWithi
On p.Patient_ID = ClinTranCount_ClientsWithi
Where 1=1
Group By p.Patient_ID
, p.Episode_ID
, p.fname
, SubString(p.fname, 1, 1)
, p.lname
, p.dob
View #2 Script --
ALTER View [dbo].[Vw_SessionDurationM
AS
Select pct.Patient_ID
, pct.Episode_ID
, Min(proc_chron) As MinAppt
, Max(proc_chron) As MaxAppt
, Case
When Min(proc_chron) = Max(proc_chron) Then 1
When DateDiff(Hour, Min(proc_chron), Max(proc_chron)) < 24 Then 1
Else DateDiff(Day, Min(proc_chron), Max(proc_chron))
End As DurationInDays
, Case
When Min(proc_chron) = Max(proc_chron) Then 1
When DatePart(Month,Min(proc_ch
And DatePart(Year,Min(proc_chr
Else DateDiff(Month, Min(proc_chron), Max(proc_chron)) + 1 --added the plus one in order to include each unique month even if the difference is only one month
End As DurationInMonths
From dbo.Patient_Clin_Tran As pct With (Nolock)
Join ( -- Only clients with at least one session in the FY
Select pct2.Patient_ID
, pct2.Patient_Assignment_ID
From dbo.Patient_Clin_Tran As pct2 With (Nolock)
Where pct2.proc_chron >= '7/1/2010'
And pct2.proc_chron < '7/1/2011'
Group By pct2.Patient_ID
, pct2.Patient_Assignment_ID
) As ClientsWithinFY
On ClientsWithinFY.Patient_ID
And ClientsWithinFY.Patient_As
Join dbo.Patient_Assignment_Cus
On pac.Patient_Assignment_ID = ClientsWithinFY.Patient_As
Join dbo.Vw_DropDownCodes_Refer
On ref.field_value = pac.referred_by
Where pct.proc_code in ('SCHLCONF','PARENTX','COL
'CASECLNMTG','CHILDTX','GR
'CLNWRKSHOP','CLNPHONE','F
'PARENTPH','MDT','ADULTX')
And pct.[status] = 'CO'
And pct.service_id = 'YTHSERVCTR'
And (pct.program_id = '602PROB' Or ref.field_description Like '%probation%')
And pct.clinician_ID <> '00000007'
And pct.patient_id not in ('00000001', '00002208')
Group By pct.Patient_ID
, pct.Episode_ID
What was the formula that gave the error?
mlmcc
mlmcc
ASKER
The formula was:
DistinctCount ({Patient_Clin_Tran.patien t_id} + " " + CStr({Patient_Clin_Tran.pr oc_chron}, "MMyyyy")) /
DistinctCount ({Patient_Clin_Tran.patien t_id})
‘A field is required here.’
~Chopp
DistinctCount ({Patient_Clin_Tran.patien
DistinctCount ({Patient_Clin_Tran.patien
‘A field is required here.’
~Chopp
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for noticing the missing parenthesis.
The formula still throws an error - requiring a number where the quotes are. I am moving on from this method.
Thank you for all the great ideas.
~Chopp
The formula still throws an error - requiring a number where the quotes are. I am moving on from this method.
Thank you for all the great ideas.
~Chopp
Ah. One of the eccentricities of CR. mlmcc was wrong about the ")". The problem is that CR won't let you do a count on a combination of fields like that. However, there is a simple way around that. Put the combination of fields in a formula, and then do a count on that.
Create a formula like this:
{Patient_Clin_Tran.patient _id} + " " + CStr({Patient_Clin_Tran.pr oc_chron}, "MMyyyy")
Then calculate your average in a formula like this
DistinctCount ({@formula created above}) / DistinctCount ({Patient_Clin_Tran.patien t_id})
That should not produce any errors.
James
Create a formula like this:
{Patient_Clin_Tran.patient
Then calculate your average in a formula like this
DistinctCount ({@formula created above}) / DistinctCount ({Patient_Clin_Tran.patien
That should not produce any errors.
James
AGree. I didn' interpret the requirement correctly if it is to divide the counts.
mlmcc
mlmcc
ASKER
I attempted to type your idea into the Record Selection, but there is obviously more to it.
Can you give me more instructions for how to try your idea?
~Chopp