Show only entries between dates

[RCAIVANO69]

The following is sample data

Service date PT-identifier exam
3/3/08 123456 ct abd
3/3/08 123456 ct chest
3/12/08 123456 ct abd
4/15/08 123456 ct body

I have group1 - pt-identitifer, group2 {@Service Date}where {@servicedate} =Cdate{Servicedate}

I have a group selection of to filter pt-identifiers which have more than one service date

DistinctCount ({@ServiceDate}, {PATIENT_IDENTIFIERS.IDENTIFIER})>1

What I need to accomplish is only show the procedures if the services date groups are within {?interval} number of days of each other (ie 14 day)

the above example should show all but the last entry

 

[dgillz]

Change {?Interval} to a date range parameter rather than a number. The create a record selection formula:

{@ServiceDate} in {?Interval}

This should limit the report to the dates you enter, and the group selection formula you currently have will still work.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

I think you would need to use the group selection as you are now doing, but with section suppression to take care of the interval issue. Use a detail suppression formula something like this:

(
(
onfirstrecord or
{table.PT_identifier} <> previous({PT-identifier}) and
{table.PT_identifier} = next({PT-identifier})
) and
datediff("d", {@servicedate},next({@servicedate})) > {?Interval}
) or
(
{table.PT_identifier} = previous({PT-identifier}) and
{table.PT_identifier} = next({PT-identifier}) and
datediff("d", {@servicedate},previous({@servicedate})) > {?Interval} and
datediff("d", {@servicedate},next({@servicedate})) > {?Interval}
) or
(
(
onlastrecord or
{table.PT_identifier} <> next({PT-identifier})
) and
{table.PT_identifier} = previous({PT-identifier}) and
datediff("d", {@servicedate},previous({@servicedate})) > {?Interval}
)

This should be close anyway.

-LB

 

[RCAIVANO69]

Thank you for the post. The second suggestion almost worked
here is my problem

If patient has multiple exams per date, but the time frame between the dates are outside the interval range they still show IE.

3/12/08 procedure
3/12/08 porcedure1

5/25/08 procedure
5/25/08 procedure1

The first and last procedure are showing.
I need to hide them all if GH2a and GH2b are not within the interval

Thank you all for your help

 

[lbass]

Please provide a larger sample showing what you see before adding the suppression criteria, what you get after using it, and then what you want to see.

-LB

 

[RCAIVANO69]

GH1 patient1-ID
GH2 3/12/08
3/12/08 Procedure1
3/12/08 Procedure2

GH2 5/25/08
5/25/08 Procedure1
5/25/08 Procedure2

GH1 patietn2-ID
GH2 3/15/08
3/15/08 Procedure

GH2 3/20/08
3/20/08 Procedure1
3/20/08 Procedure2

GH2 5/2/08
5/2/08 Procedure1
5/2/08 Procedure2

GH2 5/11/08
5/11/08 Procedure1

GH2 5/30/08 Procedure1

Assuming {?interval} = 10 days

Patient1-ID would be entirely suppressed since
the intval between GH2 would be greater than 10

and for patient2-ID we would see the following
GH1 patietn2-ID
GH2 3/15/08
3/15/08 Procedure

GH2 3/20/08
3/20/08 Procedure1
3/20/08 Procedure2

GH2 5/2/08
5/2/08 Procedure1
5/2/08 Procedure2

GH2 5/11/08
5/11/08 Procedure1

since 3/15 and 3/20 are within 10 days and 5/2 and 5/11 are within 10 days but 5/30 is out of range

Thanks

 

[lbass]

And using the same sample, what are you getting instead, using the current suppression formula?

-LB

 

[RCAIVANO69]

GH1 patient1-ID
GH2 3/12/08
3/12/08 Procedure1
3/12/08 Procedure2

GH2 5/25/08
5/25/08 Procedure1
5/25/08 Procedure2

GH1 patietn2-ID
GH2 3/15/08
3/15/08 Procedure

GH2 3/20/08
3/20/08 Procedure1
3/20/08 Procedure2

GH2 5/2/08
5/2/08 Procedure1
5/2/08 Procedure2

GH2 5/11/08
5/11/08 Procedure1


what I did notice when reviewing my full report was if

GH2 3/20/08
3/20/08 procedure1

GH2 5/30/08
5/30/08 procedure1

In this senario it did hide the procedure done on 3/20 but
showed the one on 5/30/08

 

[lbass]

So you're saying the suppression formula doesn't work at all?

-LB

 

[RCAIVANO69]

In my actualy report without the suppression filter I have the following (interval used =14 days)

1/23/08 Procedure-1
3/5/08 Procedure-1
5/19/08 Procedure-1

with suppression

1/23/08 Procedure-1
3/5/08 Procedure-1

it would only hide the last procedure

could this be a problem since i'm using {@servicdate}as GH2
would there be a better way??

 

[lbass]

Please show a sample of actual data for each of the three: data before suppression; data after suppression; results you expect.

-LB

 

[RCAIVANO69]

Parameters 1-1-08 - 5-31-08 interval =10days


No suppression

date exam # procedure mrn
0001480
01/02/2008
1/2/2008 151899 ct abd 0001480
1/2/2008 151603 ct chest 0001480
Exam count per service date 2

05/30/2008
5/30/2008 161053 ct chest 0001480
5/30/2008 160409 ct abd 0001480
Exam count per service date 2
Distinct Count of @ServiceDate 2


0024346
03/02/2008
3/2/2008 155425 ct abd 0024346
Exam count per service date 1

03/10/2008
3/10/2008 156022 ct abd 0024346
Exam count per service date 1

03/28/2008
3/28/2008 157152 ct abd 0024346
3/28/2008 157153 ct chest 0024346
Exam count per service date 2
Disctinct Count of @ServiceDate 3

0081714
04/02/2008
4/2/2008 157456 ct abd 0081714
Exam count per service date 1

05/03/2008
5/3/2008 159393 ct abd 0081714
5/3/2008 159368 ct chest 0081714
5/3/2008 159367 ct abd 0081714
5/3/2008 159394 ct chest 0081714
Exam count per service date 4

05/08/2008
5/8/2008 159707 ct abd 0081714
5/8/2008 159707 ct chest 0081714
Exam count per service date 2

05/20/2008
5/20/2008 160428 ct chest 0081714
5/20/2008 160420 ct abd 0081714
Exam count per service date 2
Disctinct Count of @ServiceDate 4


with suppression

date exam # procedure mrn
0001480
01/02/2008
1/2/2008 151899 ct abd 0001480
1/2/2008 151603 ct chest 0001480
Exam count per service date 2

05/30/2008
5/30/2008 161053 ct chest 0001480
5/30/2008 160409 ct abd 0001480
Exam count per service date 2
Distinct Count of @ServiceDate 2


0024346
03/02/2008
3/2/2008 155425 ct abd 0024346
Exam count per service date 1

03/10/2008
3/10/2008 156022 ct abd 0024346
Exam count per service date 1

03/28/2008
3/28/2008 157152 ct abd 0024346
3/28/2008 157153 ct chest 0024346
Exam count per service date 2
Disctinct Count of @ServiceDate 3

0081714
04/02/2008

Exam count per service date 1

05/03/2008
5/3/2008 159393 ct abd 0081714
5/3/2008 159368 ct chest 0081714
5/3/2008 159367 ct abd 0081714
5/3/2008 159394 ct chest 0081714
Exam count per service date 4

05/08/2008
5/8/2008 159707 ct abd 0081714
5/8/2008 159707 ct chest 0081714
Exam count per service date 2

05/20/2008
5/20/2008 160428 ct chest 0081714
5/20/2008 160420 ct abd 0081714
Exam count per service date 2
Disctinct Count of @ServiceDate 4


EXPECTED RESULTS


0024346
03/02/2008
3/2/2008 155425 ct abd 0024346
Exam count per service date 1

03/10/2008
3/10/2008 156022 ct abd 0024346
Exam count per service date 1
Disctinct Count of @ServiceDate 3

0081714

05/03/2008
5/3/2008 159393 ct abd 0081714
5/3/2008 159368 ct chest 0081714
5/3/2008 159367 ct abd 0081714
5/3/2008 159394 ct chest 0081714
Exam count per service date 4

05/08/2008
5/8/2008 159707 ct abd 0081714
5/8/2008 159707 ct chest 0081714
Exam count per service date 2
Disctinct Count of @ServiceDate 4

 

[lbass]

Try the following. I had the dates reversed in a couple of the datediff formulas so they were showing a negative --which would always be less than the interval. Sorry.

(
(
onfirstrecord or
{table.PT_identifier} <> previous({PT-identifier}) and
{table.PT_identifier} = next({PT-identifier})
) and
datediff("d", {@servicedate},next({@servicedate})) > {?Interval}
) or
(
{table.PT_identifier} = previous({PT-identifier}) and
{table.PT_identifier} = next({PT-identifier}) and
datediff("d", previous({@servicedate}),{@servicedate}) > {?Interval} and
datediff("d", {@servicedate},next({@servicedate})) > {?Interval}
) or
(
(
onlastrecord or
{table.PT_identifier} <> next({PT-identifier})
) and
{table.PT_identifier} = previous({PT-identifier}) and
datediff("d", previous({@servicedate}),{@servicedate}) > {?Interval}
)

-LB

 

[RCAIVANO69]

This seems to only work if there are only (1) exam per servicedate. if there are multiple exams per date
the datediff @servicedate will always be less than the interval.

Is there a way to accoumplish this by using
maximum({@servicedate}) in the footer of each date????

The comparision ideally needs to be done against the GH2 which is @servicedate

 

[lbass]

I am missing your point. I did test this last bit and it seemed to work. Please show how it doesn't work. I assumed if there were multiples you wanted to show them both.

-LB

 

[RCAIVANO69]

I need to show all studies that are done within {?interval} days of each other. We dont' care if 10 studies were done on one day if their next service date is not within the interval period

IE. if our interval is 10 days if there are
(5) studies done on 2/15/08 and
(1) study done on 3/20/08
(1) study done on 3/26/08
(3) studies done on 4/15/08
(4) studies done on 5/12/08
(2) studies done on 5/16/08


I only want to show the ALL the studies done on
3/20/08, 3/26/08, 5/12/08 & 5/16/08
because these DISTINCT service dates are within 10days of each other

studies from 2/15/08 & 4/15/08 were not performed
within 10 days of another servicedate

because these DISTINCT service dates are within 10days of each other

I hope this is possible and maybe helped clarify what I was trying to accomplish

 

[lbass]

Okay, I see. I think the only way you can really do this is to use SQL expressions to limit the records returned to the report, as follows. First create a SQL expression {%cntdts}:

(
select count(distinct "ServiceDate")
from table A
where A."PT_Identifier" = table."PT_Identifier"
)


Then create {%maxID}:

(
select max("ID")
from table A
where A."ServiceDate" = table."ServiceDate"
)

...where ID is some field that would always be unique to the detail row. None of the fields you are currently showing meet that requirement, but I'm guessing it exists. If not, you could use a concatenation within the max, I think.

The syntax/punctuation in the SQL expression depends upon your datasource.

Then in the record selection formula, use:

{%cntdts} > 1 and
{%maxID} = {table.ID}

This would return one row per date and you no longer need the group selection or a group on date, just a group on PT_ID. Then add a subreport to the details section (now only one row per date). Add a subreport that is linked to the main report on PT_ID and link {%cntdts} to the date field in the subreport. The subreport should contain the detail level fields, while the main report will only show the PT_ID and date. Apply the suppression formula to the details section. To suppress the group header when all details are suppressed, I think you could use a formula like this:

datediff("d", minimum({table.date},{table.PT_Identifier}),maximum({table.date},{table.PT_Identifier})) > {?Interval}

-LB