Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: 13 Month Rolling History Post Reply Post New Topic
Page  of 2 Next >>
Author Message
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Topic: 13 Month Rolling History
    Posted: 08 Jan 2008 at 6:59am
Greetings:
This is my first post and I have a question.  I wouldn't be here if I didn't have a question, right?
 
I need to generate a monthly report that consists of patients that have not scheduled an appointment within 13 months of their last visit.
 
The first time I ran the report I used this criteria:
 
if {APPT.APPT_DATE} >= dateadd("m", -13, currentdate)
then " "
else "PAST DUE"
 
What I also did was group on Patient_ID and sort by Appt_Date.  I then copied both fields to the group footer and then hid the detail.  That will produce a listing of the appointment with the greatest date.
 
When I run the report February 1st, I want another list of patients that are 13 months overdue.  This group of patients should be entirely different than the patients that were on the list the previous month.  I want the older than 14 months (last month's report) to roll off the report.  Each month the report is run would contain different patients for 13 months overdue only.

Here is an example:

21001  1/21/2004
21001 4/15/2005
21001 12/6/2006
21003 5/10/2005
21003 6/18/2006
21003 11/15/2006
 
The January report will show patient number 21003 11/15/2006 as being 13 months past due.

The February report will show patient number 21001 12/6/2006 as being 13 months past due.  Patient 21003 11/15/2006 should not show on the February report because it's now 14 months past due. 

This is the 13 month rolling date selection criteria is need.  How do I do a select "range" for 13 months only?
Thanks for  your assistance.
 
"Pete"
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 08 Jan 2008 at 11:50am
You're 90% of the way there already.

Try:

IF {Appt.ApptDate} IN DateAdd("m", -14, CurrentDate) TO DateAdd("m", -13, CurrentDate)
THEN "PAST DUE"
ELSE " "


IP IP Logged
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Posted: 08 Jan 2008 at 1:22pm
I thought it had to be something like that.  I went down that path originally but I couldn't get the syntax correct for some reason. 
 
There is a concern though about using that formula.  If I run the report today (01/08/2008), it will only pick up appointments between 11/08/2006 and 12/08/2006 as past due.  What I really want is any past due for the entire month of November, 2006.  When I run the report in February I want all of December, 2006.    How would I write that formula?
 
The formula you suggested will work if I schedule the report to run every month on the first of the month so I can live with it.  That is unless there is another solution.
 
Thanks for your time.
 
"Pete"
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 09 Jan 2008 at 5:00am
Sure there is.

If Month({Appt.ApptDate}) = Month(CurrentDate) - 1
AND Year({Appt.ApptDate}) = Year(CurrentDate) - 1
Then "PAST DUE"
Else ""

You can't simply subtract 13, because that will just give you a negative number.  But, subtracting one month and one year gives you the same result.  Oh, dang, except for January.  So, wrap the above in a test for January, like so:

IF Month({CurrentDate}) = 1 THEN
    IF Month({Appt.ApptDate}) = 12
    AND Year({Appt.ApptDate}) = Year(CurrentDate) - 2
    THEN "PAST DUE"
    ELSE ""
ELSE
    IF Month({Appt.ApptDate}) = Month(CurrentDate) - 1
    AND Year({Appt.ApptDate}) = Year(CurrentDate) - 1
    THEN "PAST DUE"
    ELSE ""


IP IP Logged
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Posted: 09 Jan 2008 at 6:14am

I made one small correction:({CurrentDate}) should be (CurrentDate).  Other than that, it works just fine to pull out all the December, 2006 appointments.

There is still a problem.  With your formula, how do I check to see if the patient has indeed scheduled an appointment?  Patient 1234 had an appointment on 12/27/2006 and has a scheduled appointment on 01/21/2008.  Patient should not be on the report because they have a future appointment.  Wouldn't I have to do that date check first before your formula?
 
"Pete"
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 09 Jan 2008 at 10:27am
Well, the ApptDate you're checking is the latest one, right?  Which means there shouldn't be a later one.  Unless you are storing future appointment dates in a separate field for some reason.


IP IP Logged
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Posted: 09 Jan 2008 at 12:32pm

The appointment date file can have future appointments in it.  Here are test formulas I currently have set up just as checks and balances as to what exactly is in the database. 

Formula:  AppDate

// Appointments older than 13 months.  We don’t care about these.

if {APPT.APPT_DATE} >= dateadd("m", -13, currentdate)

then " "

else "Old"

 

Formula:  Range

//Appointments that are less than 13 months old. 

if {@ApptDate} = " " and {@Future} = " "

THEN

IF Month(CurrentDate) = 1 THEN

    IF Month({APPT.APPT_DATE}) = 12

    AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 2

    THEN " "

    ELSE "Within Range"

 

Formula:  Future

//Future scheduled appointments

if {APPT.APPT_DATE} > currentdate

then "Future"

else " "

 

Formula:  PastDue

//Appointments older than 13 months that do not have a scheduled appointment.

if {@ApptDate} = " " and {@Future} = " "

then

IF Month(CurrentDate) = 1 THEN

    IF Month({APPT.APPT_DATE}) = 12

    AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 2

    THEN "PAST DUE"

    ELSE ""

ELSE

    IF Month({APPT.APPT_DATE}) = Month(CurrentDate) - 1

    AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 1

    THEN "PAST DUE"

    ELSE ""

 

Sample Data:

 

Patient

Date

1

11/21/2005

1

12/27/2006

1

1/21/2008

2

8/18/2006

3

1/19/2005

3

10/29/2007

4

12/16/2002

4

12/13/2005

4

12/13/2006

 

 Sample Report:

  

PH

Patient

Date

 

D

1

11/21/2005

 

D

1

12/27/2006

 

D

1

1/21/2008

 

GF1

1

1/21/2008

Future

D

2

8/18/2006

 

GF1

2

8/18/2006

Old

D

3

1/19/2005

 

D

3

10/29/2007

 

GF1

3

10/29/2007

Within Range

D

4

12/16/2002

 

D

4

12/13/2005

 

D

4

12/13/2006

 

GF1

4

12/13/2006

PAST DUE

 
I hide the detail.  This seems to work until I try and add a selection formula of:
@PastDue = "PAST DUE".  I then get the following results:
 
PH Patient Date
D 1 12/27/2006
GF1 1 12/27/2006 PAST DUE
D 4 12/13/2006
GF1 4 12/13/2006 PAST DUE
 
Patient 1 is wrong.  12/27/2006 should not be on the report as it has a future appointment.
 
I'm confused and I am sure you are by now too.
"Pete"
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 10 Jan 2008 at 5:35am
Ah.  The basic problem here is one of timing.  Your selection criteria are being evaluated before @Future is.  So, your @Future value is actually returning the results for the previous record.

The simplest solution is to simply combine the functionality of the two formulas.  So, it might look like:


IF {APPT.APPT_DATE} >= dateadd("m", -13, CurrentDate)

    AND {APPT.APPT_DATE} <= CurrentDate

THEN

    IF Month(CurrentDate) = 1 THEN

        IF Month({APPT.APPT_DATE}) = 12

        AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 2

        THEN "PAST DUE"

        ELSE ""

    ELSE

        IF Month({APPT.APPT_DATE}) = Month(CurrentDate) - 1

        AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 1

        THEN "PAST DUE"

        ELSE ""

ELSE ""


Although, honestly, I'm starting to question your whole methodology.  I think that you are starting to pile up too many discrete functions, and their interactions are no longer entirely clear.  Also, the differences between a straight DateDiff on CurrentDate and the Month and Year comparison for @PastDue is, I think, creating some overlap.  Let's try a different approach.

In each record, let's put a formula, call it @ApptStatus that looks like:


Local DateVar CalcDate := CurrentDate - Day(CurrentDate) + 1;
//This function returns the first day of the current month.

SELECT DateDiff("m",{Appt.ApptDate},CalcDate)
    CASE Is > 13     : 1 //Equivalent to "Old"
    CASE 13            : 2 //Equivalent to "Past Due"
    CASE Is < 1       : 4 //Equivalent to "Future"
    DEFAULT            : 3 //Equivalent to "Current";


Now, in the group footer, we put another formula, call it @ApptDesc, that looks like:



("Old","PAST DUE","Current","Future")[Maximum(@ApptStatus,{Appt.PatientID})]
//This will return the string corresponding to the latest appointment status.


In the suppression criteria of your group footer, you can put a formula like:


Maximum(@ApptStatus,{Appt.PatientID}) <> 2



As another note, I would simply add to your Select Criteria a restriction where ApptDate >= DateAdd("m",-14,CurrentDate).  This will prevent a lot of irrelevant data from coming in.




IP IP Logged
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Posted: 10 Jan 2008 at 5:45am
Timing is exactly the issue and that's what I was trying to get around.  The various "buckets" for current, old, future, etc. were just my way to identify what exactly was in the database.  I definitely want to clean all that stuff up as I don't really need it.

Let me start over and use your recommendations and I'll get back to you.

I really appreciate all the time you are taking with this "newbie" to solve my problem.  I have learned a lot.

"Pete"
IP IP Logged
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Posted: 10 Jan 2008 at 10:13am

Thanks to you, I got the results I should.  Report looks great. 

Only issue I had was with the group footer selection criteria.
("Old","PAST DUE","Current","Future") doesn't seem to want to work before the Maximum command.  I might have the parentheses in the wrong place.  I'll work on that later.
 
Once again, thanks for all your help.  I would not have been able to complete this report without your assistance.  This was quite a learning experience for me.  Thanks for your patience.
 
"Pete"
 
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.