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
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
ASKER
this line
If IsNull(LastHSDate) then
DaysSince := 0
gives an error of "a field is required here" for the (LastHSDate) section
If IsNull(LastHSDate) then
DaysSince := 0
gives an error of "a field is required here" for the (LastHSDate) section
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
LBarrett
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,{Y ourDateFie ld});
LastHSDate := {YourDateField};
)
else
DaySince := 0;
DaysSince
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,{Y
LastHSDate := {YourDateField};
)
else
DaySince := 0;
DaysSince
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"
LastHSDate := {YourDateField};
)
else
DaySince := 0;
DaysSince
the DaySince:=0 I changed to DaysSince:=0
but I get the error " a date is required here"
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,{Y ourDateFie ld});
LastHSDate := {YourDateField};
)
else
(
DaySince := 0;
LastHSDate := LastHSDate;
);
DaysSince
mlmcc
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,{Y
LastHSDate := {YourDateField};
)
else
(
DaySince := 0;
LastHSDate := LastHSDate;
);
DaysSince
mlmcc
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This seems to be just what I needed.
Thank you for the help, very much appreciated.
LBarrett
Thank you for the help, very much appreciated.
LBarrett
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,{Y
LastHSDate := {YourDateField};
DaysSince
mlmcc