Link to home
Start Free TrialLog in
Avatar of LBarrett
LBarrett

asked on

Number of Days between dates based on condition

Hi Experts,

I have data similar to this in detail section - report is grouped by AdmSys

PatSys      AdmSys      BegDatTim                         SvcID      DateOnly
18951      10324      4/2/2007 11:00:00 AM      EXT HHA      4/2/2007
18951      10324      4/3/2007 10:00:00 AM      HHA      4/3/2007
18951      10324      4/3/2007 11:00:00 AM      HS      4/3/2007
18951      10324      4/4/2007 10:00:00 AM      HHA      4/4/2007
18951      10324      4/4/2007 11:00:00 AM      EXT HHA      4/4/2007
18951      10324      4/4/2007 1:30:00 PM      HS      4/4/2007
18951      10324      4/5/2007 10:00:00 AM      HHA      4/5/2007
18951      10324      4/5/2007 11:00:00 AM      EXHHA      4/5/2007
18951      10324      4/6/2007 10:00:00 AM      HHA      4/6/2007
18951      10324      4/6/2007 11:00:00 AM      EXHHA      4/6/2007
18951      10324      4/9/2007 10:00:00 AM      HHA      4/9/2007
18951      10324      4/10/2007 10:00:00 AM      HHA      4/10/2007
18951      10324      4/11/2007                       HS      4/11/2007
18951      10324      4/11/2007 10:00:00 AM      HHA      4/11/2007
18951      10324      4/11/2007 12:30:00 PM      SN      4/11/2007
18951      10324      4/12/2007 10:00:00 AM      HHA      4/12/2007
18951      10324      4/17/2007 10:00:00 AM      HHA      4/17/2007
18951      10324      4/18/2007 10:00:00 AM      HHA      4/18/2007
18951      10324      4/18/2007 12:30:00 PM      SN      4/18/2007
18951      10324      4/24/2007 10:00:00 AM      HHA      4/24/2007
18951      10324      4/25/2007                       HS      4/25/2007
18951      10324      4/25/2007 10:00:00 AM      HHA      4/25/2007
18951      10324      4/25/2007 12:30:00 PM      SN      4/25/2007

What I need is to have a field that determines the number of days between dates of each where SvcID = "HS".  It must also reset after each.  So what I need will look like this:

PatSys      AdmSys      BegDatTim                         SvcID      DateOnly      Days Between HSVisits

18951      10324      4/2/2007 11:00:00 AM      EXT HHA      4/2/2007
18951      10324      4/3/2007 10:00:00 AM      HHA      4/3/2007
18951      10324      4/3/2007 11:00:00 AM      HS      4/3/2007            0
18951      10324      4/4/2007 10:00:00 AM      HHA      4/4/2007
18951      10324      4/4/2007 11:00:00 AM      EXT HHA      4/4/2007
18951      10324      4/4/2007 1:30:00 PM      HS      4/4/2007            1
18951      10324      4/5/2007 10:00:00 AM      HHA      4/5/2007
18951      10324      4/5/2007 11:00:00 AM      EXHHA      4/5/2007
18951      10324      4/6/2007 10:00:00 AM      HHA      4/6/2007
18951      10324      4/6/2007 11:00:00 AM      EXHHA      4/6/2007
18951      10324      4/9/2007 10:00:00 AM      HHA      4/9/2007
18951      10324      4/10/2007 10:00:00 AM      HHA      4/10/2007
18951      10324      4/11/2007                       HS      4/11/2007      6
18951      10324      4/11/2007 10:00:00 AM      HHA      4/11/2007
18951      10324      4/11/2007 12:30:00 PM      SN      4/11/2007
18951      10324      4/12/2007 10:00:00 AM      HHA      4/12/2007
18951      10324      4/17/2007 10:00:00 AM      HHA      4/17/2007
18951      10324      4/18/2007 10:00:00 AM      HHA      4/18/2007
18951      10324      4/18/2007 12:30:00 PM      SN      4/18/2007
18951      10324      4/24/2007 10:00:00 AM      HHA      4/24/2007
18951      10324      4/25/2007                       HS      4/25/2007      14
18951      10324      4/25/2007 10:00:00 AM      HHA      4/25/2007
18951      10324      4/25/2007 12:30:00 PM      SN      4/25/2007

Some visits have date/time and some do not so I have the Date Only column added since the time between HS to HS does not matter just the number of days.  Tried setting variables but could get them to work correctly (fairly novice at variables) even with adding EvaluateAfter, tried running total with poor results.  Any help would be appreciated!  Using CR 8.5.

LBarrett

Avatar of Mike McCracken
Mike McCracken
Try this idea

In the report header add a fromula
WhilePrintingrecords;
Global DateVar LastHSDate;
''

In the detail sectioin add a formula
WhilePrintingrecords;
Global DateVar LastHSDate;
Local NumberVar DaysSince;
If IsNull(LastHSDate) then
    DaysSince := 0
else
    DaysSince := DateDiff('d',LastHSDate,{YourDateField});
LastHSDate := {YourDateField};
DaysSince

mlmcc
Avatar of LBarrett
LBarrett

ASKER

this line
If IsNull(LastHSDate) then
    DaysSince := 0

gives an error of "a field is required here" for the (LastHSDate) section
Avatar of LBarrett
LBarrett

ASKER

How do these formulas only choose the records where SvcId="HS"?  Can this be used in a Group Header since more than one patient and admission will be run each time?  Group 1 is Patient ID Group 2 is Admission Number then the detail sorted by date.  The parameters will be date range and probably company branch.

LBarrett
Avatar of Mike McCracken
Mike McCracken
Forgot the select part for the HS.

I am going to assume your datas are current.

In the report header add a fromula
WhilePrintingrecords;
Global DateVar LastHSDate := Date(1900,01,01);
''

In the detail sectioin add a formula
WhilePrintingrecords;
Global DateVar LastHSDate;
Local NumberVar DaysSince;
If {YourTypeFIeld} = 'HS' then
(
    If (LastHSDate = Date(1900,01,01)) then
        DaysSince := 0
    else
        DaysSince := DateDiff('d',LastHSDate,{YourDateField});

    LastHSDate := {YourDateField};
)
else
    DaySince := 0;

DaysSince

Avatar of LBarrett
LBarrett

ASKER

This line
LastHSDate := {YourDateField};
)
else
    DaySince := 0;

DaysSince

the DaySince:=0 I changed to DaysSince:=0
but I get the error " a date is required here"
Avatar of Mike McCracken
Mike McCracken
This fix will make no sense but try it this way

WhilePrintingrecords;
Global DateVar LastHSDate;
Local NumberVar DaysSince;
If {YourTypeFIeld} = 'HS' then
(
    If (LastHSDate = Date(1900,01,01)) then
        DaysSince := 0
    else
        DaysSince := DateDiff('d',LastHSDate,{YourDateField});

    LastHSDate := {YourDateField};
)
else
(
    DaySince := 0;
    LastHSDate := LastHSDate;
);

DaysSince

mlmcc
Avatar of LBarrett
LBarrett

ASKER

Did a short test and it seems to work great, will do full test tomorrow a.m.  I have to say I don't get it, would you mind trying to explain???
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 LBarrett
LBarrett

ASKER

This seems to be just what I needed.

Thank you for the help, very much appreciated.

LBarrett