Link to home
Start Free TrialLog in
Avatar of Chopp
ChoppFlag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chopp

ASKER

What do you mean by 'COMMAND'?  
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chopp

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chopp

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_ClinTranSessionCountWithReferredByTemplate]
As
Select      Distinct
            p.Patient_ID
      ,     p.Episode_ID
      ,     Sum(ClinTranCount_ClientsWithinFY.NumberOfSessions) As NumberOfSessions
      ,     Max(ClinTranCount_ClientsWithinFY.MaxProcChron) 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_Assignment_ID = ct.Patient_Assignment_ID
                       
                       
                  Join dbo.Patient_Assignment_Custom As pac With (Nolock)
                       On    pac.Patient_Assignment_ID = ClientsWithinFY.Patient_Assignment_ID
                       
                   Join dbo.Vw_DropDownCodes_Referred As ref With (Nolock)
                       On    ref.field_value = pac.referred_by
                 
                  Where
                              ct.Status = 'CO'
                  And         ct.proc_code In ('SCHLCONF','PARENTX','COLLATMTG','INTAKE','CASECONF',
                                                        'CASECLNMTG','CHILDTX','GRPTX','PROCONSULT','SCHLVISIT',
                                                        'CLNWRKSHOP','CLNPHONE','FAMILYTX','CRISIS','TEACHER',
                                                        '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_ClientsWithinFY
      On    p.Patient_ID = ClinTranCount_ClientsWithinFY.Patient_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_SessionDurationMonthsDaysWithReferredByTemplate]
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_chron)) = DatePart(Month,Max(proc_chron))
                         And DatePart(Year,Min(proc_chron)) = DatePart(Year,Max(proc_chron))      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_Assignment_ID = pct.Patient_Assignment_ID
                       
Join dbo.Patient_Assignment_Custom As pac With (Nolock)
                       On    pac.Patient_Assignment_ID = ClientsWithinFY.Patient_Assignment_ID
                       
 Join dbo.Vw_DropDownCodes_Referred As ref With (Nolock)
                       On    ref.field_value = pac.referred_by

Where pct.proc_code in ('SCHLCONF','PARENTX','COLLATMTG','INTAKE','CASECONF',
                                                  'CASECLNMTG','CHILDTX','GRPTX','PROCONSULT','SCHLVISIT',
                                                  'CLNWRKSHOP','CLNPHONE','FAMILYTX','CRISIS','TEACHER',
                                                '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
Avatar of Mike McCracken
Mike McCracken

What was the formula that gave the error?

mlmcc
Avatar of Chopp

ASKER

The formula was:

DistinctCount ({Patient_Clin_Tran.patient_id} + " " + CStr({Patient_Clin_Tran.proc_chron}, "MMyyyy")) /
DistinctCount ({Patient_Clin_Tran.patient_id})

‘A field is required here.’


~Chopp
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chopp

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
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.proc_chron},  "MMyyyy")


 Then calculate your average in a formula like this

DistinctCount ({@formula created above}) / DistinctCount ({Patient_Clin_Tran.patient_id})


 That should not produce any errors.

 James
AGree.  I didn' interpret the requirement correctly if it is to divide the counts.

mlmcc